3.9 Dynamic SQL

A simple dynamic select sttatement, with two possible outcomes 一个简单的动态查询语句，有两种可能的结果

<select id="dynamicGetAccountList" cacheModel="account-cache" parameterClass="Account" resultMap="account-result" >
  select * from ACCOUNT
    <isGreaterThan prepend="and" property="Id" compareValue="0">
       where ACC_ID = #Id#
    </isGreaterThan>
  order by ACC_LAST_NAME
</select>

A complex dynamic select statement, with 16 possible outcomes 一个复杂的动态查询语句

<select id="dynamicGetAccountList" parameterClass="Account" resultMap="account-result" >
  select * from ACCOUNT
    <dynamic prepend="WHERE">
      <isNotNull prepend="AND" property="FirstName">
        ( ACC_FIRST_NAME = #FirstName#
        <isNotNull prepend="OR" property="LastName">
          ACC_LAST_NAME = #LastName#
        </isNotNull>
        )
      </isNotNull>
      <isNotNull prepend="AND" property="EmailAddress">
        ACC_EMAIL like #EmailAddress#
      </isNotNull>
      <isGreaterThan prepend="AND" property="Id" compareValue="0">
        ACC_ID = #Id#
      </isGreaterThan>
    </dynamic>
  order by ACC_LAST_NAME
</select>

Creating a dynamic statement with conditional tags

<statement id="someName" parameterClass="Account" resultMap="account-result" >
  select * from ACCOUNT
  <dynamic prepend="where">
    <isGreaterThan prepend="and" property="id" compareValue="0">
      ACC_ID = #id#
    </isGreaterThan>
    <isNotNull prepend="and" property="lastName">
      ACC_LAST_NAME = #lastName#
    </isNotNull>
  </dynamic>
order by ACC_LAST_NAME
</statement>

 

dynamic element  和conditional elements 都有prepend属性，有需要的话 parent element会覆盖 child element的 prepend属性

3.9.1. Binary Conditional Elements  二元条件判断

3.9.1.1. Binary Conditional Attributes:

• • prepend
  • property
  • compareProperty / compareValue

      elements：

• • <isEqual>
  • <isNotEqual>
  • <isGreaterThan>
  • <isGreaterEqual>
  • <isLessEqual>

3.9.2. Unary Conditional Elements 一元条件判断

3.9.2.1. Unary Conditional Attributes

• • prepend
  • property

      elements：

• • <isPropertyAvailable>
  • <isNotPropertyAvailable>
  • <isNull>
  • <isNotNull>
  • <isEmpty>  Checks to see if the value of a Collection, String property is null or empty ("" or size() < 1)
  • <isNotEmpty>  Checks to see if the value of a Collection, String property is not null and not empty ("" or size() < 1). 

3.9.3. Parameter Present Elements

3.9.3.1. Parameter Present Attributes

• • prepend 

      elements：

• • <isParameterPresent>  Checks to see if the parameter object is present (not null). 
  • <isNotParameterPresent>  Checks to see if the parameter object is not present (null)

3.9.4. Iterate Element

3.9.4.1. Iterate Attributes

• • prepend 
  • property  a property of type IList that is to be iterated over
  • open
  • close
  • conjunction  the string to be applied in between each iteration, useful for AND and OR (optional)

      elements：

• • <iterate> 

iterate用法：注意 '[]' ，用来防止解析器将属性解析为简单的字符串

<iterate prepend="AND" property="UserNameList"
  open="(" close=")" conjunction="OR">
  username=#UserNameList[]#
</iterate>

3.9.5. Simple Dynamic SQL Elements

<statement id="getProduct" resultMap="get-product-result">
  select * from PRODUCT order by $preferredOrder$
</statement>

A dynamic element that changes the comparison operator

<statement id="getProduct" resultMap="get-product-result">
  SELECT * FROM PRODUCT
  <dynamic prepend="WHERE">
    <isNotEmpty property="Description">
       PRD_DESCRIPTION $operator$ #Description#
    </isNotEmpty>
  </dynamic>
</statement>

$operator$可以是 ‘<’ 、 '>'、  '='、 'LIKE' 等