Conditional Logic with <if>, <where>, and <set>

Static SQL strings can be limiting when application requirements change. MyBatis provides the <if> tag to introduce conditional logic directly into your mapping files.

Consider a scenario where you want to query a database table using optional parameters. Without dynamic SQL, you might write a rigid statement:

<select id="findUser" resultType="User">
    SELECT * FROM users WHERE user_id = #{id} AND status = #{status};
</select>

To make this flexible, allowing queries by ID only, status only, or both, you can use <if> tags:

<select id="findUser" resultType="User">
    SELECT * FROM users WHERE
    <if test="id != null">
        user_id = #{id}
    </if>
    <if test="status != null">
        AND status = #{status}
    </if>
</select>

However, this approach creates syntax errors if the first condition is false (leaving a trailing "WHERE") or if both are true but the logic isn't handled perfectly. The <where> tag solves this by automatically inserting the "WHERE" clause only if there is content inside it, and it intelligently strips leading "AND" or "OR" keywords.

<select id="findUser" resultType="User">
    SELECT * FROM users
    <where>
        <if test="id != null">
            user_id = #{id}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

Siimlarly, the <set> tag is used in update statements to dynamically include columns. It ensures that commas are handled correctly, removing the trailing comma that might occur if the last condition is false.

Switch-Like Logic with <choose>, <when>, and <otherwise>

When you need to pick one option among many (like a Java switch statement), use the <choose> structure. It evalautes conditions top-to-bottom and executes the first one that matches, ignoring the rest.

<select id="findUser" resultType="User">
    SELECT * FROM users
    <where>
        <choose>
            <when test="id != null">
                user_id = #{id}
            </when>
            <when test="email != null">
                email_address = #{email}
            </when>
            <otherwise>
                active = 1
            </otherwise>
        </choose>
    </where>
</select>

Iterating Collections with <foreach>

The <foreach> tag is essential for handling lists or arrays, commonly used for "IN" queries or batch operations.

1. Batch Selection and Deletion

When passing a list of IDs to perform a bulk query, <foreach> wraps the list with parentheses and separates values with commas.

<select id="findUsersByIds" resultType="User">
    SELECT * FROM users
    WHERE user_id IN
    <foreach collection="idList" item="currentId" open="(" separator="," close=")">
        #{currentId}
    </foreach>
</select>

• collection: The name of the parameter list or array.
• item: The alias for each element during iteration.
• open/close: Strings to prefix and suffix the loop (e.g., parentheses).
• separator: The delimiter between elements (e.g., a comma).

2. Batch Insertion

You can generate a multi-value insert statement dynamically. Assuming you pass a list of User objects:

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

3. Batch Updates

To execute multiple update statements in one go, iterate over the list. Note that standard JDBC connections usually forbid multiple statements in one query for security reasons. To enable this, you must append allowMultiQueries=true to your JDBC URL in the configuration file (e.g., jdbc:mysql://localhost:3306/db?allowMultiQueries=true).

<update id="batchUpdate">
    <foreach collection="userList" item="user" separator=";">
        UPDATE users SET role = #{user.role} WHERE username = #{user.username}
    </foreach>
</update>

Reusable SQL Fragments with <sql> and <include>

To avoid repetition, MyBatis allows you to define a block of SQL using the <sql> tag and reference it later with <include>.

<sql id="baseColumns"> id, username, email, role </sql>

<select id="findAll" resultType="User">
    SELECT
    <include refid="baseColumns" />
    FROM users
</select>