Master MyBatis Dynamic SQL: foreach, if, choose, trim, and selectKey Explained

MyBatis foreach Tag

The foreach tag iterates over a collection and supports attributes item, index, collection, open, separator, and close. When collection is a single‑parameter List it is referenced as list; for an array it is array; for multiple parameters MyBatis wraps them into a Map where the key is the parameter name.

public List<Entity> queryById(List<String> userids);

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

Concat Fuzzy Query

Use concat inside an if tag to add a LIKE condition only when the parameter is not null.

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

choose (when, otherwise) Tag

The choose tag works like a switch statement: it evaluates when conditions in order and executes the first true branch; 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(CONCAT('%', #{username}), '%')
      </when>
      <when test="sex != null and sex != ''">
        AND u.sex = #{sex}
      </when>
      <otherwise>
        -- no condition
      </otherwise>
    </choose>
  </where>
</select>

selectKey Tag

For auto‑generated primary keys, selectKey can run a query before or after the main insert. Example uses a MySQL function nextval('student') to set studentId on the entity.

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

if Tag and Conditional WHERE

The if tag can be placed inside select or where to add conditions only when parameters are non‑null. Combining many if tags directly under where may produce stray AND / OR; wrapping them with a where tag automatically removes leading logical operators.

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

set Tag and if+set for Updates

When updating, set removes trailing commas. Using if inside set updates only non‑null fields.

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

trim Tag as Flexible where/set

trim

can replace both where and set by specifying prefix and suffixOverrides (or prefixOverrides) to clean up extra logical operators or 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 CONCAT('%', #{studentName}, '%')
    </if>
    <if test="studentSex != null and studentSex != ''">
      AND ST.STUDENT_SEX = #{studentSex}
    </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>
    <if test="studentSex != null and studentSex != ''">
      STUDENT_SEX = #{studentSex},
    </if>
    ...
  </trim>
  WHERE STUDENT_ID = #{studentId}
</update>

foreach with Array and List Parameters

When the method receives an array, use collection="array"; for a List, use collection="list". Both generate an IN clause.

<select id="getStudentListByClassIds_foreach_array" resultMap="resultMap_studentEntity">
  SELECT ... FROM STUDENT_TBL ST
  WHERE ST.CLASS_ID IN
  <foreach collection="array" item="classIds" open="(" separator="," close=")">
    #{classIds}
  </foreach>
</select>

<select id="getStudentListByClassIds_foreach_list" resultMap="resultMap_studentEntity">
  SELECT ... FROM STUDENT_TBL ST
  WHERE ST.CLASS_ID IN
  <foreach collection="list" item="classIdList" open="(" separator="," close=")">
    #{classIdList}
  </foreach>
</select>

SQL Fragment Include

Define reusable SQL pieces with sql and reference them via include.

<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="java.lang.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>

Reference Image