Understanding MyBatis Dynamic SQL and Tag Usage

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

The article introduces the nine MyBatis dynamic tags— if , where , trim , choose , foreach , sql , include , resultMap , and association/collection —and explains their purposes and typical usage patterns.

if tag : Used for conditional SQL fragments. Example:

<if test="username != null"> AND username = #{username} </if>

where + if : Combines where with multiple if statements to generate clean WHERE clauses, automatically removing leading AND / OR .

<where>
  <if test="name != null"> AND name LIKE #{name} </if>
  <if test="url != null"> AND url LIKE #{url} </if>
</where>

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

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

choose (when, otherwise) : Works like a switch statement to select the first matching condition. Example:

<choose>
  <when test="id != null"> id = #{id} </when>
  <when test="username != null"> username = #{username} </when>
  <otherwise> sex = #{sex} </otherwise>
</choose>

foreach tag : Iterates over collections to build IN clauses or batch operations. Example for 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>

sql and include tags : Define reusable SQL fragments with <sql id="..."> and reference them via <include refid="..."/> , promoting DRY principles.

<sql id="selectvp"> SELECT * FROM student </sql>
<select id="find" resultType="Student">
  <include refid="selectvp"/>
</select>

resultMap, association, collection : Map query results to Java objects, supporting one‑to‑many, many‑to‑one, and many‑to‑many relationships. Example of 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>

The article also provides a complete XML mapper example, a Java test class demonstrating CRUD operations, and notes on security considerations such as SQL injection risks when using dynamic SQL.

In summary, by mastering these MyBatis tags and patterns, developers can write flexible, maintainable, and safe SQL statements within their Java applications.