Dynamic SQL Best Practices

MyBatis dynamic SQL uses <if>, <choose>, <where>, <set>, <foreach>, and <trim> to build SQL fragments based on parameters, avoiding duplicate SQL. This article covers common patterns, avoiding syntax errors, preventing injection, and maintainability with examples and a reference table.

Overview

• <where> — Removes a leading AND/OR so you avoid WHERE AND .... Use with <if> for optional conditions.
• <set> — For UPDATE; removes a trailing comma so only non-null fields are updated.
• <choose>/<when>/<otherwise> — Pick one branch (e.g. sort column, status). Clearer than nested <if>.
• <foreach> — For IN lists and batch INSERT. Always use #{}; never ${} for values.
• Security — Dynamic parts control only which fragments are included. All values use #{}. For dynamic table/column names, use a whitelist or application-level branching; never user input in ${}.

Example

Example 1: Optional conditions — <where> + <if>

XMLCopy
<select id="selectByCondition" resultType="Order">
  SELECT * FROM orders
  <where>
    <if test="userId != null">
      AND user_id = #{userId}
    </if>
    <if test="status != null and status != ''">
      AND status = #{status}
    </if>
    <if test="minAmount != null">
      AND amount &gt;= #{minAmount}
    </if>
  </where>
  ORDER BY id DESC
</select>

• <where> strips a leading AND, so you can put AND before each condition. All values use #{}, so it is safe.

Example 2: Selective UPDATE — <set> + <if>

XMLCopy
<update id="updateSelective">
  UPDATE orders
  <set>
    <if test="status != null">status = #{status},</if>
    <if test="amount != null">amount = #{amount},</if>
    <if test="updatedAt != null">updated_at = #{updatedAt},</if>
  </set>
  WHERE id = #{id}
</update>

• <set> removes the trailing comma. Only non-null fields are updated. Good for partial-update APIs.

Example 3: Sort — <choose> or whitelist

XMLCopy
<select id="selectList" resultType="Order">
  SELECT * FROM orders
  <where>...</where>
  <choose>
    <when test="orderBy == 'amount'">ORDER BY amount DESC</when>
    <when test="orderBy == 'createdAt'">ORDER BY created_at DESC</when>
    <otherwise>ORDER BY id DESC</otherwise>
  </choose>
</select>

• Sort column comes from a fixed set (e.g. amount, createdAt). Column names are fixed in XML; no ${}.

Example 4: IN list — <foreach> + #{}

XMLCopy
<select id="selectByIds" resultType="Order">
  SELECT * FROM orders WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
</select>

• Use #{} for each id. Chunk if the list is very long (see Batch Insert/Update).

Example 5: Reusable SQL with <sql> and <include>

XMLCopy
<sql id="Order_Columns">id, user_id, amount, status, created_at</sql>

<select id="selectById" resultType="Order">
  SELECT <include refid="Order_Columns"/> FROM orders WHERE id = #{id}
</select>
<select id="selectByUser" resultType="Order">
  SELECT <include refid="Order_Columns"/> FROM orders
  <where>
    <if test="userId != null">AND user_id = #{userId}</if>
  </where>
</select>

• Centralize column lists; change the table schema in one place.

Scenario	Recommended	Avoid
Optional WHERE	<where> + <if>	Hand-written WHERE 1=1 or missing AND
Selective UPDATE	<set> + <if>	Manual comma handling
Sort/type branches	<choose> or whitelist	User input in ${orderBy}
IN / batch values	<foreach> + #{}	${} for list
Optional fragments	<if test="...">	Complex logic in test (keep it simple)

Core Mechanism / Behavior

• <where> — If content is non-empty, prepends WHERE and strips the first AND/OR.
• <set> — Strips a trailing comma and prepends SET.
• <trim> — Custom prefix/suffix and overrides (e.g. prefix="WHERE" prefixOverrides="AND|OR") for more control. Dynamic SQL evaluates OGNL at runtime to decide which nodes to include.

Key Rules

• Use #{} for all values. Dynamic SQL only controls which fragments are included; never inject user input into SQL text.
• Use <where> for optional conditions and <set> for selective updates. Keep test expressions simple; put complex logic in Java and pass explicit parameters.
• Extract common fragments into <sql> and reference with <include> for easier maintenance.

What's Next

See Parameter Binding for safe value binding. See ResultMap vs ResultType for complex queries with dynamic SQL.