Master MyBatis Dynamic SQL: Tags, Conditions, and Advanced Queries

1 MyBatis Dynamic SQL Features

1. What is MyBatis dynamic SQL?

MyBatis dynamic SQL allows writing SQL fragments in XML mapper files using tags, enabling conditional logic and automatic concatenation, avoiding manual string building.

2. Nine dynamic SQL tags

3. Execution principle

OGNL evaluates expressions from the parameter object, and based on the result the SQL is assembled at runtime.

2 MyBatis Tags

1. if tag

Similar to Java if, used to conditionally include SQL fragments.

<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 statement</if>

2. where + if

Combines <where> and <if> to build flexible queries; note that when <if> fails, <where> only removes the leading AND/OR of the first condition.

<select id="selectAllWebsite" resultMap="myResult">
    select id,name,url from website
    where 1=1
    <if test="name != null">
        AND name like #{name}
    </if>
    <if test="url != null">
        AND url like #{url}
    </if>
</select>

Note, after an <if> failure the <where> keyword only removes the leading AND/OR of the first condition, not the trailing one.

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

4. choose (when, otherwise)

Acts like a switch; only one of the when conditions is applied.

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

5. trim tag

Formats SQL fragments, can replace where or set, removing unwanted prefixes or suffixes.

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username != null">and username=#{username}</if>
        <if test="sex != null">and sex=#{sex}</if>
    </trim>
</select>

Similarly for set:

<update id="updateUserById" parameterType="com.ys.po.User">
    update user u
    <trim prefix="set" suffixOverrides=",">
        <if test="username != null and username != ''">u.username = #{username},</if>
        <if test="sex != null and sex != ''">u.sex = #{sex},</if>
    </trim>
    where sid=#{sid}
</update>

6. foreach tag

Iterates over collections to build IN clauses or batch statements.

// batch query
<select id="findAll" resultType="Student" parameterType="Integer">
    <include refid="selectvp"/> WHERE sid in
    <foreach item="ids" collection="array" open="(" separator="," close=")">
        #{ids}
    </foreach>
</select>
// batch delete
<delete id="del" parameterType="Integer">
    delete from student where sid in
    <foreach item="ids" collection="array" open="(" separator="," close=")">
        #{ids}
    </foreach>
</delete>

7. sql and include tags

<sql>

defines reusable fragments; <include> inserts them.

<sql id="selectvp">
    select * from student
</sql>

<select id="findbyid" resultType="student">
    <include refid="selectvp"/>
    WHERE 1=1
    <if test="sid != null">AND sid like #{sid}</if>
</select>

3 MyBatis Association Queries

1. One‑to‑many

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

<select id="find1" resultMap="myStudent1">
    select * from student1 s left join teacher t on s.sid=t.sid
</select>

2. Many‑to‑one

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

<select id="find2" resultMap="myTeacher">
    select * from teacher t right join student1 s on t.sid=s.sid
</select>

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