Master MyBatis Dynamic SQL: Essential Tags and Real-World Examples

Introduction

The article continues a series on MyBatis by focusing on dynamic SQL, a powerful feature that lets developers construct SQL statements conditionally at runtime.

What is Dynamic SQL?

Dynamic SQL in MyBatis means building complete SQL strings based on input parameters, similar to using CASE WHEN in plain MySQL.

Common Tags

if

The if tag evaluates a test expression; when true, the enclosed SQL fragment is included. Example:

<select id="selectPats" resultType="com.xxx.domain.PatientInfo">
  select * from patient_info where status=1
  <if test="iptNum!=null">
    and ipt_num=#{iptNum}
  </if>
  <if test="bedNum!=null">
    and bed_num=#{bedNum}
  </if>
</select>

Multiple conditions can be combined with and inside a single if test.

choose, when, otherwise

The choose element works like a Java switch. Only the first when whose test evaluates to true is applied; if none match, otherwise runs.

<select id="selectPats" resultType="com.xxx.domain.PatientInfo">
  select * from patient_info where 1=1
  <choose>
    <when test="iptNum != null">
      AND ipt_num=#{iptNum}
    </when>
    <when test="bedNum != null">
      AND bed_num=#{bedNum}
    </when>
    <otherwise>
      AND status=1
    </otherwise>
  </choose>
</select>

where

The where tag automatically adds a WHERE clause only when its child elements produce content, and it removes leading AND / OR to avoid syntax errors.

<select id="selectPats" resultType="com.xxx.domain.PatientInfo">
  select * from patient_info
  <where>
    <choose>
      <when test="iptNum != null">AND ipt_num=#{iptNum}</when>
      <when test="bedNum != null">AND bed_num=#{bedNum}</when>
      <otherwise>AND status=1</otherwise>
    </choose>
  </where>
</select>

foreach

The foreach tag iterates over collections to build IN clauses or other repeated fragments. Important attributes include item, index, open, close, and separator.

<select id="selectPats" resultType="com.xxx.domain.PatientInfo">
  SELECT * FROM patient_info WHERE ID in
  <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

set

The set tag is used inside update statements to include only non‑null fields and automatically handle trailing commas.

<update id="updateStudent" parameterType="Object">
  UPDATE STUDENT
  <set>
    <if test="name!=null and name!=''">NAME = #{name},</if>
    <if test="major!=null and major!=''">MAJOR = #{major},</if>
    <if test="hobby!=null and hobby!=''">HOBBY = #{hobby}</if>
  </set>
  WHERE ID = #{id};
</update>

sql

The sql tag defines reusable fragments that can be included elsewhere with include. Example of a column list and a conditional where clause:

<sql id="Base_Column_List">
  ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY
</sql>

<sql id="Example_Where_Clause">
  where 1=1
  <trim suffixOverrides=",">
    <if test="id != null and id != ''">and id = #{id}</if>
    <if test="major != null and major != ''">and MAJOR = #{major}</if>
    ...
  </trim>
</sql>

include

The include tag inserts a previously defined sql fragment. The refid attribute can reference a fragment in the same mapper or in another mapper using the fully‑qualified class name.

<select id="selectAll" resultMap="BaseResultMap">
  SELECT
  <include refid="Base_Column_List" />
  FROM student
  <include refid="Example_Where_Clause" />
</select>

Advanced Tips

Avoiding Magic Numbers

Define constants in a Java class and reference them in MyBatis expressions using the @full.class.Name@CONSTANT syntax.

package com.xxx.core;
public class CommonConstants {
  public static final int DOC_TYPE = 1; // doctor
  public static final int NUR_TYPE = 2; // nurse
}

<if test="type!=null and [email protected]@DOC_TYPE">
  -- doctor logic
</if>
<if test="type!=null and [email protected]@NUR_TYPE">
  -- nurse logic
</if>

Referencing SQL from Other Mapper Files

Use the fully‑qualified mapper name in the refid attribute to include a fragment defined in a different XML file.

<include refid="com.xxx.dao.xxMapper.Base_Column_List" />

Conclusion

The article has covered all major MyBatis dynamic‑SQL tags, their attributes, and practical usage patterns, providing a solid foundation for building flexible, maintainable queries in Java backend projects.