02day Mybatis

1.select 해쉬맵으로 행처리

log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
     <appender name="console" class="org.apache.log4j.ConsoleAppender">
          <layout class="org.apache.log4j.PatternLayout">
               <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} [%-5p](%-35c{1}:%-3L) %m%n" />
          </layout>
     </appender>

     <root>
          <level value="DEBUG" />
          <appender-ref ref="console" />
    </root>
</log4j:configuration>

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="Mapper.xml"/>
    </mappers>
</configuration>

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">
    <select id="select2" parameterType="String" resultType="model1.EmpTO">
        select *
        from emp
        where ename=upper(#{ename}) <!-- 입력값 대문자로 바꾸기 -->
    </select>
    <select id="select3" parameterType="String" resultType="java.util.HashMap">  <!-- 해쉬맵으로 결과 받기 -->
        select *
        from emp
        where ename=upper(#{ename})
    </select>
</mapper>

ApplicationMainEx2.java

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

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 ApplicationMainEx2 {

 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);
   
   //해쉬맵으로 select 결과 받기
   Map map = sqlSession.selectOne("select3","scott");
   
   System.out.println("크기 : " + map.size());
   System.out.println("empno : " + map.get("empno".toUpperCase()));  //oracle에 경우에 컬럼명 대문자 처리를 해주어야 함
   System.out.println("hiredate : " + map.get("hiredate".toUpperCase()));
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

2. select 여러행 가져오기
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">
    <select id="selectList" resultType="model1.DeptTO">
        select deptno, dname, loc
        from dept
    </select>
</mapper>

DeptTO.java

package model1;

public class DeptTO {
 private String deptno;
 private String  dname;
 private String loc;
 
 public String getDeptno() {
  return deptno;
 }
 public void setDeptno(String deptno) {
  this.deptno = deptno;
 }
 public String getDname() {
  return dname;
 }
 public void setDname(String dname) {
  this.dname = dname;
 }
 public String getLoc() {
  return loc;
 }
 public void setLoc(String loc) {
  this.loc = loc;
 }
 
}

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

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);
            
            /*select 여러행을 가져오기*/
            //DeptTO to1 = (DeptTO)sqlSession.selectOne("selectList");  //한개 가져올 경우 에러뜸
            List<DeptTO> deptList = (List)sqlSession.selectList("selectList");
            
            System.out.println("갯수 : " + deptList.size());
            for(int i=0; i<deptList.size(); i++){
                DeptTO to = deptList.get(i);
                System.out.println("deptno : "+to.getDeptno()+"  //  dname : "+to.getDname());
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

3. 이름 첫글자 매개변수 받고 리스트 출력
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">
    <!-- 이름에 첫글자를 넣으면 리스트를 출력하는 구문 -->
    <select id="selectList" resultType="model1.EmpTO">
        select *
        from emp
        where ename like upper(#{ename} || '%')
    </select>
</mapper>

ApplicationMainEx2.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 ApplicationMainEx2 {

    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);

            /*select 여러행을 가져오기*/
            //이름에 첫글자를 넣으면 리스트를 출력
            List<EmpTO> deptList = (List)sqlSession.selectList("selectList","s");
            
            System.out.println("갯수 : " + deptList.size());
            for(int i=0; i<deptList.size(); i++){
                EmpTO to = deptList.get(i);
                System.out.println("deptno : "+to.getDeptno()+"  //  dname : "+to.getEname());
            }
            
        } catch (IOException e) {
            System.out.println("에러 : " + e.getMessage());
        } finally{
            if(sqlSession !=null) sqlSession.close();
            if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
        }
    }

}

4.select 한개행 해쉬맵으로, 여러행 리스트로 받기
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">
    <select id="selectList2" resultType="java.util.HashMap"><!-- 해쉬맵으로 행입력값 받기 -->
        select *
        from emp e, salgrade s
        where e.sal between s.losal and s.hisal
    </select>
</mapper>

ApplicationMainEx2.java

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
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;

public class ApplicationMainEx2 {

    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);
            
            /*select 여러행을 가져오기*/
            List<HashMap<String, Object>> empList = sqlSession.selectList("selectList2");
            for(int i=0; i<empList.size(); i++){
                HashMap<String,Object> map = empList.get(i);
                System.out.print(map.get("EMPNO")+"\t");
                System.out.print(map.get("ENAME")+"\t");
                System.out.print(map.get("SAL")+"\t");
                System.out.print(map.get("GRADE")+"\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. Mybatis tomcat에서 쓰기
java EE에서 webProject 만들기

라이브러리,설정파일 복사하기 폴더구조

기본 JSP파일

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>

<%
    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);
        System.out.println("데이터 베이스에 연결됨");
    }catch(IOException e){
        System.out.println("에러 : "+e.getMessage());
    }finally{
        if(sqlSession !=null) sqlSession.close();
        if(inputStream !=null) inputStream.close();
    }
%>

6. 톰캣에서 select
폴더구조

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">
    <select id="selectList1" resultType="model1.DeptTO">
        select *
        from dept
    </select>
</mapper>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>

<%@page import="model1.DeptTO"%>
<%@page import="java.util.List"%>

<%
    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);
        System.out.println("데이터 베이스에 연결됨");
        
        List<DeptTO> deptList = sqlSession.selectList("selectList1");
        for(DeptTO to: deptList){
            out.println("deptno : "+ to.getDeptno() +"<br />");
            out.println("dname : "+ to.getDname() +"<br />");
        }
        
    }catch(IOException e){
        System.out.println("에러 : "+e.getMessage());
    }finally{
        if(sqlSession !=null) sqlSession.close();
        if(inputStream !=null) inputStream.close();
    }
%>

7. context.xml 을써서 pooling방식으로 톰캣에서 데이터베이스 연결하기

폴더구조

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">
    <select id="selectList1" resultType="model1.DeptTO">
        select *
        from dept
    </select>
</mapper>

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="development2"><!-- 디폴트를 2번으로 바꿈 -->
        <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>
        <environment id="development2"><!-- JNDI를 통한 접속 -->
            <transactionManager type="JDBC" />
            <dataSource type="JNDI">
                <property name="data_source" value="java:comp/env/jdbc/oracle"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="Mapper.xml"/><!-- 매핑할 sql문이 있는 xml파일을 적음 -->
    </mappers>
</configuration>

context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>
    <Resource 
        name="jdbc/oracle" 
        auth="Container" 
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.driver.OracleDriver" 
        url="jdbc:oracle:thin:@localhost:1521:orcl"
        username="scott" 
        password="tiger" 
        maxTotal="20" 
        maxWaitMillis="5000" />    
</Context>

ApplicationEx01.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>

<%@page import="model1.DeptTO"%>
<%@page import="java.util.List"%>

<%
    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);
        System.out.println("데이터 베이스에 연결됨");
        
        List<DeptTO> deptList = sqlSession.selectList("selectList1");
        for(DeptTO to: deptList){
            out.println("deptno : "+ to.getDeptno() +"<br />");
            out.println("dname : "+ to.getDname() +"<br />");
        }
        
    }catch(IOException e){
        System.out.println("에러 : "+e.getMessage());
    }finally{
        if(sqlSession !=null) sqlSession.close();
        if(inputStream !=null) inputStream.close();
    }
%>