MyBatis Dynamic SQL Tags: if, choose, trim, foreach, bind for Conditional Queries, Updates, Inserts, and Batch Operations

MyBatis provides powerful dynamic SQL capabilities that simplify conditional SQL construction, reducing errors compared to manual string concatenation.

1. Data preparation – A Maven project named mybatis-dynamic is created with a student table containing fields like student_id, name, phone, email, sex, locked, and timestamps.

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 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';

2. Using the if tag – The if tag is commonly used in WHERE, UPDATE, and INSERT statements to include conditions only when corresponding parameters are non‑null.

2.1 if in WHERE clause

Three scenarios are demonstrated:

Only name is provided → fuzzy search using LIKE.

Only sex is provided → exact match.

Both name and sex are provided → combined conditions.

/**
 * 根据输入的学生信息进行条件检索
 * 1. 当只输入用户名时，使用用户名进行模糊检索；
 * 2. 当只输入性别时，使用性别进行完全匹配；
 * 3. 当用户名和性别都存在时，用这两个条件进行查询匹配
 */
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>

The generated SQL uses the where 1=1 trick so that subsequent and clauses can be appended safely.

2.2 if in UPDATE

Only non‑null fields are updated, preventing overwriting unchanged columns.

/**
 * 更新非空属性
 */
int updateByPrimaryKeySelective(Student record);

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

2.3 if in INSERT

Only non‑null columns are inserted, using a dynamic trim to handle commas.

/**
 * 非空字段才进行插入
 */
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,jdbcType=INTEGER},</if>
    <if test="name != null">#{name,jdbcType=VARCHAR},</if>
    <if test="phone != null">#{phone,jdbcType=VARCHAR},</if>
    <if test="email != null">#{email,jdbcType=VARCHAR},</if>
    <if test="sex != null">#{sex,jdbcType=TINYINT},</if>
    <if test="locked != null">#{locked,jdbcType=TINYINT},</if>
    <if test="gmtCreated != null">#{gmtCreated,jdbcType=TIMESTAMP},</if>
    <if test="gmtModified != null">#{gmtModified,jdbcType=TIMESTAMP},</if>
  </trim>
</insert>

Test cases show the generated SQL and results for each scenario.

3. Using the choose tag

The choose tag implements if‑else logic. An example selects a student by studentId if present, otherwise by name, and returns no rows when neither is supplied.

/**
 * - 当 student_id 有值时，使用 student_id 查询；
 * - 当 student_id 没有值时，使用 name 查询；
 * - 否则返回空
 */
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 and set

The trim tag can replace explicit where or set tags, automatically adding or removing leading/trailing keywords.

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

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

5. foreach for collections

The foreach tag iterates over arrays, Lists, or Maps, useful for IN clauses and batch operations.

5.1 foreach in WHERE

/**
 * 获取 id 集合中的用户信息
 */
List<Student> selectByStudentIdList(List<Integer> ids);

<select id="selectByStudentIdList" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
  select
  <include refid="Base_Column_List" />
  from student
  where student_id in
  <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</select>

5.2 Batch insert with foreach

/**
 * 批量插入学生
 */
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. bind for reusable expressions

The bind tag defines a variable using an OGNL expression, allowing database‑specific syntax handling (e.g., MySQL concat vs. Oracle two‑argument version).

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

7. Full source code

All Java test methods demonstrating the above SQL statements are available at https://github.com/homejim/mybatis-examples .