Master MyBatis Dynamic SQL: 9 Essential Tags and Best Practices

MyBatis offers many conveniences, but configuring XML can still be tedious; using good patterns can save time and reduce errors.

1. foreach tag for looping containers

The foreach element supports attributes item , index , collection , open , separator , close . The collection attribute must be specified and its value depends on the parameter type: list for a List, array for an array, or a map key when parameters are wrapped into a Map.

//mapper method
public List<Entity> queryById(List<String> userids);

<!-- corresponding XML -->
<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

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

choose

evaluates its internal when conditions sequentially; the first true condition stops further checks. If none match, the otherwise block is executed, similar to Java's switch statement.

<!-- 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>
                <!-- default condition -->
            </otherwise>
        </choose>
    </where>
</select>

4. selectKey tag

selectKey

enables automatic primary‑key generation in insert statements, useful for Oracle sequences or MySQL functions.

<!-- insert with auto‑generated 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, 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 many SQL statements; it adds a condition only when the corresponding parameter is not null or empty.

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

6. if + where

When multiple if tags are used, wrapping them with where automatically removes stray AND / OR keywords, preventing syntax errors.

<!-- where with if -->
<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
    SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ST.STUDENT_SEX, ST.STUDENT_BIRTHDAY, ST.STUDENT_PHOTO, ST.CLASS_ID, ST.PLACE_ID
    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>
        <!-- additional if conditions omitted for brevity -->
    </where>
</select>

7. if + set for update statements

Using if inside a set block prevents errors caused by null parameters and removes unnecessary commas.

<!-- update with if and set -->
<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>
        <if test="studentBirthday != null ">
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
        </if>
        <!-- other fields omitted -->
    </set>
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};
</update>

8. if + trim replacing where/set

The trim tag can dynamically add or remove prefixes/suffixes, serving as a flexible alternative to where and set.

<!-- trim as where -->
<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">
    SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ST.STUDENT_SEX, ST.STUDENT_BIRTHDAY, ST.STUDENT_PHOTO, ST.CLASS_ID, ST.PLACE_ID
    FROM STUDENT_TBL ST
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <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>
        <!-- other if conditions omitted -->
    </trim>
</select>

<!-- trim as set -->
<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>
        <if test="studentSex != null and studentSex != '' ">
            STUDENT_TBL.STUDENT_SEX = #{studentSex},
        </if>
        <!-- other fields omitted -->
    </trim>
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId}
</update>

9. foreach for IN conditions

The foreach element is essential for iterating collections in IN clauses; it works with both List and array parameters.

<!-- foreach with array -->
<select id="getStudentListByClassIds_foreach_array" resultMap="resultMap_studentEntity">
    SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ST.STUDENT_SEX, ST.STUDENT_BIRTHDAY, ST.STUDENT_PHOTO, ST.CLASS_ID, ST.PLACE_ID
    FROM STUDENT_TBL ST
    WHERE ST.CLASS_ID IN
    <foreach collection="array" item="classIds" open="(" separator="," close=")">
        #{classIds}
    </foreach>
</select>

<!-- foreach with list -->
<select id="getStudentListByClassIds_foreach_list" resultMap="resultMap_studentEntity">
    SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ST.STUDENT_SEX, ST.STUDENT_BIRTHDAY, ST.STUDENT_PHOTO, ST.CLASS_ID, ST.PLACE_ID
    FROM STUDENT_TBL ST
    WHERE ST.CLASS_ID IN
    <foreach collection="list" item="classIdList" open="(" separator="," close=")">
        #{classIdList}
    </foreach>
</select>

SQL fragment and include

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

<!-- sql fragment definition -->
<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>

<!-- using the fragment -->
<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>