Master MyBatis Dynamic SQL: IF, CHOOSE, FOREACH, and BIND Explained

MyBatis Dynamic SQL Overview

MyBatis offers a powerful dynamic SQL feature that simplifies conditional SQL construction, avoiding the error‑prone string concatenation required when using plain JDBC.

Supported Dynamic SQL Tags

Element

Purpose

Notes

if

Conditional statement

Single‑condition branch

choose (when, otherwise)

Java‑style if‑else

Multi‑condition branch

trim (where, set)

Helper element

Handles SQL concatenation issues

foreach

Loop statement

Commonly used for batch insert, update, query

bind

Creates a variable bound to the context

Helps with cross‑database compatibility and SQL injection prevention

1. Data Preparation

A Maven project named mybatis-dynamic is created with a student table:

DROP TABLE IF EXISTS `student`; 
CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别',
  `locked` tinyint(4) DEFAULT NULL COMMENT '状态(0:正常,1:锁定)',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '存入数据库的时间',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改的时间',
  `delete` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

Project structure illustration:

2. Using the if Tag

2.1 Conditional Query in WHERE

Three scenarios are demonstrated:

Only username provided – fuzzy search.

Only gender provided – exact match.

Both username and gender provided – combined criteria.

/**
 * Search by student information.
 * 1. If only name is provided, perform fuzzy search.
 * 2. If only gender is provided, perform exact match.
 * 3. If both are provided, combine conditions.
 */
List<Student> selectByStudentSelective(Student student);

<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
  select
  <include refid="Base_Column_List" />
  from student
  where 1=1
  <if test="name != null and name != ''">
    and name like concat('%', #{name}, '%')
  </if>
  <if test="sex != null">
    and sex = #{sex}
  </if>
</select>

Test cases show the generated SQL for each scenario, confirming that where 1=1 enables easy concatenation of optional conditions.

2.2 Conditional Update with if

/** Update non‑null fields */
int updateByPrimaryKeySelective(Student record);

<update id="updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student">
  update student
  <set>
    <if test="name != null">`name` = #{name},</if>
    <if test="phone != null">phone = #{phone},</if>
    <if test="email != null">email = #{email},</if>
    <if test="sex != null">sex = #{sex},</if>
    <if test="locked != null">locked = #{locked},</if>
    <if test="gmtCreated != null">gmt_created = #{gmtCreated},</if>
    <if test="gmtModified != null">gmt_modified = #{gmtModified},</if>
  </set>
  where student_id = #{studentId}
</update>

2.3 Conditional Insert with if

/** Insert only non‑null fields */
int insertSelective(Student record);

<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student">
  insert into student
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="studentId != null">student_id,</if>
    <if test="name != null">`name`,</if>
    <if test="phone != null">phone,</if>
    <if test="email != null">email,</if>
    <if test="sex != null">sex,</if>
    <if test="locked != null">locked,</if>
    <if test="gmtCreated != null">gmt_created,</if>
    <if test="gmtModified != null">gmt_modified,</if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="studentId != null">#{studentId},</if>
    <if test="name != null">#{name},</if>
    <if test="phone != null">#{phone},</if>
    <if test="email != null">#{email},</if>
    <if test="sex != null">#{sex},</if>
    <if test="locked != null">#{locked},</if>
    <if test="gmtCreated != null">#{gmtCreated},</if>
    <if test="gmtModified != null">#{gmtModified},</if>
  </trim>
</insert>

3. Using the choose Tag

The choose construct mimics if‑else logic, ensuring at least one when and optionally an otherwise.

/**
 * Query by ID if present, otherwise by name, else return null.
 */
Student selectByIdOrName(Student record);

<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
  select
  <include refid="Base_Column_List" />
  from student
  where 1=1
  <choose>
    <when test="studentId != null">and student_id = #{studentId}</when>
    <when test="name != null and name != ''">and name = #{name}</when>
    <otherwise>and 1=2</otherwise>
  </choose>
</select>

4. trim for where , set and where without 1=1

Using trim you can add a prefix only when content exists and remove leading logical operators.

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

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

5. The foreach Tag

5.1 Using foreach in WHERE

/** Retrieve users by a collection of IDs */
List<Student> selectByStudentIdList(List<Integer> ids);

<select id="selectByStudentIdList" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from student
  where student_id in
  <foreach collection="list" item="id" open="(" close=")" separator="," index="i">#{id}</foreach>
</select>

5.2 Batch Insert with foreach

/** Batch insert students */
int insertList(List<Student> students);

<insert id="insertList">
  insert into student(name, phone, email, sex, locked)
  values
  <foreach collection="list" item="student" separator=",">
    (#{student.name}, #{student.phone}, #{student.email}, #{student.sex}, #{student.locked})
  </foreach>
</insert>

6. The bind Tag

When database functions differ (e.g., MySQL supports multiple parameters while Oracle supports two), bind can create a variable to achieve cross‑database compatibility.

<if test="name != null and name != ''">
  <bind name="nameLike" value="'%'+name+'%'"/>
  and name like #{nameLike}
</if>

All examples are accompanied by JUnit tests that print the generated SQL and verify the results.