10 Powerful MyBatis SQL Patterns Used by Top Companies

1. Using the forEach tag to iterate collections

The forEach element supports attributes item, index, collection, open, separator and close. The most error‑prone attribute is collection, which varies based on the parameter type:

If a single List is passed, collection="list".

If a single array is passed, collection="array".

If multiple parameters are passed, MyBatis wraps them into a Map where the key is the parameter name; the collection value is the key of the List or array inside that map.

Note: When you pass a List or an array to MyBatis, it automatically wraps it in a Map using the names list and array as keys.

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. Fuzzy search with concat

<select id="queryById" resultMap="BascResultMap" parameterType="entity">
  SELECT * FROM entity
  <where>
    <if test="name!=null">
      name LIKE concat('%', concat(#{name}, '%'))
    </if>
  </where>
</select>

3. Conditional logic with choose , when , otherwise

The choose tag works like a Java switch: it evaluates when conditions in order and executes the first true branch; if none match, the otherwise block runs.

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

4. Generating keys with selectKey

For databases that require a generated primary key (e.g., Oracle sequences or MySQL functions), selectKey can retrieve the key before or after the insert statement.

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

StudentEntity entity = new StudentEntity();
entity.setStudentName("黎明你好");
entity.setStudentSex(1);
entity.setStudentBirthday(DateUtil.parse("1985-05-28"));
entity.setClassId("20000001");
entity.setPlaceId("70000001");
this.dynamicSqlMapper.createStudentAutoKey(entity);
System.out.println("新增学生ID: " + entity.getStudentId());

5. Conditional queries with if

The if tag can be placed inside any SQL statement. When a parameter is null or empty, the corresponding condition is omitted, preventing errors or empty results.

<!-- Query students with optional name filter -->
<select id="getStudentList_if" resultMap="studentResultMap" parameterType="StudentEntity">
  SELECT * FROM STUDENT_TBL ST
  WHERE
    <if test="studentName !=null">
      ST.STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')
    </if>
    <if test="studentSex != null and studentSex != ''">
      AND ST.STUDENT_SEX = #{studentSex}
    </if>
    <if test="studentBirthday != null">
      AND ST.STUDENT_BIRTHDAY = #{studentBirthday}
    </if>
    <if test="classId != null and classId != ''">
      AND ST.CLASS_ID = #{classId}
    </if>
    <if test="placeId != null and placeId != ''">
      AND ST.PLACE_ID = #{placeId}
    </if>
</select>

6. Combining if with where to avoid stray keywords

When many if tags are used, the generated SQL may contain an extra WHERE AND. Wrapping the conditions with a where tag lets MyBatis insert the WHERE only when there is at least one child element, and it removes leading AND / OR.

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="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('%', #{studentName}, '%')</if>
    <if test="studentSex != null and studentSex != ''">AND ST.STUDENT_SEX = #{studentSex}</if>
    <if test="studentBirthday != null">AND ST.STUDENT_BIRTHDAY = #{studentBirthday}</if>
    <if test="classId != null and classId != ''">AND ST.CLASS_ID = #{classId}</if>
    <if test="placeId != null and placeId != ''">AND ST.PLACE_ID = #{placeId}</if>
  </where>
</select>

7. Updating with if + set

When an update statement contains nullable fields, using if alone can leave a trailing comma. The set tag removes such commas, and the combination if + set updates only non‑null columns.

<update id="updateStudent_if_set" parameterType="StudentEntity">
  UPDATE STUDENT_TBL
  <set>
    <if test="studentName != null and studentName != ''">STUDENT_NAME = #{studentName},</if>
    <if test="studentSex != null and studentSex != ''">STUDENT_SEX = #{studentSex},</if>
    <if test="studentBirthday != null">STUDENT_BIRTHDAY = #{studentBirthday},</if>
    <if test="studentPhoto != null">STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},</if>
    <if test="classId != ''">CLASS_ID = #{classId},</if>
    <if test="placeId != ''">PLACE_ID = #{placeId},</if>
  </set>
  WHERE STUDENT_ID = #{studentId};
</update>

8. Replacing where / set with trim

The trim tag can add a prefix (e.g., WHERE or SET) and automatically strip leading AND / OR or trailing commas.

<!-- 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('%', #{studentName}, '%')</if>
    <if test="studentSex != null and studentSex != ''">AND ST.STUDENT_SEX = #{studentSex}</if>
    <if test="studentBirthday != null">AND ST.STUDENT_BIRTHDAY = #{studentBirthday}</if>
    <if test="classId != null and classId != ''">AND ST.CLASS_ID = #{classId}</if>
    <if test="placeId != null and placeId != ''">AND ST.PLACE_ID = #{placeId}</if>
  </trim>
</select>

<!-- trim as set -->
<update id="updateStudent_if_trim" parameterType="StudentEntity">
  UPDATE STUDENT_TBL
  <trim prefix="SET" suffixOverrides=",">
    <if test="studentName != null and studentName != ''">STUDENT_NAME = #{studentName},</if>
    <if test="studentSex != null and studentSex != ''">STUDENT_SEX = #{studentSex},</if>
    <if test="studentBirthday != null">STUDENT_BIRTHDAY = #{studentBirthday},</if>
    <if test="studentPhoto != null">STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},</if>
    <if test="classId != ''">CLASS_ID = #{classId},</if>
    <if test="placeId != ''">PLACE_ID = #{placeId},</if>
  </trim>
  WHERE STUDENT_ID = #{studentId}
</update>

9. Iterating arrays and lists with foreach

When building IN clauses, foreach can handle both array and List parameters. MyBatis uses the key array for arrays and list for lists.

// Mapper method for array parameter
public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);

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

@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);
  }
}

// Mapper method for List parameter
public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);

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

@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. Reusing SQL fragments with sql and include

The sql tag defines a reusable snippet; include inserts it by referencing its id.

<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 demonstrate how to write concise, maintainable, and error‑free MyBatis dynamic SQL, covering iteration, conditional logic, key generation, and SQL reuse.