Using MyBatis Dynamic SQL Tags (if, choose, trim, foreach, bind) for Conditional Queries and Updates

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

The dynamic SQL mechanism relies on OGNL expressions and supports several tags:

if : single‑condition branch

choose (when, otherwise) : multi‑condition branch similar to Java if‑else trim (where, set) : auxiliary element for handling leading/trailing keywords

foreach : iterates over collections, arrays, or maps for batch operations

bind : defines a variable in the OGNL context for reuse

1. Data Preparation

A Maven project mybatis-dynamic is created with a student table containing fields such as student_id, name, phone, email, sex, locked, gmt_created, and gmt_modified.

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

2.1 In WHERE Clause

The if tag is frequently used for conditional queries. Example interface method:

/**
 * Query students based on provided fields.
 * @param student
 * @return List<Student>
 */
List<Student> selectByStudentSelective(Student student);

Corresponding dynamic SQL:

<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 trick where 1=1 allows all subsequent conditions to be prefixed with and without worrying about the first clause.

2.2 In UPDATE Statement

When updating only non‑null fields, if tags prevent overwriting unchanged columns:

/**
 * Update non‑null properties
 */
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 In INSERT Statement

Only non‑null columns are inserted using if inside trim blocks:

/**
 * 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,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>

3. Using the choose Tag

The choose tag implements if‑else logic. Example: select by ID if present, otherwise by name, otherwise return no rows.

/**
 * Select by ID or 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. Using trim (and where / set )

The trim tag can add or remove prefixes/suffixes automatically, eliminating the need for manual where 1=1 handling.

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

Similarly, for set:

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

5. Using the foreach Tag

5.1 In WHERE Clause

Query a list of IDs:

/**
 * Get students by a list 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

/**
 * 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. Using the bind Tag

The bind tag creates a reusable variable, useful for handling database‑specific syntax differences. Example for a name‑like condition:

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

Conclusion

The article demonstrates practical usage of MyBatis dynamic SQL tags to write clean, maintainable, and database‑agnostic SQL statements, covering conditional queries, selective updates, optional inserts, multi‑branch logic, batch operations, and variable binding.