Programming/SPRING

[SPRING] Mapper

너굴위 2023. 10. 25. 17:35
728x90
반응형

Mapper (Interface 생성)

- mapper.xml에 저장한 쿼리문들을 어노테이션을 사용하여 간편하게 관리할 수 있도록 하는 인터페이스 생성

- 인터페이스 생성 전, 매핑 xml파일 삭제, MyBaisConfig.xml파일에서 매핑관련 내용 삭제(혹은 주석처리)

- 이외에 log4j와 pom xml파일은 수정할 내용 없음 (src/main/java안에 log4j.xml, myBatisConfig.xml 넣기)

- mapperinterface를 사용하게 되면 DAO를 사용하지 않아도 작성 가능( jsp 파일에서 처리하면 된다.)

* MyBatisWebEx03

 

경로: src/main/java

  패키지: com.exam.mapper

//SqlMapperInter.java

package com.exam.mapper;

import com.exam.model1.DeptTO;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

public interface SqlMapperInter {

	@Select("select deptno, dname, loc from dept where deptno=10")
	public DeptTO selectByDeptno();
	
	@Select("select deptno, dname, loc from dept")
	public List<DeptTO> selectList();
	
	@Select("select deptno, dname, loc from dept where deptno=#{deptno}")
	//public List<DeptTO> selectListByDeptno(String deptno);
	public List<DeptTO> selectListByDeptno(DeptTO to);
	
	@Select("select deptno, dname, loc from dept where dname like #{dname}")
	public List<DeptTO> selectListByLikeDname(String dname);
	
	@Insert("insert into dept2 values(#{deptno},#{dname},#{loc})")
	public int insert(DeptTO to);
	
	@Update("update dept2 set dname=#{dname} where deptno=#{deptno}")
	public int update(DeptTO to);
	
	@Delete("delete from dept2 where deptno=#{deptno}")
	public int delete(String deptno);
	
}

 

  패키지 : com.exam.model1

//DeptTO.java
package com.exam.model1;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class DeptTO {
	private String deptno;
	private String dname;
	private String loc;

}

경로: src/main/webapp   (jsp파일 삽입)

<!--dept4.jsp 부서관련 리스트 불러오기-->
<%@page import="com.exam.mapper.SqlMapperInter"%>
<%@page import="java.io.IOException"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactoryBuilder"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactory"%>
<%@page import="org.apache.ibatis.io.Resources"%>
<%@page import="org.apache.ibatis.session.SqlSession"%>
<%@page import="java.io.InputStream"%>
<%@page import="com.exam.model1.DeptTO"%>
<%@page import="java.util.List"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
   String resource = "myBatisConfig.xml";
   
   InputStream is = null;
   SqlSession sqlSession = null;
   
   try {
      is = Resources.getResourceAsStream( resource );
      SqlSessionFactory sqlSessionFactory
      = new SqlSessionFactoryBuilder().build( is );
      
      sqlSession = sqlSessionFactory.openSession(true);
      
      // 어노테이션시 추가되는 구문
      sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
      SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
      
      List<DeptTO> lists = mapper.selectList();   //리스트 불러오기
      //DeptTO paramTO = new DeptTO();
      //paramTO.setDeptno("30");
      //List<DeptTO> lists = mapper.selectListByDeptno(paramTO);     //부서번호에 맞는 리스트 불러오기
      
      //List<DeptTO> lists = mapper.selectListByLikeDname("S%");  //사실 뭐하는건지 잘 모르겠음ㅋ
      
      out.println("<table width='600' border='1d'>");
      for(DeptTO to : lists) {
	      out.println("<tr>");
	      out.println("<td>" + to.getDeptno() + "</td>");
	      out.println("<td>" + to.getDname() + "</td>");
	      out.println("<td>" + to.getLoc() + "</td>");
	      out.println("</tr>");
      }
      out.println("</table>");
      
      
   } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } finally {
      if( sqlSession != null ) sqlSession.close();
      if( is != null ) try { is.close(); } catch(IOException e) {}
   }
%>
</body>
</html>

=> mapper.selectList(); 처럼 인터페이스에 정의한 내용을 참조하여 사용하면 된다.

 

<!--insert.jsp 부서 테이블에 데이터 삽입하기-->
<%@page import="com.exam.mapper.SqlMapperInter"%>
<%@page import="java.io.IOException"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactoryBuilder"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactory"%>
<%@page import="org.apache.ibatis.io.Resources"%>
<%@page import="org.apache.ibatis.session.SqlSession"%>
<%@page import="java.io.InputStream"%>
<%@page import="com.exam.model1.DeptTO"%>
<%@page import="java.util.List"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String resource = "myBatisConfig.xml";
   
   InputStream is = null;
   SqlSession sqlSession = null;
   
   try {
      is = Resources.getResourceAsStream( resource );
      SqlSessionFactory sqlSessionFactory
      = new SqlSessionFactoryBuilder().build( is );
      
      sqlSession = sqlSessionFactory.openSession(true);
      
      // 어노테이션시 추가되는 구문
      sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
      SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
      
   		DeptTO to = new DeptTO();
   		to.setDeptno("50");
   		to.setDname("개발부");
   		to.setLoc("전주");
   	
   		int result = mapper.insert(to);
   		out.println("결과: "+result);
      
      
   } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } finally {
      if( sqlSession != null ) sqlSession.close();
      if( is != null ) try { is.close(); } catch(IOException e) {}
   }
%>
</body>
</html>

 

<!-- update.jsp 부서 테이블 내용 수정하기-->
<%@page import="com.exam.mapper.SqlMapperInter"%>
<%@page import="java.io.IOException"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactoryBuilder"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactory"%>
<%@page import="org.apache.ibatis.io.Resources"%>
<%@page import="org.apache.ibatis.session.SqlSession"%>
<%@page import="java.io.InputStream"%>
<%@page import="com.exam.model1.DeptTO"%>
<%@page import="java.util.List"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String resource = "myBatisConfig.xml";
   
   InputStream is = null;
   SqlSession sqlSession = null;
   
   try {
      is = Resources.getResourceAsStream( resource );
      SqlSessionFactory sqlSessionFactory
      = new SqlSessionFactoryBuilder().build( is );
      
      sqlSession = sqlSessionFactory.openSession(true);
      
      // 어노테이션시 추가되는 구문
      sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
      SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
      
   		
      	DeptTO to = new DeptTO();
 		to.setDname("총괄부");
 		to.setDeptno("40");
 		
 		int result = mapper.update(to);
 		out.println("결과: "+result);
      
   } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } finally {
      if( sqlSession != null ) sqlSession.close();
      if( is != null ) try { is.close(); } catch(IOException e) {}
   }
%>
</body>
</html>

 

<!-- delete.jsp 부서 테이블 내용 삭제하기-->

<%@page import="com.exam.mapper.SqlMapperInter"%>
<%@page import="com.exam.mapper.SqlMapperInter"%>
<%@page import="java.io.IOException"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactoryBuilder"%>
<%@page import="org.apache.ibatis.session.SqlSessionFactory"%>
<%@page import="org.apache.ibatis.io.Resources"%>
<%@page import="org.apache.ibatis.session.SqlSession"%>
<%@page import="java.io.InputStream"%>
<%@page import="com.exam.model1.DeptTO"%>
<%@page import="java.util.List"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String resource = "myBatisConfig.xml";
   
   InputStream is = null;
   SqlSession sqlSession = null;
   
   try {
      is = Resources.getResourceAsStream( resource );
      SqlSessionFactory sqlSessionFactory
      = new SqlSessionFactoryBuilder().build( is );
      
      sqlSession = sqlSessionFactory.openSession(true);
      
      // 어노테이션시 추가되는 구문
      sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
      SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
   	
   		int result = mapper.delete("50");
   		out.println("결과: "+result);
      
      
   } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } finally {
      if( sqlSession != null ) sqlSession.close();
      if( is != null ) try { is.close(); } catch(IOException e) {}
   }
%>
</body>
</html>

xml - java 
=> pojo java (순수/인터페이스) + 어노테이션
xml
select deptno, dname, loc from dept where deptno=10
      
우편번호 검색기
annotation

 

1. DML (select..delete)

2. DDL

     create table

     drop table

 

mybatis – MyBatis 3 | Dynamic SQL

 

mybatis – MyBatis 3 | Dynamic SQL

Dynamic SQL One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, mak

mybatis.org

=> mybatis 구문 참고 사이트

 

mapper.xml을 통해 테이블 생성 / 삭제하기 (쿼리문 조작)

- 기본 java project에 생성 (관련 라이브러리 추가하기 => 나같은 경우에는 apis에 있는 라이브러리 다 추가했다)

 

* 테이블 생성 java코드

//MyBatisEx01.java
import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


public class MyBatisEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		// 파일의 존재 유무를 따짐
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		SqlSession sqlSession = null;
		try {
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			sqlSession = sqlSessionFactory.openSession(true);
			
			int result = sqlSession.update("createTable1");
			//String sql = "create table testble2 (col1 varchar(10))";
			//int result = sqlSession.update("createTable2",sql);
			
			System.out.println("설정 호출 성공");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(is != null) try{ is.close(); } catch(IOException e) {}
		}
	}

}

 

 

* 테이블 삭제 java 코드

<!--MyBatisEx02.java-->
import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


public class MyBatisEx02 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		// 파일의 존재 유무를 따짐
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		SqlSession sqlSession = null;
		try {
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			sqlSession = sqlSessionFactory.openSession(true);
			
			int result = sqlSession.update("dropTable1");
			
			System.out.println("설정 호출 성공");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(is != null) try{ is.close(); } catch(IOException e) {}
		}
	}

}

 

* 테이블 생성/삭제 쿼리문이 있는 xml 

<!--mapper.xml-->

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">


	<update id="createTable1">
		create table testtbl1(
		col1 varchar(10),
		col2 varchar(20)
		)
		
	</update>
	
	
	<update id="createTable2" parameterType="String">
		${value}
	</update>
	
	<update id="dropTable1">
		drop table if exists testtbl1
	</update>
</mapper>

 

 

.

.

.

.

* namespace에 따라 불러오는 데이터가 달라지게 하기

//MyBatisEx03.java

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import model1.DeptTO2;


public class MyBatisEx03 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		// 파일의 존재 유무를 따짐
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		SqlSession sqlSession = null;
		try {
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			sqlSession = sqlSessionFactory.openSession(true);
			
			DeptTO2 to = sqlSession.selectOne("mybatis1.selectone1");   //다르게 설정한 namespace를 지정
			
			System.out.println(to.getDeptno());
			System.out.println(to.getDname());
			System.out.println(to.getLoc());
			
			System.out.println("설정 호출 성공");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(is != null) try{ is.close(); } catch(IOException e) {}
		}
	}

}
<!--mapper1.xml-->

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis1">

	<!-- sql의 반복이 발생하기 때문에 sql을 미리 선언하고 사용가능 -->
	<sql id="userColumns">deptno,dname</sql>

	<!-- mapper1.xml -->
	<select id="selectone1" resultType="model1.DeptTO2">   
		select <include refid="userColumns"/>
		from dept
		where deptno=10
	</select>

	<select id="selectone2" resultType="model1.DeptTO2">
		select <include refid="userColumns"/>, loc
		from dept
		where deptno=20
	</select>

	<select id="selectone3" resultType="model1.DeptTO2">
		select <include refid="userColumns"/>
		from dept
		where deptno=30
	</select>
	
</mapper>

쿼리문 치환

- <include refid=~ 는 문자열 취급하여 쿼리문을 이어 작성해도 무방하다

  (치환으로 사용, xml방식만 가능 => 어노테이션 방식은 불가능)

 

<!--mapper2.xml-->

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis2">   <!-- namespace의 이름을 다르게 설정하여 같은 id 여도 다르게 호출 가능-->
	<!-- mapper2.xml -->
	<select id="selectone1" resultType="model1.DeptTO2">
		select deptno, dname, loc
		from dept
		where deptno=20
	</select>
	
	
</mapper>

=> xml 파일이 달라도 namespace의 값으로 참조 가능

 

 

*검색기능 추가 mybatisboardex04(action사용한 보드프로젝트) , mybatisboardEx01(.do 사용한 보드 프로젝트) 

   => 정리하기

 

728x90
반응형