MyBatis Dynamic SQL: Using if, choose, foreach, trim, and bind Tags with Practical Code Samples

MyBatis offers a powerful dynamic SQL feature that eliminates the tedious and error‑prone manual string concatenation when building SQL statements based on varying conditions.

1. Data Preparation

A Maven project mybatis-dynamic is created and a student table is defined:

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. if Tag

The if tag is the most frequently used dynamic element. It must be combined with the test attribute.

2.1 Using if in WHERE Conditions

Three possible query scenarios are demonstrated:

Only name is provided – perform a fuzzy search.

Only sex is provided – perform an exact match.

Both name and sex are provided – combine the conditions.

Mapper method:

/**
 * Query students by optional 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>

Unit test shows the generated SQL for each case and the resulting rows.

2.2 Using if in UPDATE Statements

Only non‑null fields are updated:

int updateByPrimaryKeySelective(Student record);

Dynamic SQL:

<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 Using if in INSERT Statements

Only non‑null columns are inserted:

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. choose Tag

The choose element implements if‑else logic with one or more when clauses and an optional otherwise clause.

Example: query by studentId if present, otherwise by name, otherwise return no rows.

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, where, and set Tags

The where and set tags are special cases of trim. They automatically add the appropriate prefix and remove leading logical operators.

Example using where to avoid a dangling WHERE 1=1 when no conditions match:

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

Similarly, set can be expressed with trim prefix="SET" suffixOverrides=",".

5. foreach Tag

The foreach element iterates over collections, arrays, or maps, useful for IN clauses and batch operations.

5.1 Using foreach in WHERE

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=",">
    #{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 Tag

The bind tag defines a temporary variable using an OGNL expression, allowing database‑specific adjustments.

Example for MySQL LIKE with concatenation compatible with Oracle:

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

7. Full Example Project

The complete source code, Maven configuration, and test cases are available at https://github.com/homejim/mybatis-examples .