ibatis 的一个小例子

<?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="com.newcross.vsg.platform.dao.map.Sector" >

  <typeAlias alias="Sector" type="com.newcross.vsg.platform.model.Sector" />

  

  <resultMap id="ResultMap" class="Sector" >

    <result column="SCT_ID" property="id" jdbcType="LONG" />

    <result column="SCT_NBR" property="sectorNumber" jdbcType="VARCHAR" />

    <result column="SCT_NAM" property="name" jdbcType="VARCHAR" />

    <result column="SCT_PID" property="parentId" jdbcType="LONG" />

    <result column="SCT_DESC" property="desc" jdbcType="VARCHAR" />

    <result column="MAP_LAYER_ID" property="mapLeayerId" jdbcType="INTEGER"/>

    <result column="HAS_SUB_MULT_LAYER" property="hasSubLAYER" jdbcType="VARCHAR"/>

    <result column="LOCATION" property="location" jdbcType="VARCHAR"/>

    <result column="SCT_CLR" property="sectorColor" jdbcType="VARCHAR" />

    <result column="TOP_PID" property="topPid" jdbcType="LONG"/>

    <result column="LVL" property="grade" jdbcType="INTEGER"/>

    <result column="CRT_TIME" property="createTime" jdbcType="TIMESTAMP" />

    <result column="CRT_BY" property="createBy" jdbcType="VARCHAR" />

    <result column="LST_UPD_TIME" property="lastUpdateTime" jdbcType="TIMESTAMP" />

    <result column="LST_UPD_BY" property="lastUpdateBy" jdbcType="VARCHAR" /> 

    <result column="VRSN_ID" property="versionId" jdbcType="LONG" />

  </resultMap>

  

  <resultMap id="ResultMap2" class="Sector" >

    <result column="SCT_ID" property="id" jdbcType="LONG" />

    <result column="LVL" property="level" jdbcType="LONG" />

    <result column="SCT_NBR" property="sectorNumber" jdbcType="VARCHAR" />

    <result column="SCT_NAM" property="name" jdbcType="VARCHAR" />

    <result column="SCT_PID" property="parentId" jdbcType="LONG" />

    <result column="SCT_DESC" property="desc" jdbcType="VARCHAR" />

    <result column="CRT_TIME" property="createTime" jdbcType="TIMESTAMP" />

    <result column="CRT_BY" property="createBy" jdbcType="VARCHAR" />

    <result column="LST_UPD_TIME" property="lastUpdateTime" jdbcType="TIMESTAMP" />

    <result column="LST_UPD_BY" property="lastUpdateBy" jdbcType="VARCHAR" /> 

    <result column="VRSN_ID" property="versionId" jdbcType="LONG" />

    <result column="SCT_CLR" property="sectorColor" jdbcType="VARCHAR" />

    <result column="MAP_LAYER_ID" property="mapLeayerId" jdbcType="INTEGER"/>

    <result column="HAS_SUB_MULT_LAYER" property="hasSubLAYER" jdbcType="VARCHAR"/>

  </resultMap>

  

  <insert id="insertSector" parameterClass="Sector" >

    INSERT INTO SECTOR (SCT_NBR, SCT_NAM, SCT_PID, SCT_DESC,CRT_BY,LST_UPD_BY,TOP_PID,LVL,MAP_LAYER_ID,HAS_SUB_MULT_LAYER)

    VALUES (#sectorNumber:VARCHAR#, #name:VARCHAR#, #parentId:LONG#, #desc:VARCHAR#,  

     #createBy:VARCHAR#, #lastUpdateBy:VARCHAR#,#topPid:LONG#,#grade:INTEGER#,#mapLeayerId:INTEGER#,#hasSubLAYER:VARCHAR#)  

  </insert>  

  

  <update id="updateSectorById" parameterClass="Sector" >

UPDATE SECTOR

       SET 

          SCT_NBR = #sectorNumber:VARCHAR#,

          SCT_NAM = #name:VARCHAR#,

          SCT_PID = #parentId:LONG#,

          SCT_DESC = #desc:VARCHAR#,

          MAP_LAYER_ID = #mapLeayerId:INTEGER#,

          HAS_SUB_MULT_LAYER = #hasSubLAYER:VARCHAR#,

          LVL = #grade:INTEGER#,

          LST_UPD_BY = #lastUpdateBy:VARCHAR# 

     WHERE SCT_ID = #id:LONG#

  </update>

 

  <update id="batchUpdateLocation" parameterClass="Sector" >

UPDATE SECTOR

      <dynamic prepend="set"> 

       <isNotNull property="location" prepend=",">

           LOCATION = #location:VARCHAR#

       </isNotNull>

       <isNotNull property="sectorColor" prepend=",">

           SCT_CLR = #sectorColor:VARCHAR#

       </isNotNull>       

       <isNotNull property="lastUpdateBy" prepend=",">

         LST_UPD_BY = #lastUpdateBy:VARCHAR#

       </isNotNull>

       </dynamic>

     WHERE SCT_ID = #id:LONG# 

  </update>

    

  <delete id="deleteById" parameterClass="Long" >

    delete from SECTOR where SCT_ID = #value#

  </delete>

 

   <select id="selectByName" resultMap="ResultMap" parameterClass="String">

     select SCT_ID,SCT_NBR,SCT_NAM,SCT_PID,SCT_DESC,LOCATION,SCT_CLR,TOP_PID,LVL,CRT_TIME,CRT_BY,LST_UPD_TIME,LST_UPD_BY,VRSN_ID,MAP_LAYER_ID,HAS_SUB_MULT_LAYER

      from SECTOR

     where SCT_NAM LIKE '%' + #value# + '%'

  </select>

  

  <select id="selectBySectorPara" resultMap="ResultMap" parameterClass="SearchSectorDto">

    select SCT_ID,SCT_NBR,SCT_NAM,SCT_PID,SCT_DESC,LOCATION,SCT_CLR,TOP_PID,LVL,CRT_TIME,CRT_BY,LST_UPD_TIME,LST_UPD_BY,VRSN_ID,MAP_LAYER_ID,HAS_SUB_MULT_LAYER

      from SECTOR

     WHERE 1 = 1

<isNotNull property="name" prepend="AND"> 

SCT_NAM LIKE '%' +#name:VARCHAR# + '%' 

</isNotNull>

<isNotNull property="parentId" prepend="AND"> 

   SCT_PID = #parentId:LONG#

</isNotNull>

<isNotNull property="sectorNumber" prepend="AND"> 

SCT_NBR = #sectorNumber:VARCHAR#

</isNotNull>

<isNotNull property="createTimeStart" prepend="AND"> 

   CRT_TIME >= #createTimeStart:TIMESTAMP#

</isNotNull>

<isNotNull property="createTimeEnd" prepend="AND"> 

   CRT_TIME &lt;=#createTimeEnd:TIMESTAMP#

</isNotNull>

<isNotNull property="location" prepend="AND"> 

   LOCATION IS NOT NULL 

</isNotNull>

  </select>

</sqlMap>

 

dao层写法参见http://pinsir.iteye.com/blog/586184