Backend Development 20 min read
Master MyBatis Dynamic SQL: 9 Essential Tag Techniques for Cleaner Code
This article walks through MyBatis dynamic SQL tags—foreach, concat, choose, selectKey, if, where, trim, set, and sql fragments—explaining their attributes, usage patterns, and common pitfalls, and provides complete XML and Java code examples for each technique.
macrozheng
macrozheng
MyBatis greatly simplifies Java data access, but configuring XML can still be cumbersome; using proper dynamic‑SQL tags saves time and reduces errors. Below are nine high‑quality patterns with explanations and full code samples.
1. foreach tag for iterating collections
The
foreachelement supports attributes
item,
index,
collection,
open,
separator, and
close. The
collectionattribute must be set correctly:
item: alias for each element.
index: loop index.
open: prefix string, e.g., "(".
separator: delimiter between items.
close: suffix string, e.g., ")".
Three cases for
collection:
If a single
Listparameter is passed, use
list.
If a single array is passed, use
array.
If multiple parameters are passed, MyBatis wraps them into a
Map; the key is the parameter name, so
collectionshould be the map key that holds the list or array.
MyBatis automatically wraps a List or array into a Map with keys list and array respectively.
<code>public List<Entity> queryById(List<String> userids);</code><code><select id="queryById" resultMap="BaseResultMap">
SELECT * FROM entity
WHERE id IN
<foreach collection="userids" item="userid" open="(" separator="," close=")">
#{userid}
</foreach>
</select></code>2. concat for fuzzy queries
<code><select id="queryById" resultMap="BaseResultMap" parameterType="entity">
SELECT * FROM entity
<where>
<if test="name != null">
name LIKE concat('%', concat(#{name}, '%'))
</if>
</where>
</select></code>3. choose (when, otherwise) tag
<code><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}), '%')
</when>
<when test="sex != null and sex != ''">
AND u.sex = #{sex}
</when>
<when test="birthday != null">
AND u.birthday = #{birthday}
</when>
<otherwise/>
</choose>
</where>
</select></code>4. selectKey for auto‑generated keys
<code><insert id="createStudentAutoKey" parameterType="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></code><code>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());</code>5. if tag for conditional clauses
<code><select id="getStudentList_if" resultMap="studentResultMap" parameterType="StudentEntity">
SELECT * FROM STUDENT_TBL ST
<where>
<if test="studentName != null">
ST.STUDENT_NAME LIKE CONCAT(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>
<if test="studentId != null and studentId != ''">
AND ST.STUDENT_ID = #{studentId}
</if>
</where>
</select></code><code>public void select_test_2_1() {
StudentEntity entity = new StudentEntity();
entity.setStudentName("");
entity.setStudentSex(1);
entity.setStudentBirthday(DateUtil.parse("1985-05-28"));
entity.setClassId("20000001");
List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity);
for (StudentEntity e : list) {
System.out.println(e);
}
}</code>6. if + where combination
<code><select id="getStudentList_whereIf" resultMap="studentResultMap" 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(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>
<if test="studentId != null and studentId != ''">
AND ST.STUDENT_ID = #{studentId}
</if>
</where>
</select></code>7. if + set for update statements
<code><update id="updateStudent_if_set" parameterType="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></code>8. trim to replace where / set
<code><select id="getStudentList_if_trim" resultMap="studentResultMap">
SELECT ST.* FROM STUDENT_TBL ST
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="studentName != null">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}), '%')
</if>
<if test="studentSex != null and studentSex != ''">
AND ST.STUDENT_SEX = #{studentSex}
</if>
...
</trim>
</select></code><code><update id="updateStudent_if_trim" parameterType="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>
...
</trim>
WHERE STUDENT_TBL.STUDENT_ID = #{studentId}
</update></code>9. foreach for IN clauses (array and list)
<code><select id="getStudentListByClassIds_foreach_array" resultMap="studentResultMap">
SELECT ... FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="array" item="classIds" open="(" separator="," close=")">
#{classIds}
</foreach>
</select></code><code>@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);
}
}</code><code><select id="getStudentListByClassIds_foreach_list" resultMap="studentResultMap">
SELECT ... FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="list" item="classIdList" open="(" separator="," close=")">
#{classIdList}
</foreach>
</select></code><code>@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);
}
}</code>SQL fragment reuse
<code><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></code>These patterns help you write concise, maintainable MyBatis mappings and avoid common pitfalls such as extra commas, misplaced AND/OR, and incorrect collection handling.
Written by
macrozheng
Dedicated to Java tech sharing and dissecting top open-source projects. Topics include Spring Boot, Spring Cloud, Docker, Kubernetes and more. Author’s GitHub project “mall” has 50K+ stars.
0 followers
Reader feedback
How this landed with the community
Rate this article
Was this worth your time?
Discussion
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.