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

MyBatis greatly simplifies Java data access, but configuring XML can still be verbose; this guide shares efficient patterns to reduce errors and improve maintainability.

1. Using the foreach tag

The foreach element supports attributes item , index , collection , open , separator , and close . The collection attribute must be set correctly: use list for a List , array for an array, or the map key when passing a custom Map .

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 one; 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(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')
            </when>
            <when test="sex != null and sex != '' ">
                AND u.sex = #{sex, jdbcType=INTEGER}
            </when>
            <when test="birthday != null ">
                AND u.birthday = #{birthday, jdbcType=DATE}
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

4. selectKey for auto‑generated keys

In insert statements, selectKey can retrieve generated primary keys (e.g., Oracle sequences or MySQL functions) and assign them to the entity.

<!-- insert with auto key -->
<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, ... )
    VALUES (#{studentId}, #{studentName}, ... )
</insert>

StudentEntity entity = new StudentEntity();
entity.setStudentName("黎明你好");
...;
this.dynamicSqlMapper.createStudentAutoKey(entity);
System.out.println("新增学生ID: " + entity.getStudentId());

5. if tag for conditional WHERE clauses

The if tag allows selective inclusion of conditions; when a parameter is null or empty, the corresponding clause is omitted, preventing errors or empty results.

<!-- query with optional name filter -->
<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
    SELECT * from STUDENT_TBL ST
    WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</select>

Using if ensures the condition is added only when studentName is not null .

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

6. Combining if with where

When many if tags are used, stray AND or OR can produce invalid SQL. Wrapping the conditions with a where tag lets MyBatis automatically insert the WHERE keyword and trim leading logical operators.

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.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 = #{studentSex} </if>
        ...
    </where>
</select>

7. if + set for UPDATE statements

Using if inside a set block prevents extra commas when some fields are null . Only non‑null fields are included in the generated UPDATE statement.

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

8. trim replaces where and 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>
        <if test="studentSex != null and studentSex != '' "> AND ST.STUDENT_SEX = #{studentSex} </if>
        ...
    </trim>
</select>

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

9. foreach for IN clauses (list and array)

The foreach element is essential for iterating collections in dynamic SQL, especially for IN conditions. It works with both List and array parameters.

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

@Test
public void test7_foreach(){
    String[] classIds = {"20000001","20000002"};
    List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_array(classIds);
    for(StudentEntity e : list){ System.out.println(e); }
}

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

@Test
public void test7_2_foreach(){
    ArrayList<String> classIdList = new ArrayList<>();
    classIdList.add("20000001");
    classIdList.add("20000002");
    List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_list(classIdList);
    for(StudentEntity e : list){ System.out.println(e); }
}

10. SQL fragment tags

The <sql> tag defines reusable SQL snippets, which can be included elsewhere with <include refid="..."/> , improving readability and reducing duplication.

<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 techniques together enable developers to write concise, maintainable, and robust MyBatis mappings for complex backend data operations.