03day Mybatis

동적 SQL문 만들기
http://www.mybatis.org/mybatis-3/ko/dynamic-sql.html

1. sql문 선언

폴더구조

Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">
    <sql id="userColumns">empno, ename, job, mgr</sql> <!-- 자주쓰는 sql 구문을 선언 -->
    <select id="selectList1" resultType="model1.EmpTO">
        select <include refid="userColumns" /><!-- 선언 sql 구문 사용 -->
        from emp
    </select>
    <select id="selectList2" parameterType="String" resultType="model1.EmpTO">
        select <include refid="userColumns" />
        from emp
        where ename like upper(#{ename} || '%')
    </select>
</mapper>

ApplicationMainEx1.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.EmpTO;

public class ApplicationMainEx1 {

    public static void main(String[] args) {
        String resource = "myBatisConfig.xml";
        InputStream inputStream = null;
        SqlSession sqlSession = null;
        
        
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            System.out.println("설정이 성공적으로 호출됨");
            
            sqlSession = sqlSessionFactory.openSession(true);
            
            //List<EmpTO> empList = sqlSession.selectList("selectList1");
            List<EmpTO> empList = sqlSession.selectList("selectList2","s");
            
            System.out.println("갯수 : " + empList.size());
            for(int i=0; i<empList.size(); i++){
                EmpTO to = empList.get(i);
                System.out.print("empno : "+to.getEmpno()+"\t");
                System.out.print("ename : "+to.getEname()+"\t");
                System.out.print("job : "+to.getJob()+"\t");
                System.out.print("mgr : "+to.getMgr()+"\n");
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

2. 매퍼를 여러개 만들어서 관리

폴더구조

myBatisConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development1">
        <environment id="development1">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
                <property name="username" value="scott"/>
                <property name="password" value="tiger"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="Mapper1.xml"/><!-- 여러개의 매퍼를 만들어서 분류할 수 있음 -->
        <mapper resource="Mapper2.xml"/>
    </mappers>
</configuration>

Mapper1.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">
    <sql id="userColumns">empno, ename, job, mgr</sql> <!-- 자주쓰는 sql 구문을 선언 -->
    <select id="selectList1" resultType="model1.EmpTO">
        select <include refid="userColumns" /><!-- 선언 sql 구문 사용 -->
        from emp
    </select>
</mapper>

Mapper2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">
    <!-- 선언문 중복으로 적으면 에러남 -->
    <select id="selectList2" parameterType="String" resultType="model1.EmpTO">
        select <include refid="userColumns" /><!-- 두번째 매퍼에 첫번째 매퍼에 선언한 sql구문을 사용할 수 있음 -->
        from emp
        where ename like upper(#{ename} || '%')
    </select>
</mapper>

ApplicationMainEx1.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.EmpTO;

public class ApplicationMainEx1 {

    public static void main(String[] args) {
        String resource = "myBatisConfig.xml";
        InputStream inputStream = null;
        SqlSession sqlSession = null;
        
        
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            System.out.println("설정이 성공적으로 호출됨");
            
            sqlSession = sqlSessionFactory.openSession(true);
            
            //List<EmpTO> empList = sqlSession.selectList("selectList1");
            List<EmpTO> empList = sqlSession.selectList("selectList2","s");
            
            System.out.println("갯수 : " + empList.size());
            for(int i=0; i<empList.size(); i++){
                EmpTO to = empList.get(i);
                System.out.print("empno : "+to.getEmpno()+"\t");
                System.out.print("ename : "+to.getEname()+"\t");
                System.out.print("job : "+to.getJob()+"\t");
                System.out.print("mgr : "+to.getMgr()+"\n");
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

3. 여러개 매퍼 별도로 설정
myBatisConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development1">
        <environment id="development1">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
                <property name="username" value="scott"/>
                <property name="password" value="tiger"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="Mapper1.xml"/><!-- 여러개의 매퍼를 만들어서 분류할 수 있음 -->
        <mapper resource="Mapper2.xml"/>
    </mappers>
</configuration>

Mapper1.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis1"><!-- 별도의 매퍼로 분리하고 싶으면 namespace값을 다르게 설정 -->
    <sql id="userColumns">empno, ename, job, mgr</sql> <!-- 자주쓰는 sql 구문을 선언 -->
    <select id="selectList1" resultType="model1.EmpTO">
        select <include refid="userColumns" /><!-- 선언 sql 구문 사용 -->
        from emp
    </select>
</mapper>

Mapper2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis2"><!-- 별도의 매퍼로 분리하고 싶으면 namespace값을 다르게 설정 -->
    <sql id="userColumns">empno, ename, job, mgr</sql><!-- 별도로 sql문 선언해 주어야함  -->
    <select id="selectList2" parameterType="String" resultType="model1.EmpTO">
        select <include refid="userColumns" />
        from emp
        where ename like upper(#{ename} || '%')
    </select>
</mapper>

ApplicationMainEx1.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.EmpTO;

public class ApplicationMainEx1 {

    public static void main(String[] args) {
        String resource = "myBatisConfig.xml";
        InputStream inputStream = null;
        SqlSession sqlSession = null;
        
        
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            System.out.println("설정이 성공적으로 호출됨");
            
            sqlSession = sqlSessionFactory.openSession(true);
            
            //List<EmpTO> empList = sqlSession.selectList("selectList1");
            List<EmpTO> empList = sqlSession.selectList("mybatis2.selectList2","s");  //namespace="mybatis2"
            
            System.out.println("갯수 : " + empList.size());
            for(int i=0; i<empList.size(); i++){
                EmpTO to = empList.get(i);
                System.out.print("empno : "+to.getEmpno()+"\t");
                System.out.print("ename : "+to.getEname()+"\t");
                System.out.print("job : "+to.getJob()+"\t");
                System.out.print("mgr : "+to.getMgr()+"\n");
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

4. 매퍼파일 annotation으로 만들기

• @(): 알림
• POJO(Plain Old Java Object): 객체간의 상속을 배재함, 독립클래스로 만듦 = 테스트할 때 변경을 해도 하위객체에 영항 안 미침

매퍼 interface만들기

폴더구조 : mapper.xml 없음

myBatisConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development1"><!-- 기본으로 적용할 환경 설정의 아이디를 적음 -->
        <environment id="development1"><!-- 한개의 환경 설정을 정의함 -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
                <property name="username" value="scott"/>
                <property name="password" value="tiger"/>
            </dataSource>
        </environment>
    </environments>
    <!-- annotation 기법은 mapper연결이 필요 없음 -->
</configuration>

SqlMapperInter.java

import java.util.List;

import org.apache.ibatis.annotations.Select;

import model1.EmpTO;

public interface SqlMapperInter {

    @Select("select * from emp")      //select 내용 구문
    public List<EmpTO> selectList();  //id 역할을 함
    
}

ApplicationMainEx1.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.EmpTO;

public class ApplicationMainEx1 {

    public static void main(String[] args) {
        String resource = "myBatisConfig.xml";
        InputStream inputStream = null;
        SqlSession sqlSession = null;
        
        
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            System.out.println("설정이 성공적으로 호출됨");
            
            sqlSession = sqlSessionFactory.openSession(true);
            
            //매퍼 클래스를 인식
            Class[] mapper = {SqlMapperInter.class};
            for(Class m : mapper){
                sqlSession.getConfiguration().addMapper(m);
            }
            SqlMapperInter inter = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
            
            //anotation mapper 실행
            List<EmpTO> empList = inter.selectList();
            
            System.out.println("갯수 : " + empList.size());
            for(int i=0; i<empList.size(); i++){
                EmpTO to = empList.get(i);
                System.out.print("empno : "+to.getEmpno()+"\t");
                System.out.print("ename : "+to.getEname()+"\t");
                System.out.print("job : "+to.getJob()+"\t");
                System.out.print("mgr : "+to.getMgr()+"\n");
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

5. annotation 매퍼 sql추가
SqlMapperInter.java

import java.util.List;

import org.apache.ibatis.annotations.Select;

import model1.EmpTO;

public interface SqlMapperInter {

    @Select("select * from emp")      //select 내용 구문
    public List<EmpTO> selectList();  //id 역할을 함
    
    //매퍼 sql구문 추가
    @Select("select * from emp where ename like upper(#{ename} || '%')")  //select 내용 구문
    public List<EmpTO> selectByEname(String ename);                       //id 역할을 함
}

ApplicationMainEx1.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.EmpTO;

public class ApplicationMainEx1 {

    public static void main(String[] args) {
        String resource = "myBatisConfig.xml";
        InputStream inputStream = null;
        SqlSession sqlSession = null;
        
        
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            System.out.println("설정이 성공적으로 호출됨");
            
            sqlSession = sqlSessionFactory.openSession(true);
            
            //매퍼 클래스를 설정 config 인식
            Class[] mapper = {SqlMapperInter.class};
            for(Class m : mapper){
                sqlSession.getConfiguration().addMapper(m);
            }
            SqlMapperInter inter = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
            
            //anotation mapper 실행
            //List<EmpTO> empList = inter.selectList();
            List<EmpTO> empList = inter.selectByEname("s");
            
            System.out.println("갯수 : " + empList.size());
            for(int i=0; i<empList.size(); i++){
                EmpTO to = empList.get(i);
                System.out.print("empno : "+to.getEmpno()+"\t");
                System.out.print("ename : "+to.getEname()+"\t");
                System.out.print("job : "+to.getJob()+"\t");
                System.out.print("mgr : "+to.getMgr()+"\n");
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}