IBatis 시작하기 2

퍼온곳 : http://micropilot.tistory.com/trackback/481

 

iBATIS를 이용하여 오라클의 기본 테이블인 EMP 에 접근하여 레코드 하나를 출력하는 예제
설치했을 때의 내용과 동일한 부분을 제외하고 수정된 부분만 게시함.

EMP.java ( EMP 테이블과 매핑될 빈 클래스 )

package test;

/**EMP 테이블과 매핑될 Bean 클래스*/
public class EMP {
/* EMP 테이블의 구조
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
*/
 private int empno;
 private String ename;
 private String job;
 private int mgr;
 private java.sql.Date hiredate;
 private int sal;
 private int comm;
 private int deptno;
 
 public int getEmpno() {
  return empno;
 }
 public void setEmpno(int empno) {
  this.empno = empno;
 }
 public String getEname() {
  return ename;
 }
 public void setEname(String ename) {
  this.ename = ename;
 }
 public String getJob() {
  return job;
 }
 public void setJob(String job) {
  this.job = job;
 }
 public int getMgr() {
  return mgr;
 }
 public void setMgr(int mgr) {
  this.mgr = mgr;
 }
 public java.sql.Date getHiredate() {
  return hiredate;
 }
 public void setHiredate(java.sql.Date hiredate) {
  this.hiredate = hiredate;
 }
 public int getSal() {
  return sal;
 }
 public void setSal(int sal) {
  this.sal = sal;
 }
 public int getComm() {
  return comm;
 }
 public void setComm(int comm) {
  this.comm = comm;
 }
 public int getDeptno() {
  return deptno;
 }
 public void setDeptno(int deptno) {
  this.deptno = deptno;
 }
}

EMP.xml (맵 파일)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="EMP">

  <!-- Use type aliases to avoid typing the full classname every time. -->

  <typeAlias alias="EMP" type="test.EMP"/>

  <!-- Result maps describe the mapping between the columns returned
       from a query, and the class properties.  A result map isn't
       necessary if the columns (or aliases) match to the properties
       exactly. -->

  <resultMap id="EMPResult" class="EMP">
    <result property="empno" column="EMPNO"/>
    <result property="ename" column="ENAME"/>
    <result property="job" column="JOB"/>
    <result property="mgr" column="MGR"/>
    <result property="hiredate" column="HIREDATE"/>
    <result property="sal" column="SAL"/>
    <result property="comm" column="COMM"/>
    <result property="deptno" column="DEPTNO"/>
  </resultMap>

  <!-- Select with no parameters using the result map for EMP class. -->

  <select id="selectAllEmp" resultMap="EMPResult">
    select * from EMP
  </select>

<!-- Use primitive wrapper type (e.g. Integer) as parameter and allow results to
be auto-mapped results to Person object (JavaBean) properties. A simpler select example without the result map.  Note the aliases to match the properties of the target result class. -->

<select id="getEmp" parameterClass="int" resultClass="EMP">
   SELECT
      EMPNO as empno,
      ENAME as ename,
      JOB as job,
      COALESCE(MGR, 0) as mgr,<!--null 일경우 0으로 대치-->
      HIREDATE as hiredate,
      SAL as sal,
      COALESCE(COMM, 0) as comm,
      DEPTNO as deptno
      FROM EMP
   WHERE EMPNO = #value#
</select>

</sqlMap>

주설정 파일(SqlMapConfigExample.xml)의 변경된 부분은 내용의 맨 아래에 EMP.xml 파일을 등록한 곳 뿐이다.

 .... 
  <sqlMap resource="Person.xml" />
  <sqlMap resource="EMP.xml" /> 
</sqlMapConfig>

EmpServlet.java

import java.io.*;
import javax.servlet.http.*;
import test.MyAppSqlConfig;
import test.EMP;
import com.ibatis.sqlmap.client.SqlMapClient;

 public class EmpServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException  {
  PrintWriter out = response.getWriter();
  SqlMapClient sqlMap = MyAppSqlConfig.getSqlMapInstance();
  try{
   Integer empno = new Integer(7839);
   EMP emp = (EMP) sqlMap.queryForObject ("getEmp", empno);
   out.print("EMPNO(7839)<hr>");
   out.println(emp.getEmpno()+" "+ emp.getEname()+" "+emp.getJob()+" "+emp.getMgr()+" "+emp.getHiredate()+" "+emp.getSal()+" "+emp.getComm()+" "+emp.getDeptno());

  }catch(Exception e){
   out.println(e);
  }     
 }
 }

실행결과 화면