一个标准化的ibatis的CRUD sql语句

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Calib0">
 
 <insert id="calib0.insert" parameterClass="com.tpaic.ruleengine.domain.Calib0">
     <![CDATA[
  INSERT INTO T_CALIB1
    ( CREATED_BY,
      CREATED_DATE,
      CALIB_ID,
         DEPARTMENT_CODE,
            CHANNEL_SOURCE_CODE,
            BUSINESS_TYPE,
            APPLY_TYPE
   )
  VALUES
    (#createdBy:VARCHAR#,
     sysdate,
     #calibId:VARCHAR#,
     #departmentCode:VARCHAR#,
     #channelSourceCode:VARCHAR#,
     #businessType:VARCHAR#,
     #applyType:VARCHAR#
    )
     ]]>
 </insert>
 

 <select id="calib0.search" parameterClass="com.tpaic.ruleengine.domain.Calib0"
  resultClass="com.tpaic.ruleengine.domain.Calib0">
  select
          c.CREATED_DATE createdDate,
          c.CREATED_BY createdBy,
           c.CALIB_ID calibId,
            c.DEPARTMENT_CODE departmentCode,
            (select t.department_chinese_name from t_department_define t where t.internal_department_code = c.department_code) departmentName,
            c.CHANNEL_SOURCE_CODE channelSourceCode,
            c.BUSINESS_TYPE businessType,
            c.APPLY_TYPE applyType,
            (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R1') as forbiddenRuleId,
            (select b1.rule_name from t_decision_rule b1 where b1.rule_id in (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R1')) as forbiddenRuleName,
            (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R2') as mustRuleId,
            (select b1.rule_name from t_decision_rule b1 where b1.rule_id in (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R2')) as mustRuleName,
            (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R3') as limitRuleId,
            (select b1.rule_name from t_decision_rule b1 where b1.rule_id in (select a1.rule_id from t_calib_rule a1 where a1.calib_id=c.calib_id and a1.business_type='R3')) as limitRuleName
      from T_CALIB1 c
      where 1 = 1
  <isNotEmpty prepend="and" property="departmentCode">
   <isNotEmpty prepend="" property="subOrganFlag">
    c.DEPARTMENT_CODE in
     (select tdd01.internal_department_code from t_department_define tdd01
           connect by prior tdd01.internal_department_code = tdd01.upper_department_code
           start with tdd01.internal_department_code=#departmentCode#) 
   </isNotEmpty>
   <isEmpty prepend="" property="subOrganFlag">
    c.DEPARTMENT_CODE = #departmentCode#
   </isEmpty>
  </isNotEmpty>
  <isNotEmpty prepend="and" property="channelSourceCode">
   c.CHANNEL_SOURCE_CODE = #channelSourceCode#
  </isNotEmpty>
  <isNotEmpty prepend="and" property="businessType">
   c.BUSINESS_TYPE = #businessType#
  </isNotEmpty>
  <isNotEmpty prepend="and" property="applyType">
   c.APPLY_TYPE = #applyType#
  </isNotEmpty>
  <isNotEmpty prepend="AND" property="userDept">
           c.DEPARTMENT_CODE IN
              (select tdd01.internal_department_code from t_department_define tdd01
                  connect by prior tdd01.internal_department_code = tdd01.upper_department_code
                  start with tdd01.internal_department_code=#userDept#)
       </isNotEmpty>
 </select>
 
 <select id="calib0.searchById" parameterClass="java.lang.String"
  resultClass="com.tpaic.ruleengine.domain.Calib0">
  select
   c.CALIB_ID calibId,
         c.DEPARTMENT_CODE departmentCode,
          (select t.department_chinese_name from t_department_define t where t.internal_department_code = c.department_code) departmentName,
            c.CHANNEL_SOURCE_CODE channelSourceCode,
            c.BUSINESS_TYPE businessType,
            c.APPLY_TYPE applyType
  from T_CALIB1 c where c.CALIB_ID = #calibId#
 </select> 
 
 <delete id="calib0.delete" parameterClass="com.tpaic.ruleengine.domain.Calib0">
   <![CDATA[
         DELETE FROM T_CALIB1
           WHERE CALIB_ID = #calibId#
       ]]>
 </delete>

<update id="vehicleBlackList.delete" parameterClass="com.tpaic.ruleengine.domain.VehicleBlackList">
  UPDATE T_VEHICLE_BLACK_LIST
        SET DELETED_FLAG = '1',
           DELETED_DATE = sysdate,
           DELETED_BY =#deletedBy#
           WHERE ID = #id#
 </update>
 
 <update id="vehicleBlackList.update" parameterClass="com.tpaic.ruleengine.domain.VehicleBlackList">
  UPDATE T_VEHICLE_BLACK_LIST set
   updated_date   =sysdate,
   updated_by     =#updatedBy#,
   ENGINE_NO     =#engineNo#,
      VEHICLE_FRAME_NO =#vehicleFrameNo#,
      SORT_DISPLAY   =#sortDisplay#,
      CLAIM_TIMES   =#claimTimes#,
      CLAIM_RATIO   =#claimRatio#,
      REMARK        =#remark#
   WHERE    ID   = #id#
 </update>
</sqlMap>