<?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="Employee">

  <!-- 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="GridEmpResult" class="demo19.GridEmpVO">
    <result property="empCode" column="EMP_CODE"/>
    <result property="firstName" column="FIRST_NAME"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="taskVO" resultMap="Employee.InnerTaskResult"/>  
  </resultMap>
  
  <resultMap id="EmpResult" class="demo19.EmpVO">
    <result property="empCode" column="EMP_CODE"/>
    <result property="firstName" column="FIRST_NAME"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="sex" column="SEX"/>  
    <result property="salary" column="SALARY"/>  
    <result property="hireDate" column="HIRE_DATE"/>  
    <result property="note" column="NOTE"/> 
    <result property="taskVO" resultMap="Employee.InnerTaskResult"/>  
  </resultMap>
  
  <resultMap id="InnerTaskResult" class="demo19.TaskVO">
    <result property="taskCode" column="TASK_CODE"/>  
    <result property="description" column="DESCRIPTION"/>
    <result property="status" column="STATUS"/>
  </resultMap>
  
  <!-- Select with no parameters using the result map for Emp class. -->
  <select id="getEmps" resultMap="GridEmpResult">
    select E.EMP_CODE,E.FIRST_NAME,E.LAST_NAME,T.TASK_CODE,T.DESCRIPTION,T.STATUS
    from EMP E 
    inner join TASKS T
    on E.TASK_CODE = T.TASK_CODE
    <dynamic prepend=" where ">
      <iterate property="filteredColumns" conjunction=" and ">
          <isEqual property="filteredColumns[].attributeName" compareValue="empCode"> EMP_CODE  </isEqual> 
          <isEqual property="filteredColumns[].attributeName" compareValue="taskVO.taskCode"> TASK_CODE  </isEqual> 
          $filteredColumns[].operator$ #filteredColumns[].value#
      </iterate>
    </dynamic>
    <dynamic prepend=" order by ">
      <iterate property="sortedColumns" conjunction=",">
        <isEqual property="sortedColumns[].attributeName" compareValue="empCode"> EMP_CODE  </isEqual> 
        <isEqual property="sortedColumns[].attributeName" compareValue="firstName"> FIRST_NAME  </isEqual> 
        $sortedColumns[].versus$
      </iterate>
    </dynamic>
  </select>

  <select id="getEmp" parameterClass="java.lang.String" resultMap="EmpResult">
    select E.EMP_CODE,E.FIRST_NAME,E.LAST_NAME,E.SEX,E.SALARY,E.HIRE_DATE,E.NOTE,T.TASK_CODE,T.DESCRIPTION,T.STATUS
    from EMP E 
    inner join TASKS T
    on E.TASK_CODE = T.TASK_CODE
    where EMP_CODE = #empCode#
  </select>
   
  <!-- Insert example, using the Emp parameter class -->
  <insert id="insertEmp" parameterClass="demo19.EmpVO">
    insert into EMP (
      EMP_CODE,
      FIRST_NAME,
      LAST_NAME,
      SEX,
      SALARY,
      HIRE_DATE,
      NOTE,
      TASK_CODE
    ) values (
      #empCode#,#firstName#,#lastName#,#sex#,#salary#,#hireDate#,#note#,#taskVO.taskCode#
    )
  </insert>

  <!-- Update example, using the old and new EmpVO objects -->
  <update id="updateEmp" parameterClass="demo19.EmpVOs">
    update EMP set
      EMP_CODE = #newVO.empCode#,
      FIRST_NAME = #newVO.firstName#,
      LAST_NAME = #newVO.lastName#,
      SEX = #newVO.sex#,
      SALARY = #newVO.salary#,
      HIRE_DATE = #newVO.hireDate#,
      NOTE = #newVO.note#,
      TASK_CODE = #newVO.taskVO.taskCode#
    where
      EMP_CODE = #oldVO.empCode# and
      FIRST_NAME = #oldVO.firstName# and
      LAST_NAME = #oldVO.lastName# and
      SEX = #oldVO.sex# and
      <isNotNull property="oldVO.salary">SALARY = #oldVO.salary# and</isNotNull>
      <isNotNull property="oldVO.hireDate">HIRE_DATE = #oldVO.hireDate# and</isNotNull>
      <isNotNull property="oldVO.note">NOTE = #oldVO.note# and</isNotNull>
      TASK_CODE = #oldVO.taskVO.taskCode#
  </update>

  <!-- Delete example, using a String as the parameter class -->
  <delete id="deleteEmp" parameterClass="java.lang.String">
    delete from EMP where EMP_CODE = #empCode#
  </delete>

</sqlMap>