Dynamic SQL

SQL That Adapts

Dynamic SQL builds different statements depending on the input. An optional filter is the perfect example - no more clicking through empty conditions.

The <where> Element

<select id="search" resultType="Customer">
  SELECT * FROM customers
  <where>
    <if test="name != null and name != ''">
      AND name LIKE #{name}
    </if>
    <if test="city != null and city != ''">
      AND city = #{city}
    </if>
  </where>
  ORDER BY id
</select>

<where> removes the leading AND/OR and the WHERE keyword automatically when the inner conditions produce a prefix that would not be valid alone.

The <foreach> Element

<select id="findByIds" resultType="Customer">
  SELECT * FROM customers
  WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

Set for Updates

<update id="updateSelective">
  UPDATE customers
  <set>
    <if test="name != null">name = #{name},</if>
    <if test="city != null">city = #{city},</if>
  </set>
  WHERE id = #{id}
</update>

<set> strips the trailing comma and adds SET only when needed.

Key Points

• <if test> includes SQL only when a condition holds (OGNL).
• <where> and <set> clean up prefixes and commas.
• <foreach> expands lists into IN clauses.
• Dynamic SQL keeps optional filters and batch inserts simple.