Mybatis 테스트

# Mybatis 를 콘솔 어플리케이션 환경에서 테스트 

  - 한글소개 : http://mybatis.github.io/mybatis-3/ko/

  - 다운로드 : https://github.com/mybatis/mybatis-3/releases

  - SQL Server용 Microsoft JDBC Driver 4.0 : http://www.microsoft.com/ko-kr/download/details.aspx?id=11774

# mybatis.xml

  - mybatis 의 기본 설정 파일

  - 다중 DB 연결 관리(environment) 및 다중 매퍼파일 등록을 관리 

  - 다중 DB 연결 테스트를 위해 같은 DBMS의 DB 만 다른 설정 

<?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="development">
    <environment id="Glass">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=Glass"/>
        <property name="username" value="user"/>
        <property name="password" value="pass"/>
      </dataSource>
    </environment>
    <environment id="SecureDB">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=SecureDB"/>
        <property name="username" value="user"/>
        <property name="password" value="pass"/>
      </dataSource>
    </environment>
  </environments>
  
  <mappers>
    <mapper resource="net/cdecl/mapper.xml"/>
    <mapper resource="net/cdecl/mapper_securedb.xml"/>
  </mappers>
  
</configuration>

# mapper.xml

  - 매퍼파일

  - 결과매핑 클래스에 대한 정의 및 각 쿼리 및 파라미터, 결과 타입에 대한 정의 

  - 다중 파라미터 및 매핑된 구조(이거 꽤 귀찮을 일 일듯;;) 형태로 결과를 받지 않는다면 HashMap 구조로 처리 가능

  - 각각의 쿼리는 select (혹은 update, delete) 엘러먼트의 id 로 선택을 하며, 디폴트의 경우 이름만 적어줘도 문제 없는듯 하나 mapper namespace 와 더불러 full 경로를 적어 주는 것이 문제가 없어 보임   

<?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="net.cdecl.CodeDAO">
  
    <resultMap id="result" type="net.cdecl.Code">
        <result property="code" column="code"/>
        <result property="codename" column="codename"/>   
    </resultMap>
 
    <select id="selectAll" resultMap="result">
        SELECT * FROM tCode;
    </select>
    
    <select id="select"  parameterType="String" resultMap="result">
        SELECT * FROM tCode Where code = #{code}
    </select>
    
    
    <select id="select2" parameterType="hashmap" resultMap="result">
        SELECT * 
        FROM tCode 
        Where code In ( #{s1} ) 
    </select>
    
    <select id="selectMap" resultType="hashmap">
        SELECT * FROM tCode;
    </select>
    
    
</mapper>

# mapper_securedb.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="net.cdecl">
  
    <select id="selectSHA256" parameterType="String" resultType="hashmap">
        select [dbo].[xfn_IntCrypt_SHA1_Encoding](#{in}) as sha1, [dbo].[xfn_IntCrypt_SHA256_Encoding](#{in}) as sha256
    </select>
    
</mapper>

# Code.java

  - tCode 테이블의 데이터 클래스

package net.cdecl;

public class Code {
    private String code;
    private String codename;

    /*
    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getCodeName() {
        return codename;
    }

    public void setCodeName(String codename) {
        this.codename = codename;
    }
    */

    public String toString() {
        return "code: " + code + ", codename: " + codename;
    }
}

# CodeDAO.java

  - 매퍼 클래스와 매퍼 XML 구조를 매핑하는 Data Access Object 

package net.cdecl;

import java.util.ArrayList;
import java.util.Map;

import net.cdecl.*;

public interface CodeDAO {
 
    public ArrayList<Code> selectAll(); // 모든 행 가져오기
    public ArrayList<Code> select(String s);
    public ArrayList<Code> select2(Map<String, Object> m);

}

# MyBaApp.java 

  - 테스트 어플리케이션 

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;

import net.cdecl.*;

public class MyBaApp {
    
    public static void main(String[] args) {
        
        try (SqlSession session = GetSqlSession("Glass")) {
            session.getConfiguration().getMappedStatementNames();
            
            Mybatis();
            Mybatis_HashMap();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession GetSqlSession(String env) throws IOException {
        String resource = "net/cdecl/mybatis.xml";
        
        SqlSessionFactory sp = null;
        
        try (InputStream inputStream = Resources.getResourceAsStream(resource)) {
            sp = new SqlSessionFactoryBuilder().build(inputStream, env);
        }
        
        return sp.openSession();
    }

    public static void Mybatis_HashMap() {
            
        try (SqlSession session = GetSqlSession("SecureDB") ){
            System.out.println("======== SHA256 DB조회");
            
            List list = session.selectList("net.cdecl.selectSHA256", "abcedfg");
            System.out.println(list);
            for (int i = 0; i < list.size(); ++i) {
                HashMap<String, Object> m = (HashMap<String, Object>)list.get(i);
                String s = String.format("abcedfg - SHA1 : %s, SHA256 : %s", m.get("sha1"), m.get("sha256"));
                System.out.println(s);
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

    }

    public static void Mybatis() {
            
        try (SqlSession session = GetSqlSession("Glass")) {         
            CodeDAO code = session.getMapper(CodeDAO.class);
            
            System.out.println("======== 데이터 매핑 리턴 ");
            ArrayList<Code> Codes = code.selectAll();
            for (Code c : Codes) {
                System.out.println(c.toString());
            }
            
            System.out.println("======== 데이터 매핑 리턴, 다중 파라미터(HashMap) ");
            Map<String, Object> m = new HashMap<String, Object>();
            m.put("s1", "0");
            
            Codes = code.select2(m);            
            for (Code c : Codes) {
                System.out.println(c.toString());
            }
        
            System.out.println("======== List 리턴 List<HashMap<String, Object>> ");
            List list = session.selectList("net.cdecl.CodeDAO.selectMap");
            System.out.println(list);
            
            for (int i = 0; i < list.size(); ++i) {
                m = (HashMap<String, Object>)list.get(i);
                String s = String.format("%s %s", m.get("Code"), m.get("CodeName"));
                System.out.println(s);
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

    }
}

# 결과 

[select2, selectAll, selectSHA256, select, net.cdecl.CodeDAO.select, net.cdecl.CodeDAO.selectAll, net.cdecl.CodeDAO.select2, selectMap, net.cdecl.CodeDAO.selectMap, net.cdecl.selectSHA256]
======== 데이터 매핑 리턴 
code: 0, codename: zero
code: 1, codename: one
code: 2, codename: two
code: 3, codename: three
code: 4, codename: 4444
======== 데이터 매핑 리턴, 다중 파라미터(HashMap) 
code: 0, codename: zero
======== List 리턴 List<HashMap<String, Object>> 
[{CodeName=zero, Code=0}, {CodeName=one, Code=1}, {CodeName=two, Code=2}, {CodeName=three, Code=3}, {CodeName=4444, Code=4}]
0 zero
1 one
2 two
3 three
4 4444
======== SHA256 DB조회
[{sha1=470e2dac6d8e0b17412cceb91442659a9d561e02, sha256=34541528206d252e76bb2597687112b53aff7f70dd1da1d763dab4c59095bf89}]
abcedfg - SHA1 : 470e2dac6d8e0b17412cceb91442659a9d561e02, SHA256 : 34541528206d252e76bb2597687112b53aff7f70dd1da1d763dab4c59095bf89