iBATIS SELECT * from EMP

iBATIS에서 SELECT * from EMP 와 같이 컬럼명을 사용하지 않고 모든 레코드를 선택하여 가져오는 경우 주의할 점이 있다. 컬럼에 null 이 설정되어 있다면 iBATIS 가 빈객체와 EMP 레코드를 매핑하는 과정에서 에러가 발생한다.

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 Account class. -->

  <select id="selectAllEmp" resultMap="EMPResult">
    <!--
    select * from EMP
    모든 컬럼이 null 이 아닌 경우에는 위의 Query 가 문제 없지만, 컬럼에 null이 들어 있다면...
    매핑과정에서 net.sf.cglib.beans.BulkBeanException 예외발생하므로 아래처럼 ....
    -->

  SELECT
      EMPNO,
      ENAME,
      JOB,
      COALESCE(MGR, 0) as MGR,
      HIREDATE,
      SAL,
      COALESCE(COMM, 0) as COMM,
      DEPTNO
   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>

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

   java.util.List list = sqlMap.queryForList("selectAllEmp");
  
   for(int i=0;i<list.size();i++) {
    emp = (EMP) list.get(i);
    out.println(emp.getEmpno()+" "+ emp.getEname()+" "+emp.getJob()+" "+emp.getMgr()+" "+emp.getHiredate()+" "+emp.getSal()+" "+emp.getComm()+" "+emp.getDeptno());
    out.println("<br>");
   }
  }catch(Exception e){
   out.println(e);
  }     
 }
 }

실행결과