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

MyBatis greatly simplifies development, but configuring XML can still be cumbersome; a good practice saves time and reduces errors. Below are essential dynamic SQL tags and their optimal usage.

1. foreach tag for looping containers

The foreach element supports attributes item , index , collection , open , separator , and close .

item: alias for each element in the collection

index: index of the element

open: string placed before the iteration (e.g., '(')

separator: delimiter between iterations (e.g., ',')

close: string placed after the iteration (e.g., ')')

The collection attribute is mandatory and varies with the parameter type:

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

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

If multiple parameters are passed, MyBatis wraps them into a Map; the key is the parameter name (e.g., list or array).

Note: MyBatis automatically wraps a List or array into a Map with keys "list" or "array" respectively.

// Mapper method
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 fuzzy query

<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 block; if none match, otherwise runs.

<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/>
        </choose>
    </where>
</select>

4. selectKey tag

Used in insert statements to generate and return primary keys (e.g., Oracle sequences or MySQL functions).

<insert id="createStudentAutoKey" parameterType="liming.student.manager.data.model.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

The if tag can be placed in any SQL statement to conditionally include fragments based on parameter values.

<select id="getStudentList_if" resultMap="studentResultMap" parameterType="liming.student.manager.data.model.StudentEntity">
    SELECT * FROM STUDENT_TBL ST
    WHERE 1=1
    <if test="studentName !=null">AND ST.STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')</if>
    <if test="studentSex != null and studentSex != ''">AND ST.STUDENT_SEX = #{studentSex}</if>
    ...
</select>

6. if + where combination

When many if conditions are used, the where tag automatically adds the WHERE keyword and removes leading AND / OR.

<select id="getStudentList_whereIf" resultMap="studentResultMap" parameterType="liming.student.manager.data.model.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>

7. if + set for update statements

The set tag removes trailing commas when some if blocks are skipped.

<update id="updateStudent_if_set" parameterType="liming.student.manager.data.model.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 (replace where/set)

The trim tag can act as a flexible where or set, removing unwanted prefixes or suffixes.

<select id="getStudentList_if_trim" resultMap="studentResultMap" parameterType="liming.student.manager.data.model.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>

9. foreach for array and list parameters

Example for an array:

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

Example for a List:

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

10. sql fragment and include

Reusable SQL fragments can be defined with <sql> and referenced via <include refid="..."/>.

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

These patterns help write clean, maintainable MyBatis XML and avoid common SQL generation errors.