Master MyBatis Dynamic SQL: Tags, Queries, and Real‑World Examples

1. What is MyBatis Dynamic SQL?

Dynamic SQL is a powerful feature of MyBatis that allows SQL statements to be built programmatically based on conditions, avoiding manual string concatenation and reducing errors.

It enables flexible query construction but may have performance overhead and security risks if not used carefully.

1.1 What does MyBatis Dynamic SQL do?

It lets developers write dynamic SQL inside XML mapper files using tags that support conditional logic and string assembly.

1.2 The 9 dynamic‑SQL tags

if : single‑condition branch.

choose (when, otherwise) : multi‑condition switch‑like logic.

trim, where : helper tags for cleaning up extra commas or AND/OR keywords.

foreach : iterate over collections for IN clauses.

bind : bind additional parameters.

2. MyBatis tags

if tag

Works like Java if, reduces manual SQL concatenation.

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    SELECT * FROM user WHERE username=#{username} AND sex=#{sex}
</select>

Usage with test attribute:

<if test="condition">SQL fragment</if>

where + if

Combines where with if to build conditional queries.

Note: <if> failures only remove the leading AND/OR inside <where> ; the trailing AND remains.

set tag

Used in UPDATE statements to set columns conditionally.

<update id="upd">
    UPDATE student
    <set>
        <if test="sname != null">sname=#{sname},</if>
        <if test="spwd != null">spwd=#{spwd},</if>
        <if test="sex != null">sex=#{sex},</if>
        <if test="phone != null">phone=#{phone}</if>
    </set>
    WHERE sid=#{sid}
</update>

choose (when, otherwise)

Acts like a switch statement to select one of several possible conditions.

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != '' and id != null">id=#{id}</when>
            <when test="username != '' and username != null">AND username=#{username}</when>
            <otherwise>AND sex=#{sex}</otherwise>
        </choose>
    </where>
</select>

trim tag

Formats SQL fragments, removing unwanted prefixes or suffixes.

<trim prefix="where" prefixOverrides="and | or">
    <if test="username != null">AND username=#{username}</if>
    <if test="sex != null">AND sex=#{sex}</if>
</trim>

foreach tag

Iterates over collections for batch operations such as IN clauses.

// Batch query
<select id="findAll" resultType="Student" parameterType="Integer">
    SELECT * FROM student WHERE sid IN
    <foreach item="ids" collection="array" open="(" separator="," close=")">
        #{ids}
    </foreach>
</select>

3. MyBatis association queries

One‑to‑many

<resultMap id="myStudent1" type="student1">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <collection property="list" ofType="teacher">
        <id property="tid" column="tid"/>
        <result property="tname" column="tname"/>
    </collection>
</resultMap>

Many‑to‑one

<resultMap id="myTeacher" type="teacher">
    <id property="tid" column="tid"/>
    <association property="student1" javaType="Student1">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
    </association>
</resultMap>

Many‑to‑many

<select id="find3" resultMap="myStudent1">
    SELECT * FROM student1 s
    LEFT JOIN relevance r ON s.sid=r.sid
    LEFT JOIN teacher t ON r.tid=t.tid
</select>