Understandign Dynamic SQL in MyBatis

When building real-world applications, SQL statements often need to adapt based on varying conditions. For instance, during user registration, some fields may be required while others are optional. This variability makes static SQL insufficient, which is where MyBatis dynamic SQL becomes invaluable.

This guide explores MyBatis XML-based dynamic SQL implementation, covering essential tags and practical usage patterns.

The <if> Tag

The <if> tag enables conditional SQL fragment inclusion based on test expressions. This proves useful when certain parameters may be null or have specific values.

<insert id="insertCustomer">
    INTO customers (
        <if test="name != null">
            customer_name
        </if>
        <if test="email != null">
            ,email
        </if>
        <if test="phone != null">
            ,phone_number
        </if>
        <if test="status != null">
            ,account_status
        </if>
    ) VALUES (
        <if test="name != null">
            #{name}
        </if>
        <if test="email != null">
            ,#{email}
        </if>
        <if test="phone != null">
            ,#{phone}
        </if>
        <if test="status != null">
            ,#{status}
        </if>
    )
</insert>

However, a potential issue arises when conditional fields are missing. If the first parameter evaluates to false, the generated SQL contains a leading comma, resulting in invalid syntax. The <trim> tag addresses this problem.

The <trim> Tag

The <trim> tag provides fine-grained control over SQL fragment formatting through four key attributes:

• prefix: Prefixes the entire block with a specified value
• suffix: Appends a specified value to the entire block
• prefixOverrides: Removes the specified prefix from the block
• suffixOverrides: Removes the specified suffix from the block

<insert id="insertCustomer">
    INSERT INTO customers
    <trim prefix="(" suffix=")" prefixOverrides=",">
        <if test="name != null">
            customer_name
        </if>
        <if test="email != null">
            ,email
        </if>
        <if test="phone != null">
            ,phone_number
        </if>
        <if test="status != null">
            ,account_status
        </if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" prefixOverrides=",">
        <if test="name != null">
            #{name}
        </if>
        <if test="email != null">
            ,#{email}
        </if>
        <if test="phone != null">
            ,#{phone}
        </if>
        <if test="status != null">
            ,#{status}
        </if>
    </trim>
</insert>

The prefixOverrides="," attribute ensures that any leading comma gets automatically removed when the first conditional element is absent.

The <where> Tag

The <where> tag intelligently handles the WHERE clause in SELECT statements. It automatical generates the WHERE keyword and removes leading AND or OR operators from the generated SQL.

<select id="findByCriteria" resultType="com.example.app.model.Customer">
    SELECT * FROM customers
    <where>
        <if test="age != null">
            age = #{age}
        </if>
        <if test="status != null">
            AND account_status = #{status}
        </if>
        <if test="active">
            AND is_active = #{active}
        </if>
    </where>
</select>

The equivalent functionality can be achieved using <trim>:

<select id="findByCriteria" resultType="com.example.app.model.Customer">
    SELECT * FROM customers
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="age != null">
            age = #{age}
        </if>
        <if test="status != null">
            AND account_status = #{status}
        </if>
        <if test="active">
            AND is_active = #{active}
        </if>
    </trim>
</select>

A key distinction: the <where> tag omits the WHERE keyword entirely when no conditions evaluate to true. In contrast, the <trim> approach retains the WHERE keyword regardless of whether conditions exist.

The <set> Tag

The <set> tag simplifies UPDATE statements by generating the SET keyword and removing trailing commas automatically.

<update id="modifyCustomer">
    UPDATE customers
    <set>
        <if test="email != null">
            email = #{email},
        </if>
        <if test="phone != null">
            phone_number = #{phone},
        </if>
        <if test="status != null">
            account_status = #{status}
        </if>
    </set>
    WHERE customer_id = #{id}
</update>

This can be equivalently written using <trim>:

<trim prefix="SET" suffixOverrides=",">

The <foreach> Tag

The <foreach> tag iterates over collections, arrays, or maps to generate batch SQL statements. Key attributes include:

• collection: The collection to iterate (List, Set, Map, or array)
• item: The variable name for each iteration element
• open: String appended before the iteration block
• close: String appended after the iteration block
• separator: String inserted between each iteration

Common applications include batch deletion and batch insertion operations.

<delete id="removeBatch">
    DELETE FROM customers
    WHERE customer_id IN
    <foreach collection="idList" open="(" close=")" item="customerId" separator=",">
        #{customerId}
    </foreach>
</delete>

The <sql> and <include> Tags

When identical SQL fragments appear across multiple statements, refactoring becomes necessary to eliminate redundancy. The <sql> tag defines reusable SQL片段, while <include> references them.

<sql id="baseColumns">
    customer_id, customer_name, email, phone_number
</sql>

<select id="fetchAll" resultType="com.example.app.model.Customer">
    SELECT <include refid="baseColumns"/> FROM customers
</select>

<select id="fetchById" resultType="com.example.app.model.Customer">
    SELECT <include refid="baseColumns"/> FROM customers WHERE customer_id = #{id}
</select>

The refid attribute specifies which SQL fragment to include, enabling centralized maintenance of common SQL patterns.