01day Mybatis

마이바티스
http://blog.mybatis.org/p/products.html

마이바티스 라이브러리 다운

Mybatis 라이브러리 보관용 폴더 만들기

이클립스 설정

•    perspective java로 바꿈
•    java project 생성
•    설정 xml파일 3개
•    라이브러리 추가

설정파일

• log4j
• mybatis안에 들어있는 log4j라는 별도의 프레임 워크
• 로그처리 전용 프레임 워크 (옵션을 키면 로그가 찍힘)
• Mapper.xml (이름 변경가능)
• SQL문 저장 매퍼파일
• myBatisConfig.xml (이름 변경가능)
• mybatis 환경 설정 파일  -데이터베이스 연결에 관한 사항을 적음

1. 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>

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>
        <environment id="development2"><!-- 여러개의 환경설정을 만들어서 oracle, mysql 등에 접속할 수 있음 -->
            <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"/><!-- 매핑할 sql문이 있는 xml파일을 적음 -->
    </mappers>
</configuration>

ApplicationMainEx01.java -- 설정 실행확인 자바파일

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

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

public class ApplicationMainEx01 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  String resource = "myBatisConfig.xml";
  InputStream inputStream = null;
  
  try {
   inputStream = Resources.getResourceAsStream(resource);
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   System.out.println("설정이 성공적으로 호출됨");
  } catch (IOException e) {
   // TODO Auto-generated catch block
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

3. Mapper.xml ---sql 문을 여기에 적음

<?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">
    <insert id="insert1" parameterType="DeptTO">
       insert into dept (deptno, dname, loc)
       values (#{deptno}, #{dname}, #{loc})
    </insert>
</mapper>

DeptTO.java ---데이터용 클래스

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

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

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   DeptTO to = new DeptTO();
   to.setDeptno("81");
   to.setDname("개발");
   to.setLoc("서울");
   
   int result = sqlSession.insert("insert1",to);  //result값 영향받은 행의 갯수  //insert1 매퍼.xml 아이디 값
   if(result >0){
    // sqlSession.commit();  수동 커밋
    System.out.println("입력성공");
   }else{
    System.out.println("입력실패");
   }
   
  } catch (IOException e) {
   // TODO Auto-generated catch block
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

4. 패키지안에 들어가있을때 주소를 추가해줌

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">
    <insert id="insert1" parameterType="model1.DeptTO"><!-- 패키지명 model1을 적어줌 -->
       insert into dept (deptno, dname, loc)
       values (#{deptno}, #{dname}, #{loc})
    </insert>
    
    <update id="update1" parameterType="model1.DeptTO">
        update dept
        set dname = #{dname}
        where deptno = #{deptno}
    </update>
</mapper>

ApplicationMainEx2.java ----update 구문 실행

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

public class ApplicationMainEx2 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   DeptTO to = new DeptTO();
   to.setDeptno("83");
   to.setDname("연구");
   
   int result = sqlSession.update("update1",to);  //update
   if(result >0){
    System.out.println("입력성공");
   }else{
    System.out.println("입력실패");
   }
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

5. 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">
    <insert id="insert1" parameterType="model1.DeptTO"><!-- 패키지명 model1을 적어줌 -->
       insert into dept (deptno, dname, loc)
       values (#{deptno}, #{dname}, #{loc})
    </insert>
    
    <update id="update1" parameterType="model1.DeptTO">
        update dept
        set dname = #{dname}
        where deptno = #{deptno}
    </update>
    
    <delete id="delete1" parameterType="model1.DeptTO">
        delete from dept
        where deptno = #{deptno}
    </delete>
</mapper>

ApplicationMainEx3.java ---delete 구문

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

public class ApplicationMainEx3 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   DeptTO to = new DeptTO();
   to.setDeptno("83");
   
   int result = sqlSession.delete("delete1",to);  //delete
   if(result >0){
    System.out.println("삭제성공");
   }else{
    System.out.println("삭제실패");
   }
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

6.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">
    <insert id="insert1" parameterType="model1.DeptTO">
       insert into dept (deptno, dname, loc)
       values (#{deptno}, #{dname}, #{loc})
    </insert>
    
    <update id="update1" parameterType="model1.DeptTO">
        update dept
        set dname = #{dname}
        where deptno = #{deptno}
    </update>
    
    <delete id="delete1" parameterType="model1.DeptTO"><!-- 데이터 여러개 넘길때 객체로 넘김 -->
        delete from dept
        where deptno = #{deptno}
    </delete>
    <delete id="delete2" parameterType="String"><!-- 데이터 하나만 넘길때 String -->
        delete from dept
        where deptno = #{deptno}
    </delete>
</mapper>

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

public class ApplicationMainEx4 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   int result = sqlSession.delete("delete2","81");  //delete 숫자하나만 넘김
   if(result >0){
    System.out.println("삭제성공");
   }else{
    System.out.println("삭제실패");
   }
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

7. 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="ERROR" /><!-- ERROR로 바꿈 -->
          <appender-ref ref="console" />
    </root>
</log4j:configuration>

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

public class ApplicationMainEx4 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   int result = sqlSession.delete("delete2","82");  //delete 숫자하나만 넘김
   if(result >0){
    System.out.println("삭제성공");
   }else{
    System.out.println("삭제실패");
   }
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

8. Mapper.xml --select 한개 행만

<?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">
    <insert id="insert1" parameterType="model1.DeptTO">
       insert into dept (deptno, dname, loc)
       values (#{deptno}, #{dname}, #{loc})
    </insert>
    
    <update id="update1" parameterType="model1.DeptTO">
        update dept
        set dname = #{dname}
        where deptno = #{deptno}
    </update>
    
    <delete id="delete1" parameterType="model1.DeptTO">
        delete from dept
        where deptno = #{deptno}
    </delete>
    <delete id="delete2" parameterType="String">
        delete from dept
        where deptno = #{deptno}
    </delete>
    
    <select id="select1" resultType="model1.DeptTO"><!-- select 한줄만 가져옴 -->
        select deptno, dname, loc
        from dept
        where deptno=10
    </select>
    <select id="select2" parameterType="String" resultType="model1.DeptTO"> <!-- select할 부서번호 한개 받음 -->
        select deptno, dname, loc
        from dept
        where deptno=#{deptno}
    </select>
    <select id="select3" parameterType="model1.DeptTO" resultType="model1.DeptTO"> <!-- select할 조건변수 2개이상 받음 -->
        select deptno, dname, loc
        from dept
        where deptno=#{deptno} and dname = #{dname}
    </select>
</mapper>

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

public class ApplicationMainEx5 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   DeptTO to1 = (DeptTO)sqlSession.selectOne("select1"); //select
   System.out.println(to1.getDeptno());
   System.out.println(to1.getDname());
   
   DeptTO to2 = (DeptTO)sqlSession.selectOne("select2","20"); //select 조건변수 1개
   System.out.println(to2.getDeptno());
   System.out.println(to2.getDname());
 
   DeptTO to3 = new DeptTO();
   to3.setDeptno("10");
   to3.setDname("ACCOUNTING");
   to3 = (DeptTO)sqlSession.selectOne("select3", to3); //select 조건변수 여러개
   System.out.println(to3.getLoc());
   
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}

9. Mapper.xml ---emp테이블 select

<?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>
</mapper>

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


public class ApplicationMainEx01 {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  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);  //true 자동 커밋
   System.out.println("데이터베이스에 연결됨");
   
   EmpTO to2 = (EmpTO)sqlSession.selectOne("select2","scott"); //select 파라매터 "scott" 소문자로 씀
   System.out.println(to2.getEmpno());
   System.out.println(to2.getDeptno());
   System.out.println(to2.getEname());
   System.out.println(to2.getJob());
   System.out.println(to2.getHiredate());
   System.out.println(to2.getSal());
 
   
  } catch (IOException e) {
   System.out.println("에러 : " + e.getMessage());
  } finally{
   if(sqlSession !=null) sqlSession.close();
   if(inputStream !=null) try{inputStream.close();} catch(IOException e){}
  }
 }

}