Comprehensive Guide to MyBatis Dynamic SQL and XML Mapping Tags

1. What is MyBatis Dynamic SQL

Dynamic SQL is a powerful feature of MyBatis that allows SQL statements to be built at runtime based on user input or external conditions, avoiding manual string concatenation and reducing errors such as missing spaces or trailing commas.

2. MyBatis Dynamic Tags

2.1 if tag (conditional judgment)

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user
    <if test="username != null"> AND username = #{username}</if>
    <if test="sex != null"> AND sex = #{sex}</if>
</select>

2.2 where + if tag

The <where> tag automatically adds the WHERE keyword and removes leading AND / OR when necessary.

<select id="findQuery" resultType="Student">
    SELECT * FROM student
    <where>
        <if test="name != null"> AND name LIKE #{name}</if>
        <if test="url != null"> AND url LIKE #{url}</if>
    </where>
</select>

2.3 set tag

Used in UPDATE statements to set column values conditionally.

<update id="upd" parameterType="com.ys.po.User">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="sex != null">sex = #{sex},</if>
    </set>
    WHERE id = #{id}
</update>

2.4 choose (when, otherwise) tag

Works like a switch statement, selecting the first matching condition.

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

2.5 trim tag

Formats SQL fragments, removing unwanted leading or trailing characters.

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

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

2.7 sql and include tags

Define reusable SQL fragments with <sql> and reference them using <include> .

<sql id="selectBase">SELECT * FROM student</sql>
<select id="findById" resultType="Student">
    <include refid="selectBase"/> WHERE sid = #{sid}
</select>

3. Complete Mapper Example and Test Class

The article provides a full mapper.xml file that demonstrates the use of all tags above, as well as a JUnit test class showing how to obtain a SqlSession , call mapper methods, and perform CRUD operations.

4. Association Queries

4.1 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>
<select id="find1" resultMap="myStudent1">
    SELECT * FROM student1 s LEFT JOIN teacher t ON s.sid = t.sid
</select>

4.2 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>
<select id="find2" resultMap="myTeacher">
    SELECT * FROM teacher t RIGHT JOIN student1 s ON t.sid = s.sid
</select>

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

These examples illustrate how MyBatis can map complex relational data structures directly to Java objects using the dynamic tags described earlier.