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

MyBatis provides powerful dynamic SQL capabilities that simplify conditional query construction compared to raw JDBC.

1. Data Preparation

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

2. Conditional Tags

if tags are used to add WHERE clauses only when parameters are non‑null. Example:

SELECT * 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>

The same pattern applies to UPDATE statements, inserting only non‑null columns.

3. choose/when/otherwise

The choose construct mimics if‑else logic. Example for selecting by ID or name:

SELECT * 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>

4. trim and set

The where and set tags can be replaced by trim to automatically remove leading AND / OR and add appropriate prefixes or suffixes.

5. foreach

foreach

iterates over collections for IN clauses or batch inserts. Example for selecting by a list of IDs:

SELECT * FROM student WHERE student_id IN
  (<foreach collection="list" item="id" separator="," >#{id}</foreach>)

For batch insertion:

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>

6. bind

The bind tag creates a temporary variable, useful for database‑specific syntax differences, e.g., creating a nameLike parameter for Oracle and MySQL.

7. Testing

JUnit test methods illustrate each dynamic SQL scenario, printing generated SQL and result sets to verify correct behavior.

All code examples are available at https://github.com/homejim/mybatis-examples .