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

MyBatis offers dynamic SQL to simplify conditional SQL building, using OGNL expressions and tags like if, choose, trim, foreach, and bind.

First, a Maven project with a student table is created, and the corresponding Java entity and mapper interfaces are defined.

The if tag is demonstrated for WHERE clauses, selective queries, UPDATE statements, and INSERT operations, showing how to include conditions only when parameters are non‑null. Example:

<if test="name != null and name != ''"> and name like concat('%', #{name}, '%') </if>

The choose tag implements if‑else logic, selecting queries based on the presence of studentId or name. Example:

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

The trim tag is used to clean up leading AND/OR in WHERE clauses and trailing commas in SET clauses, with attributes prefix, prefixOverrides, suffix, and suffixOverrides. Example for WHERE:

<trim prefix="WHERE" prefixOverrides="AND |OR">
  ...
</trim>

And for SET:

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

The foreach tag handles collections for IN clauses, batch inserts, and other iterative SQL fragments, with attributes such as collection, item, open, close, and separator. Example for an IN clause:

<foreach collection="list" item="id" open="(" close=")" separator=",">
  #{id}
</foreach>

For batch insertion:

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

The bind tag creates reusable variables, illustrated by adapting a MySQL LIKE expression for Oracle compatibility:

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

Each section includes full XML mapper snippets and JUnit test methods that execute the generated SQL and display results, demonstrating how dynamic SQL simplifies complex query construction in MyBatis.