MyBatis Dynamic SQL and Tag Usage Guide

MyBatis provides powerful dynamic SQL features that help developers avoid manual string concatenation in JDBC or similar frameworks. Dynamic SQL can be built at runtime based on user input or external conditions, improving flexibility but potentially affecting performance and security if misused.

What dynamic SQL does : It allows conditional logic and dynamic assembly of SQL statements directly in XML mapper files using tags such as <if> , <where> , <trim> , <choose> , <foreach> , etc.

Key MyBatis dynamic tags (9 types) :

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

<where>...</where>

<trim prefix="..." prefixOverrides="and|or" suffixOverrides=",">...</trim>

<choose><when test="...">...</when><otherwise>...</otherwise></choose>

<set>...</set>

<foreach item="item" collection="list" open="(" separator="," close=")">...</foreach>

<sql id="fragment">...</sql>

<include refid="fragment"/>

<bind name="..." value="..."/>

Example: Simple if tag

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

Combining where and if

<select id="findQuery" resultType="Student">
    <include refid="selectvp"/>
    <where>
        <if test="sacc != null"> sacc like concat('%', #{sacc}, '%') </if>
        <if test="sname != null"> AND sname like concat('%', #{sname}, '%') </if>
        <if test="sex != null"> AND sex = #{sex} </if>
        <if test="phone != null"> AND phone = #{phone} </if>
    </where>
</select>

Using trim to clean up leading AND/OR

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

Batch operations with foreach

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

Association and collection mapping for one‑to‑many and many‑to‑one queries

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

Include reusable SQL fragments

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

The article also provides a link to a free Spring Boot project (https://github.com/javastacks/spring-boot-best-practice) for practical testing.