Advanced MyBatis Dynamic SQL: foreach, if, choose, trim, selectKey, and SQL Fragments

MyBatis greatly simplifies Java persistence, but certain XML configurations can still be cumbersome. This guide shares best‑practice patterns for MyBatis dynamic SQL to save time and reduce errors.

1. foreach Tag for Looping Collections

The foreach element supports attributes item , index , collection , open , separator , and close . The collection attribute must be set and its value depends on the parameter type:

When a single List parameter is passed, collection="list" .

When a single array parameter is passed, collection="array" .

When multiple parameters are wrapped in a Map , collection is the key of the list or array inside the map.

Official MyBatis documentation states that a List is wrapped with the key list and an array with the key array when passed as a single argument.

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

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

2. concat for Fuzzy Queries

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

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

3. choose (when, otherwise) Tag

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

<!-- choose example -->
<select id="getUserList_choose" resultMap="resultMap_user" parameterType="com.yiibai.pojo.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>
</otherwise>
        </choose>
    </where>
</select>

4. selectKey Tag for Auto‑Generated Keys

In insert statements, selectKey can retrieve a generated primary key (e.g., from a MySQL function) and assign it to the entity.

<!-- insert with auto‑generated key -->
<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, STUDENT_SEX, STUDENT_BIRTHDAY, STUDENT_PHOTO, CLASS_ID, PLACE_ID)
    VALUES (#{studentId}, #{studentName}, #{studentSex}, #{studentBirthday}, #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler}, #{classId}, #{placeId})
</insert>

5. if Tag for Conditional Queries

Wrap optional conditions in if tags to avoid null‑parameter errors. Example shows a flexible SELECT that only adds WHERE clauses when the corresponding fields are non‑null.

<select id="getStudentList_if" resultMap="studentResultMap" 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>

6. Combining if and where

When many if tags are used, the where tag automatically removes leading AND / OR to prevent syntax errors.

<select id="getStudentList_whereIf" resultMap="studentResultMap" parameterType="StudentEntity">
    SELECT ... FROM STUDENT_TBL ST
    <where>
        <if test="studentName !=null"> ST.STUDENT_NAME LIKE ... </if>
        <if test="studentSex != null and studentSex != ''"> AND ST.STUDENT_SEX = ... </if>
        ...
    </where>
</select>

7. if + set for Update Statements

Use set together with if to build an UPDATE that only modifies non‑null fields, avoiding extra commas.

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

8. trim Tag as a Flexible Alternative

The trim tag can replace both where and set by specifying prefix , suffixOverrides , etc., to clean up redundant keywords or commas.

<select id="getStudentList_if_trim" resultMap="studentResultMap">
    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>

9. foreach for IN Clauses (list and array)

The foreach element is essential for iterating over collections in IN conditions. MyBatis automatically wraps a List as list and an array as array .

// array parameter example
public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);

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

// list parameter example
public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);

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

10. sql Fragment Tag

Define reusable SQL snippets with <sql id="..."> and include them via <include refid="..."/> to improve 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="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>

Finally, the author invites readers to follow the "码猿技术专栏" public account to obtain PDF collections of Spring Cloud, Spring Boot, and MyBatis advanced tutorials.