Dynamic SQL--官方文档

https://ibatis.apache.org/docs/dotnet/datamapper/ch03s09.html

A very common problem with working directly with ADO is dynamic SQL. It is normally very difficult to work with SQL statements that change not only the values of parameters, but which parameters and columns are included at all. The typical solution is usually a mess of conditional if-else statements and horrid string concatenations. The desired result is often a query by example, where a query can be built to find objects that are similar to the example object. The iBATIS DataMapper API provides a relatively elegant solution that can be applied to any mapped statement element. Here is a simple example:

<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>

In the above example, there are two possible statements that could be created depending on the state of the Id property of the parameter object. If the Id parameter is greater than 0, then the statement will be created as follows:

select * from ACCOUNT where ACC_ID = ?

Or if the Id parameter is 0 or less, the statement will look as follows.

select * from ACCOUNT

The immediate usefulness of this might not become apparent until a more complex situation is encountered. For example, the following is a somewhat more complex example.

<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>

Depending on the situation, there could be as many as 16 different SQL queries generated from the above dynamic statement. To code the if-else structures and string concatenations could get quite messy and require hundreds of lines of code.

Using dynamic statements is as simple as inserting some conditional tags around the dynamic parts of your SQL. For example:

<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>

In the above statement, the <dynamic> element demarcates a section of the SQL that is dynamic. The dynamic element is optional and provides a way to manage a prepend in cases where the prepend ("WHERE") should not be included unless the contained conditions append to the statement. The statement section can contain any number of conditional elements (see below) that will determine whether the contained SQL code will be included in the statement. All of the conditional elements work based on the state of the parameter object passed into the query. Both the dynamic element and the conditional elements have a "prepend" attribute. The prepend attribute is a part of the code that is free to be overridden by the a parent element‘s prepend if necessary. In the above example the "where" prepend will override the first true conditional prepend. This is necessary to ensure that the SQL statement is built properly. For example, in the case of the first true condition, there is no need for the AND, and in fact it would break the statement. The following sections describe the various kinds of elements, including Binary Conditionals, Unary Conditionals, and Iterate.

<isEqual prepend="AND" 
             property="status" 
             compareValue="Y">
MARRIED = ‘TRUE‘
</isEqual>               

<isNotEqual prepend="AND" 
             property="status" 
             compareValue="N">
MARRIED = ‘FALSE‘
</isNotEqual>   

<isGreaterThan prepend="AND" 
             property="age" 
             compareValue="18">
ADOLESCENT = ‘FALSE‘
</isGreaterThan>   

<isGreaterEqual prepend="AND" 
             property="shoeSize" 
             compareValue="12">
BIGFOOT = ‘TRUE‘
</isGreaterEqual>

<isLessEqual prepend="AND" 
             property="age" 
             compareValue="18">
ADOLESCENT = ‘TRUE‘
</isLessEqual>

<isPropertyAvailable property="id" >
  ACCOUNT_ID=#id# 
</isPropertyAvailable>

<isNotPropertyAvailable property="age" >
  STATUS=‘New‘ 
</isNotEmpty>

<isNull prepend="AND" property="order.id" >
  ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) 
</isNotEmpty>

<isNotNull prepend="AND" property="order.id" >
  ORDER.ORDER_ID = #order.id#
</isNotEmpty>

<isEmpty property="firstName" >
  LIMIT 0, 20
</isNotEmpty>

<isNotEmpty prepend="AND" property="firstName" >
  FIRST_NAME LIKE ‘%$FirstName$%‘
</isNotEmpty>

<isParameterPresent prepend="AND">
  EMPLOYEE_TYPE = #empType#
</isParameterPresent>

<isNotParameterPresent prepend="AND">
  EMPLOYEE_TYPE = ‘DEFAULT‘
</isNotParameterPresent>

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

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

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

  SELECT * FROM PRODUCT WHERE PRD_DESCRIPTION LIKE ‘%dog%‘