MyBatis Dynamic SQL Tutorial: Using if, choose, trim, foreach, and bind Tags with Code Examples

MyBatis offers a powerful dynamic SQL feature that simplifies conditional SQL construction compared to raw JDBC.

Dynamic SQL tags supported include if, choose, trim, foreach, and bind, each demonstrated with Maven project setup, table creation, and mapper methods.

1. Data Preparation

Created a Maven project mybatis-dynamic and a student table with fields such as student_id, name, phone, email, sex, locked, gmt_created, 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. if Tag

The if tag is frequently used in SELECT, DELETE, UPDATE statements and must be combined with the test attribute.

2.1 Conditional WHERE

Example: query by name (fuzzy), by sex (exact), or both.

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 methods demonstrate queries with only name, only sex, and both conditions, showing the generated SQL and results.

2.2 Conditional UPDATE

Only non‑null fields are updated using if inside a set block.

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 Conditional INSERT

Insert only non‑null columns using if inside trim tags.

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 tag implements if‑else logic with when and otherwise.

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 Tag

The trim tag can replace both where and set to automatically remove leading/trailing logical operators.

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

5. foreach Tag

Iterates over collections, arrays, maps, or iterable objects for IN clauses or batch operations.

5.1 IN Clause Example

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 Example

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

Creates a temporary variable using OGNL to simplify expressions, e.g., building a LIKE pattern compatible with different databases.

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

The article concludes with author information and links to the source repository.