# 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
