Master MyBatis Dynamic SQL: 9 Essential Tags and Best Practices

MyBatis offers powerful dynamic SQL capabilities, but XML configuration can be verbose; the following guide presents concise, reliable patterns for nine commonly used tags.

1. foreach tag for looping collections

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 parameters are wrapped in a Map.

MyBatis automatically wraps a List or array in a Map with keys "list" or "array" respectively.

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 for fuzzy queries

Use concat inside an if test to add optional LIKE conditions.

<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, executing the first when whose test is true, otherwise the otherwise block.

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

4. selectKey for auto‑generated keys

In insert statements, selectKey can retrieve keys generated by a database function or sequence.

<!-- insert with selectKey -->
<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 for conditional clauses

Wrap any SQL fragment with if to include it only when the test expression is true, preventing null‑value errors.

<!-- conditional select -->
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
  SELECT * 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 blocks omitted for brevity -->
  </where>
</select>

6. if + where to avoid stray AND/OR

Enclose multiple if statements inside a where tag so MyBatis inserts the WHERE keyword only when needed and removes leading logical operators.

7. if + set for update statements

Combine if with set to update only non‑null fields, automatically handling commas.

<!-- conditional update -->
<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 omitted -->
  </set>
  WHERE STUDENT_TBL.STUDENT_ID = #{studentId}
</update>

8. if + trim as a flexible alternative

The trim tag can replace where or set by specifying prefix and suffixOverrides to clean up extra keywords or commas.

<!-- trim replacing where -->
<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>
    <if test="studentSex != null and studentSex != '' ">AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}</if>
    <!-- other conditions omitted -->
  </trim>
</select>

9. foreach for IN clauses (list and array)

Iterate over a collection to build an IN clause. Use collection="list" for a List and collection="array" for an array.

<!-- foreach with 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>

<!-- foreach with 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>

SQL fragment reuse

Define reusable snippets with <sql id="..."> and include them via <include refid="..."/> to improve maintainability.

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

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

The above patterns help write clean, maintainable MyBatis XML mappings while avoiding common pitfalls such as stray commas, redundant logical operators, and null‑value errors.