Master MyBatis Dynamic SQL: foreach, if, where, choose, trim & selectKey

Using MyBatis foreach Tag

The foreach element iterates over a collection (List or array) to build IN clauses. When a List is passed, MyBatis wraps it in a Map with the key list; for an array, the key is array. Example:

public List<StudentEntity> queryById(List<String> userids);
<select id="queryById" resultMap="BaseResultMap">
  SELECT * FROM entity
  WHERE id IN
  <foreach collection="userids" item="userid" open="(" separator="," close=")">
    #{userid}
  </foreach>
</select>

Concat for Fuzzy Queries

To build a LIKE condition only when a parameter is present, combine concat with an if tag:

<select id="queryById" resultMap="BascResultMap" parameterType="entity">
  SELECT * FROM entity
  <where>
    <if test="name != null">
      name LIKE CONCAT('%', #{name}, '%')
    </if>
  </where>
</select>

choose (when, otherwise) Tag

The choose element works like a switch statement: it evaluates when conditions in order and executes the first true block; if none match, the otherwise block runs.

<select id="getUserList_choose" resultMap="resultMap_user" parameterType="User">
  SELECT * FROM User u
  <where>
    <choose>
      <when test="username != null">
        u.username LIKE CONCAT('%', #{username}, '%')
      </when>
      <when test="sex != null and sex != ''">
        AND u.sex = #{sex}
      </when>
      <when test="birthday != null">
        AND u.birthday = #{birthday}
      </when>
      <otherwise/>
    </choose>
  </where>
</select>

selectKey Tag for Auto‑generated Keys

In insert statements, selectKey can generate primary keys (e.g., using a MySQL function) and assign them to the entity before the actual insert.

<insert id="createStudentAutoKey" parameterType="StudentEntity" keyProperty="studentId">
  <selectKey keyProperty="studentId" resultType="String" order="BEFORE">
    SELECT nextval('student')
  </selectKey>
  INSERT INTO STUDENT_TBL (STUDENT_ID, STUDENT_NAME, ...) VALUES (#{studentId}, #{studentName}, ...)
</insert>

if Tag for Conditional Queries

The if tag can be placed inside any SQL statement to include a clause only when a condition is true, preventing null‑parameter errors.

<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
  SELECT * FROM STUDENT_TBL ST
  WHERE ST.STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')
</select>

When studentName is null, wrap the condition with if to omit it.

Combining if with where

Using where together with multiple if tags automatically removes stray AND/OR keywords, avoiding syntax errors when some parameters are null.

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="StudentEntity">
  SELECT ... FROM STUDENT_TBL ST
  <where>
    <if test="studentName != null">ST.STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')</if>
    <if test="studentSex != null and studentSex != ''">AND ST.STUDENT_SEX = #{studentSex}</if>
    ...
  </where>
</select>

if with set for Updates

When updating, wrap each column assignment with if inside a set tag so that null fields are omitted, preventing syntax errors and preserving existing values.

<update id="updateStudent_if_set" parameterType="StudentEntity">
  UPDATE STUDENT_TBL
  <set>
    <if test="studentName != null and studentName != ''">STUDENT_NAME = #{studentName},</if>
    <if test="studentSex != null and studentSex != ''">STUDENT_SEX = #{studentSex},</if>
    ...
  </set>
  WHERE STUDENT_ID = #{studentId}
</update>

Using trim to Replace where/set

The trim tag can add a prefix (e.g., WHERE or SET) and automatically remove leading AND/OR or trailing commas.

<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">
  SELECT ... FROM STUDENT_TBL ST
  <trim prefix="WHERE" prefixOverrides="AND|OR">
    <if test="studentName != null">ST.STUDENT_NAME LIKE ...</if>
    ...
  </trim>
</select>

<update id="updateStudent_if_trim" parameterType="StudentEntity">
  UPDATE STUDENT_TBL
  <trim prefix="SET" suffixOverrides=",">
    <if test="studentName != null and studentName != ''">STUDENT_NAME = #{studentName},</if>
    ...
  </trim>
  WHERE STUDENT_ID = #{studentId}
</update>

SQL Fragment and include

Reusable SQL snippets can be defined with <sql> and referenced via <include refid="..."/>, improving maintainability.

<sql id="orderAndItem">
  o.order_id, o.cid, o.address, o.create_date, o.orderitem_id,
  i.orderitem_id, i.product_id, i.count
</sql>

<select id="findOrderAndItemsByOid" parameterType="String" resultMap="BaseResultMap">
  SELECT
  <include refid="orderAndItem"/>
  FROM ordertable o
  JOIN orderitem i ON o.orderitem_id = i.orderitem_id
  WHERE o.order_id = #{orderId}
</select>

Note: You can pass a List instance or an array as a parameter object to MyBatis; it will automatically wrap it in a Map using the keys list or array respectively.