Advanced MyBatis Dynamic SQL Techniques and Best Practices

MyBatis greatly simplifies Java persistence, but configuring XML can still be cumbersome; this article shares high‑quality writing patterns that save time and reduce errors.

1. foreach tag – attributes include item , index , collection , open , separator , close . The collection value differs for a single List ( list ), an array ( array ), or a custom Map where the key is the map entry name.

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 – use <if> to add a LIKE clause 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 – works like a Java switch , selecting the first true when condition.

<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 tag – generates primary keys (e.g., via a database sequence or function) and returns them to the Java object.

<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 combined with where – prevents extra AND/OR when a condition is omitted.

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
    SELECT ST.* 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>
        ... (other conditions)
    </where>
</select>

6. if + set for updates – updates only non‑null fields and removes trailing commas.

<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>
        ... (other fields)
    </set>
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};
</update>

7. trim tag – replaces where or set to automatically strip 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 CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
        </if>
        ... (other conditions)
    </trim>
</select>

8. foreach for array and list parameters – demonstrates both collection="array" and collection="list" usages.

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

9. Reusable SQL fragments – define with <sql id="orderAndItem"> and include via <include refid="orderAndItem"/> .

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

By following these patterns, developers can write MyBatis mappings that are concise, maintainable, and free from common runtime SQL errors.