MyBatis Dynamic SQL

One of the standout features that makes MyBatis a premier persistence framework is its powerful and flexible Dynamic SQL mechanism. Instead of forcing developers to write repetitive JDBC boilerplate or construct complex strings manually in application code, MyBatis empowers you to generate SQL statements dynamically based on runtime parameters using OGNL expressions. This capability dramatically streamlines enterprise application development, particularly when dealing with optional search filters or partial record updates.
The <if> tag is the foundation of dynamic SQL construction. By evaluating the test attribute, it determines whether to append a specific SQL fragment to the statement.

<select id="findUsers" resultType="User">
    SELECT * FROM user
    WHERE 1 = 1
    <if test="username != null and username != ''">
        AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="age != null">
        AND age = #{age}
    </if>
</select>

When writing conditions, keep two best practices in mind:

• For strings, always check for both null and empty strings (!= '') to prevent unexpected matching behavior.
• For numeric fields, check strictly against null to avoid potential type-casting exceptions.

The <where> Tag: Intelligent Prefix Management

While appending WHERE 1 = 1 is a common workaround, it lacks elegance. The <where> tag solves this gracefully. It automatically injects the WHERE keyword only when at least one inner condition evaluates to true, and it cleverly strips away any redundant AND or OR prefixes left at the beginning of the clause.

<select id="findUsers" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

If none of the inner conditions are met, the <where> tag renders nothing, keeping the resulting SQL syntax completely valid.

<choose>, <when>, and <otherwise>: Multi-Branch Selection

When you need mutual exclusivity—similar to a Java switch statement—the <choose> family is the ideal choice. It evaluates multiple branches in order and executes the first one that matches. If none match, the <otherwise> block serves as the fallback.

<select id="findUsers" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="username != null">
                AND username = #{username}
            </when>
            <when test="email != null">
                AND email = #{email}
            </when>
            <otherwise>
                AND status = 1
            </otherwise>
        </choose>
    </where>
</select>

The <set> Tag: Dynamic Update Statements

Building update statements often involves modifying only a subset of columns. The <set> tag takes care of prepending the SET keyword and stripping any trailing commas that might break the SQL syntax.

<update id="updateUser">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="age != null">age = #{age},</if>
    </set>
    WHERE id = #{id}
</update>

Note: Ensure that at least one field is updated when calling this statement, as an empty set block will result in a syntax error.

The <foreach> Tag: Iterating Over Collections

The <foreach> tag is indispensable for processing collection parameters, frequently used in IN clauses and bulk inserts. Key attributes include:

• collection: The name of the parameter collection (defaults to list for Lists, array for arrays, or a custom name via @Param).
• item: The variable name representing the current element during iteration.
• index: The index or key of the current element.
• open / close: Enclosing characters for the generated block.
• separator: The delimiter between elements.

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

For batch insertion, <foreach> constructs high-performance multi-row insert statements effortlessly:

<insert id="batchInsert">
    INSERT INTO user (username, email) VALUES
    <foreach collection="users" item="u" separator=",">
        (#{u.username}, #{u.email})
    </foreach>
</insert>

The <trim> Tag: Precision Custom Formatting

Both <where> and <set> are specialized wrappers around the versatile <trim> tag. By configuring prefix, suffix, prefixOverrides, and suffixOverrides, developers can tailor custom prefix and suffix rules with absolute precision.

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="username != null">AND username = #{username}</if>
    <if test="age != null">AND age = #{age}</if>
</trim>

Best Practices

To maintain clean and maintainable persistence layers, keep these guidelines in mind:

1. Prefer semantic tags like <where> and <set> over manual prefix/suffix handling.
2. Always validate collections before passing them to <foreach> to avoid generating invalid syntax like IN ().
3. Extract repetitive SQL fragments using the <sql> tag and include them via <include>.
4. Be cautious with XML special characters inside test attributes; use XML entities or keywords like gt and lt when necessary.

Conclusion

MyBatis Dynamic SQL bridges the gap between static query definitions and the flexible requirements of modern applications. By leveraging tags such as if, where, choose, set, foreach, and trim, developers can maintain readable, robust, and highly adaptable data access logic without sacrificing performance or control.