Understanding MyBatis Dynamic SQL and XML Mapping Tags

Dynamic SQL is one of MyBatis's powerful features that allows developers to construct SQL statements dynamically based on conditions, avoiding manual string concatenation and reducing errors such as missing spaces or trailing commas.

1 MyBatis Dynamic SQL Features

1.1 What does MyBatis dynamic SQL do?

It enables writing dynamic SQL inside XML mapper files using tags to perform logical judgments and dynamic concatenation.

1.2 The 9 dynamic SQL tags

Includes <if> , <where> , <set> , <choose> , <when> , <otherwise> , <trim> , <foreach> , and <include> .

1.3 Execution principle

MyBatis uses OGNL to evaluate expressions from the parameter object and assembles the final SQL accordingly.

2 MyBatis Tags

2.1 <if> tag (conditional judgment)

Similar to Java's if statement; it can greatly reduce manual SQL concatenation.

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user where username=#{username} and sex=#{sex}
</select>

Usage example with test attribute:

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

2.2 Combining <where> and <if>

Allows flexible fuzzy queries; if no parameters are provided, all records are returned.

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

2.3 <set> tag

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

2.4 <choose> (when, otherwise) tag

Acts like a switch statement, selecting the first satisfied condition.

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

2.5 <trim> tag

Formats SQL fragments by adding prefixes/suffixes and removing unwanted leading or trailing keywords.

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

2.6 <foreach> tag

Iterates over collections to build IN clauses or batch statements.

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

2.7 <sql> and <include> tags

Define reusable SQL fragments and include them where needed.

<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

3.1 One-to-Many

Define a resultMap with a collection element to map a list of child objects.

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

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

3.2 Many-to-One

Use association inside a resultMap to embed a parent object.

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

<select id="find2" resultMap="myTeacher">
    SELECT * FROM teacher t RIGHT JOIN student1 s ON t.sid=s.sid
</select>

3.3 Many-to-Many

Combine multiple joins and reuse the one-to-many resultMap to retrieve nested collections.

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

At the end of the article, the author encourages readers to share the content, join a community group, and provides several promotional links to resources such as IDE activation codes, ERP systems, workflow projects, OA systems, and food delivery demos.