MyBatis Dynamic SQL: The Tags That Actually Matter
if, where, set, foreach, and choose cover almost every real query I write. A field guide with the patterns I reach for most.
Dynamic SQL is how MyBatis lets you build safe, maintainable queries without manual string concatenation. Eight tags cover almost everything I actually write in production mapper XML.
| Tag | Use |
|---|---|
| <if> | Add a condition only when a value exists |
| <choose>/<when>/<otherwise> | Branch logic (if-else) |
| <where> | Auto-adds WHERE and strips a leading AND/OR |
| <set> | Auto-builds SET for update statements |
| <foreach> | Iterate a list/map for IN or batch SQL |
| <trim> | Custom prefix/suffix cleanup |
| <sql> + <include> | Reusable SQL fragment |
| <bind> | Runtime variable, often for LIKE |
<if> inside <where>
<select id="searchUsers" resultMap="UserMap">
SELECT * FROM users
<where>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="status != null">
AND status = #{status}
</if>
</where>
</select><choose> for controlled sorting
ORDER BY
<choose>
<when test="sortBy == 'username'">username</when>
<when test="sortBy == 'createdAt'">created_at</when>
<otherwise>id</otherwise>
</choose>
<choose>
<when test="sortOrder == 'ASC'">ASC</when>
<otherwise>DESC</otherwise>
</choose>Never string-concat a sort column
Sort/order parameters come straight from query strings. Whitelisting them through <choose> instead of interpolating them directly is what keeps a "sortBy" parameter from becoming a SQL injection vector.
<foreach> for IN-lists and batch inserts
<select id="findByIds" resultMap="UserMap">
SELECT * FROM users
WHERE id IN
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<insert id="batchInsert">
INSERT INTO users (username, email, status) VALUES
<foreach collection="users" item="user" separator=",">
(#{user.username}, #{user.email}, #{user.status})
</foreach>
</insert><set> for partial updates
<update id="dynamicUpdate">
UPDATE users
<set>
<if test="username != null and username != ''">username = #{username},</if>
<if test="email != null and email != ''">email = #{email},</if>
<if test="status != null">status = #{status},</if>
</set>
WHERE id = #{id}
</update><set> does the same trailing-comma cleanup for UPDATE that <where> does for leading AND/OR โ without it, a partial update with only one changed field would end in a dangling comma and fail to compile.
Reusable fragments
<sql id="userColumns">id, username, email, status, created_at</sql>
<select id="findAll" resultMap="UserMap">
SELECT <include refid="userColumns"/>
FROM users
ORDER BY id DESC
</select>Every one of these patterns also has an index-usage cost โ see [[mapper-xml-query-review-checklist]] for the review checklist I run against every mapper change before it ships.
Prompt Engineering for Backend Developers: The 3-Part Rule
NextThe Checklist I Run on Every MyBatis Query Before It Ships
Related articles
The Checklist I Run on Every MyBatis Query Before It Ships
The database will run almost any query โ slowly. A five-step self-review that catches index misses and type mismatches before they hit production.
Thymeleaf for Spring Boot: From th:text to Fragments
A practical path through Thymeleaf โ basic attribute binding, object selection, and the fragment pattern that kills copy-pasted navbars.
Inversion of Control & Dependency Injection: The Bean I Kept Recreating
Six classes, one Person object, one `new Person(...)` copy-pasted six times. That's the problem IoC actually solves โ not an abstract principle, a concrete duplication bug.