Dynamic SQL in iBATIS

Building SQL Conditionally

iBATIS gave developers XML tags to compose SQL at runtime. Nearly every modern feature of MyBatis dynamic SQL began here.

<dynamic> With <isNotEmpty>

<select id="searchCustomers" parameterClass="map" resultMap="customerResult">
  SELECT id, name, email, city FROM customers
  <dynamic prepend="WHERE">
    <isNotEmpty prepend="AND" property="city">
      city = #city#
    </isNotEmpty>
    <isNotEmpty prepend="AND" property="name">
      name LIKE '%#name#%'
    </isNotEmpty>
  </dynamic>
</select>

prepend inserts the keyword (WHERE, AND) only when a branch is included.

Iterating Over Lists

<select id="findByIds" parameterClass="map" resultMap="customerResult">
  SELECT * FROM customers WHERE id IN
  <iterate property="ids" open="(" close=")" conjunction=",">
    #ids[]#
  </iterate>
</select>

Compare and Choice Logic

<isEqual property="status" compareValue="VIP">...</isEqual>
<isNull property="city">...</isNull>
<isNotEmpty property="name">...</isNotEmpty>

Key Points

• <dynamic> enables branching and iteration inside SQL.
• <isNotEmpty>, <isEqual>, <isNull> test conditions.
• prepend adds WHERE/AND/OR only when a branch fires.
• <iterate> expands collections into IN lists.