• <if></if>
• <where></where> (along with trim and set)
• <choose></choose> (with when and otherwise)
• <foreach></foreach>

Important Note: When working with XML mappers, special characters such as greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=) should be replaced with their corresponding XML entities. This prevents parsing errors since XML files already contain numerous angle brackets.

Original Symbol	<	<=	>	>=	&	'	"
XML Entity	&lt;	&lt;=	&gt;	&gt;=	&amp;	&apos;	&quot;

Conditional Statements with <if>

The <if> tag is commonly used to construct conditional WHERE clauses. When multiple conditions need to be combined, developers can use multiple <if> tags. A traditional approach involves adding "WHERE 1=1" to ensure the query remains valid even when all conditions evaluate to false. Each subsequent condition can then be prefixed with "AND".

<!-- Dynamic SQL with if tags -->
<select id="findUsersByConditions" resultType="User">
    SELECT id, username, email, age
    FROM users
    WHERE 1=1
    <if test="username != null and username != ''">
        AND username LIKE '%' #{username} '%'
    </if>
    <if test="age != null and age > 0">
        AND age > #{age}
    </if>
</select>

When both conditions are provided, the resulting SQL will be: SELECT id, username, email, age FROM users WHERE 1=1 AND username LIKE '%' ? '%' AND age > ?

With only the username condition: SELECT id, username, email, age FROM users WHERE 1=1 AND username LIKE '%' ? '%'

With no conditions: SELECT id, username, email, age FROM users WHERE 1=1

While this approach works, the "WHERE 1=1" clause can negatively impact query performance on large datasets.

Advanced Conditional Tags: <where>, <trim>, and <set>

The <where> tag provides a more elegant solution by automatically adding the WHERE keyword only when atleast one condition evaluates to true. It also handles the removal of leading AND or OR keywords from the first condition that evaluates to true.

<select id="findUsersByConditionsOptimized" resultType="User">
    SELECT id, username, email, age
    FROM users
    <where>
        <if test="username != null and username != ''">
            AND username LIKE '%' #{username} '%'
        </if>
        <if test="age != null and age > 0">
            AND age > #{age}
        </if>
    </where>
</select>

For more complex scenarios, the <trim> tag offers greater flexibility with its attributes:

• prefix: Adds a prefix before the content, if applicable
• suffix: Adds a suffix after the content, if applicable
• prefixOverrides: Removes specified content from the beginning of the trimmed content
• suffixOverrides: Removes specified content from the end of the trimmed content

Here's an example of using <trim> to replace the <where> functionality:

<select id="findUsersWithTrim" resultType="User">
    SELECT id, username, email, age
    FROM users
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="username != null and username != ''">
            AND username LIKE '%' #{username} '%'
        </if>
        <if test="age != null and age > 0">
            AND age > #{age}
        </if>
    </trim>
</select>

The <set> tag is specifically designed for UPDATE statements and handles the removal of trailing commas:

<update id="updateUser">
    UPDATE users
    <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>

Conditional Logic with <choose>, <when>, and <otherwise>

When you need to implement mutually exclusive conditions (similar to a switch-case statement), the <choose> tag comes in handy. It evaluates each <when> condition in order and executes the first one that evaluates to true. If none of the <when> conditions are met, the <otherwise> block is executed.

<select id="findUsersWithPriority" resultType="User">
    SELECT id, username, email, age
    FROM users
    <where>
        <choose>
            <when test="username != null and username != ''">
                AND username LIKE '%' #{username} '%'
            </when>
            <when test="email != null and email != ''">
                AND email = #{email}
            </when>
            <otherwise>
                AND status = 'ACTIVE'
            </otherwise>
        </choose>
    </where>
</select>

Iterating with <foreach>

The <foreach> tag is essential for iterating over collections and constructing IN clauses or batch operations. Key attributes include:

• collection: The collection to iterate (array, list, map)
• item: The variable name for each element in the iteration
• index: The variable name for the index (for lists/arrays) or key (for maps)
• open: The string to prepend to the entire iteration result
• close: The string to append to the entire iteration result
• separator: The separator between each iteration

Example with an array of IDs:

<select id="findUsersByIdArray" resultType="User">
    SELECT id, username, email, age
    FROM users
    WHERE id IN
    <foreach collection="array" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
</select>

Example with a list of IDs:

<select id="findUsersByIdList" resultType="User">
    SELECT id, username, email, age
    FROM users
    WHERE id IN
    <foreach collection="list" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
</select>

Example with a list of objects:

<select id="findUsersByObjectList" resultType="User">
    SELECT id, username, email, age
    FROM users
    WHERE id IN
    <foreach collection="list" item="user" open="(" close=")" separator=",">
        #{user.id}
    </foreach>
</select>

SQL Fragments with <sql> and <include>

The <sql> tag allows you to define reusable SQL fragments that can be included in other statements using the <include> tag. This promotes code reuse and consistency across your mappings.

<sql id="userColumns">
    id, username, email, age
</sql>

<sql id="userTable">
    users
</sql>

<select id="findUsersWithFragments" resultType="User">
    SELECT
    <include refid="userColumns"/>
    FROM
    <include refid="userTable"/>
    <where>
        <if test="status != null">
            status = #{status}
        </if>
    </where>
</select>

MyBatis dynamic SQL tags provide a flexible and powerful way to construct complex queries while maintaining clean and readable code. By leveraging these tags, developers can create sophisticated data access logic without resorting to string concatenation or complex conditional checks in their application code.