Comprehensive Guide to MyBatis Dynamic SQL Tags: if, where, set, choose, foreach, sql, and bind

Welcome from the author, a Java architect who writes code and poetry, introducing a series of MyBatis dynamic SQL techniques.

Readers will learn a complete set of MyBatis dynamic SQL statements, variable definitions, and how to extract reusable SQL fragments.

1. if Statement

Requirement: query blogs by author and title, using only the non‑null condition.

<!--需求1：<br/>根据作者名字和博客名字来查询博客！<br/>如果作者名字为空，那么只根据博客名字查询，反之，则根据作者名来查询<br/>select * from blog where title = #{title} and author = #{author}-->
<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>

If author is null, the generated SQL becomes select * from user where title=#{title}; if title is null, the generated SQL would incorrectly start with and, which is fixed by using a where element.

2. where Statement

By wrapping the conditional fragments in a where element, MyBatis automatically inserts the WHERE keyword only when needed and removes leading AND / 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>

If the default behavior of where does not meet your needs, you can use a custom trim element.

2.1 Custom trim equivalent to where

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

3. set Statement

When updating records, the set element works like where by inserting the SET keyword and removing trailing commas.

<!--注意set是用的逗号隔开-->
<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>

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

Used to generate a list of conditions, e.g., querying blogs with ids 1, 2, 3.

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

6. SQL Fragment

Reusable SQL snippets can be defined with sql and included via include.

<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>

Best practice: define fragments on a single table and avoid including where inside the fragment.

7. bind Element

The bind element creates a new variable for use in OGNL expressions.

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

The article concludes with a call to share and join the community.