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

MyBatis offers convenient data access but configuring XML can be cumbersome; this article shares best practices for writing clean and error‑free dynamic SQL.

1. foreach tag – explains attributes (item, index, collection, open, separator, close) and three cases for the collection attribute (list, array, map) with official note.

//mapper中我们要为这个方法传递的是一个容器,将容器中的元素一个一个的
//拼接到xml的方法中就要使用这个forEach这个标签了
public List<Entity> queryById(List<String> userids);
    //对应的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 – shows how to build conditional LIKE clauses using concat .

//比如说我们想要进行条件查询,但是几个条件不是每次都要使用,那么我们就可以
//通过判断是否拼接到sql中
  <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 – works like a switch statement to select the first true 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 – demonstrates generating auto‑increment keys for insert statements in MySQL.

<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 – conditional inclusion of WHERE clauses based on parameter nullity.

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

6. if + where – combines if with where to avoid extra AND/OR keywords.

<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>  
        <if test="studentBirthday != null ">  
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
        </if>  
        <if test="classId != null and classId!= '' ">  
            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}  
        </if>  
        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">  
            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeId != null and placeId != '' ">  
            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">  
            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="studentId != null and studentId != '' ">  
            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}  
        </if>  
    </where>   
</select>

7. if + set – updates only non‑null fields using set to handle 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>  
        <if test="studentBirthday != null ">  
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},  
        </if>  
        <if test="studentPhoto != null ">  
            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},  
        </if>  
        <if test="classId != '' ">  
            STUDENT_TBL.CLASS_ID = #{classId}  
        </if>  
        <if test="placeId != '' ">  
            STUDENT_TBL.PLACE_ID = #{placeId}  
        </if>  
    </set>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};      
</update>

8. trim tag – replaces where / set to automatically remove redundant keywords.

<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>  
        <if test="studentBirthday != null ">  
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
        </if>  
        <if test="classId != null and classId!= '' ">  
            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}  
        </if>  
        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">  
            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeId != null and placeId != '' ">  
            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">  
            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="studentId != null and studentId != '' ">  
            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}  
        </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>  
        <if test="studentSex != null and studentSex != '' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},  
        </if>  
        <if test="studentBirthday != null ">  
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},  
        </if>  
        <if test="studentPhoto != null ">  
            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},  
        </if>  
        <if test="classId != '' ">  
            STUDENT_TBL.CLASS_ID = #{classId},  
        </if>  
        <if test="placeId != '' ">  
            STUDENT_TBL.PLACE_ID = #{placeId},  
        </if>  
    </trim>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId}  
</update>

9. foreach for arrays and lists – shows how to pass List or array parameters and iterate them in IN clauses.

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

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

Finally, the article introduces reusable SQL fragments with <sql> and <include> tags.

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