iBatis中的动态查询

下面给出几个例子和dtd定义：

<select id="selectDispatchedKey" parameterClass="KeyAndKeyFlowInfo" resultMap="KeyAndKeyFlowResult">
   select distinct
    KEY_ID,
    USER_ID,
    INITIATOR,
    INIT_DATE,
    INITIATOR_EMAIL,
    SGS_KEY.BRANCH_NAME,
    APPROVER,
    APPROVER_EMAIL,
    APPROVE_DATE
   from
    SGS_KEY,
    SGS_KEY_FLOW
   where SGS_KEY.APPLY_FLOW_ID=SGS_KEY_FLOW.KEY_FLOW_ID
   <dynamicprepend="and" open="(" close=")">   
    <isNotNull property="ki.userId" prepend="and" removeFirstPrepend="false">
     USER_ID = #ki.userId#
    </isNotNull>
    <isNotNullproperty="kfi.initiator" prepend="and">
     INITIATOR = #kfi.initiator#
    </isNotNull>
    <isNotNullproperty="kfi.initDate" prepend="and">
     INIT_DATE = #kfi.initDate#
    </isNotNull>
   </dynamic>
</select>

<update id="updateKeyFlow" parameterClass="KeyFlowInfo">
   update SGS_KEY_FLOW set
   <dynamic>
     KEY_FLOW_ID =#keyFlowId#
    <isNotNull property="branchName" prepend=",">
     BRANCH_NAME = #branchName#
    </isNotNull>
    <isNotEqual property="operation" prepend="," compareProperty="operation" compareValue="0">
     OPERATION = #operation#
    </isNotEqual>
    <isNotNull property="initiator" prepend=",">
     INITIATOR = #initiator#
    </isNotNull>
    <isNotNull property="initiatorEmail" prepend=",">
     INITIATOR_EMAIL = #initiatorEmail#
    </isNotNull>
    <isNotNull property="initDate" prepend=",">
     INIT_DATE = #initDate#
    </isNotNull>
    <isNotNull property="approver" prepend=",">
     APPROVER = #approver#
    </isNotNull>
    <isNotNull property="approverEmail" prepend=",">
     APPROVER_EMAIL = #approverEmail#
    </isNotNull>
    <isNotNull property="approveDate" prepend=",">
     APPROVE_DATE = #approveDate#
    </isNotNull>
    <isNotEqual property="keyNum" prepend="," compareProperty="keyNum" compareValue="0">
     KEY_NUM = #keyNum#
    </isNotEqual>
    <isNotNull property="applyReason" prepend=",">
     APPLY_REASON = #applyReason#
    </isNotNull>
    <isNotNull property="rejectReason" prepend=",">
     REJECT_REASON = #rejectReason#
    </isNotNull>                        
   </dynamic>
   where
    KEY_FLOW_ID =#keyFlowId#
</update>

属性关键字	含义
<isEqual>	如果参数相等于值 则查询条件有效。
<isNotEqual>	如果参数不等于值则查询条件有效。
<isGreaterThan>	如果参数大于值则查询条件有效。
<isGreaterEqual>	如果参数等于值则查询条件有效。
<isLessEqual>	如果参数小于值则查询条件有效。如下所示： <isLessEqual prepend = ”AND” property = ”age” compareValue = ”18” > ADOLESCENT = ‘TRUE’ </isLessEqual>
<isPropertyAvailable>	如果参数有使用则查询条件有效。
<isNotPropertyAvailable>	如果参数没有使用则查询条件有效。
<isNull>	如果参数为NULL则查询条件有效。
<isNotNull>	如果参数不为NULL则查询条件有效。
<isEmpty>	如果参数为空则查询条件有效。
<isNotEmpty>	如果参数不为空则查询条件有效 。参数的数据类型为 Collection 、 String 时参数不为 NULL 或“”。如下所示： <isNotEmpty prepend=”AND” property=”firstName” > FIRST_NAME=#firstName# </isNotEmpty>
<isParameterPresent>	如果参数类不为NULL 则查询条件有效。
<isNotParameterPresent>	Checks to see if the parameter object is not present (null). Example Usage: <isNotParameterPresent prepend=”AND”> EMPLOYEE_TYPE = ‘DEFAULT’ </isNotParameterPresent>

http://203.208.39.132/search?q=cache:c0ebNIxuwRsJ:blog.csdn.net/yangguoqi/archive/2009/04/25/4122824.aspx+isNotNull+removeFirstPrepend&cd=4&hl=zh-CN&ct=clnk&gl=cn&st_usg=ALhdy292qjrM9JyeUS1SW1kaFKLSpEs4-w
项目的例子：
  <statement id="[customerinfo-lookup]" parameterClass="java.util.Map" resultClass="java.util.HashMap">
    <![CDATA[
   SELECT A.ROW_ID s_CUSTOMER_ID,A.PERMIT_NO s_PERMIT_NO,A.CUSTOMER_DESC s_CUSTOMER_DESC,A.ADDR s_ADDR,
     A.CUSTMGR_ID s_CUSTMGR_ID,B.EMPLOYEE_NAME s_CUSTMGR_NAME,A.MARKET_ORG s_MARKET_ORG,C.DEPT_NAME s_DEPT_NAME
      FROM MD_CUSTOMER A LEFT JOIN MD_EMPLOYEE B ON A.CUSTMGR_ID=B.ROW_ID LEFT JOIN MD_DEPT C ON A.MARKET_ORG=C.ROW_ID
      WHERE 1=1 AND A.STATUS='1'
    ]]>
    <isNotEmpty prepend="and" property="custmgrID">
      <![CDATA[ A.CUSTMGR_ID=#custmgrID# ]]>
    </isNotEmpty>
    <isNotNull property="filter" removeFirstPrepend="true" prepend="AND" open="(" close=")">
   <isNotEmpty prepend="OR" property="filter.m_PERMIT_NO">
     <![CDATA[ A.PERMIT_NO LIKE '%$filter.m_PERMIT_NO$%' ]]>
   </isNotEmpty>
   <isNotEmpty prepend="OR" property="filter.m_CUSTOMER_DESC">
     <![CDATA[ A.CUSTOMER_DESC LIKE '%$filter.m_CUSTOMER_DESC$%' ]]>
   </isNotEmpty>
   <isNotEmpty prepend="OR" property="filter.s_PLACE_NAME">
     <![CDATA[ A.ADDR LIKE '%$filter.m_ADDR$%' ]]>
   </isNotEmpty>
    </isNotNull>
    <isNotNull prepend="ORDER BY" property="sort"> <!--排序-->
      $sort$
    </isNotNull>
  </statement>

posted on 2010-01-22 15:30 飞熊 阅读(1606) 评论(0)  编辑  收藏 所属分类: Ibatis