Master MyBatis Dynamic SQL: If, Where, Set, Choose, Foreach & Bind Explained

Introduction

After reading this article you will learn:

MyBatis dynamic SQL statement collection

How to define variables in MyBatis

How to extract reusable SQL fragments

1. If Statement

Requirement: query blogs by author name and blog title. If the author name is null, query only by title; otherwise query by author.

<!-- requirement comment -->
<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog where
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

The generated SQL will omit the author condition when it is null. If title is null the resulting SQL would incorrectly start with and author=...; this is addressed in the next section.

2. Where Statement

By wrapping the conditional fragments inside a where element, MyBatis automatically inserts the WHERE keyword only when at least one child element produces content, and removes leading AND or OR.

<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

You can also achieve the same effect with a custom trim element:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
</trim>

2.1 Custom trim equivalent to where

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
</trim>

3. Set Statement

When performing an update, the set element works like where by inserting the SET keyword and removing trailing commas.

<!-- note: commas separate items -->
<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id};
</update>

A custom trim can also emulate set:

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

3.1 Custom trim equivalent to set

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

4. Choose Statement

The choose element works like a Java switch, selecting the first matching when clause or falling back to otherwise.

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

5. Foreach Statement

Example: query blogs whose id is in a list (e.g., 1,2,3).

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

6. SQL Fragment

Frequently used SQL snippets can be defined with sql and included elsewhere with include for better reuse.

<!-- definition -->
<sql id="if-title-author">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<!-- usage -->
<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <include refid="if-title-author"/>
    </where>
</select>

It is recommended to base fragments on a single table to maximize reusability.

7. Bind Element

The bind element creates a new variable in the OGNL context, useful for complex expressions such as building a LIKE pattern.

<select id="selectBlogsLike" resultType="Blog">
    <bind name="pattern" value="'%' + _parameter.getTitle() + '%'"/>
    SELECT * FROM BLOG
    WHERE title LIKE #{pattern}
</select>