Dynamic SQL

Mapper XML supports Dynamic SQL via if, choose, foreach, etc., using the familiar MyBatis style.

• <if>: conditional fragments.
• <choose> / <when> / <otherwise>: multi-branch condition selection.
• <trim> / <where> / <set>: help avoid malformed SQL when generating specific statements.
• <foreach>: iterate collections.
• <bind>: create variables via OGNL expressions and bind them to the context.

if tag

<if> conditionally appends SQL.

<select id="queryUser">
    select * from users
    where state = 'ACTIVE'
    <if test="age != null">
        and age = #{age}
    </if>
</select>

Simple if can be replaced by rules

<select id="queryUser">
    select * from users
    where state = 'ACTIVE'
    @{and, age = #{age}}
</select>

• See AND rule

Attributes

Property	Description
test	Required OGNL expression returning boolean; when true the SQL inside is rendered.

choose (when, otherwise) tags

Renders the first matching <when> branch; if none match, <otherwise> is used.

<select id="queryUser">
    select * from users
    where state = 'ACTIVE'
    <choose>
        <when test="title != null">and title = #{title}</when>
        <when test="content != null">and content = #{content}</when>
        <otherwise>and owner = 'owner1'</otherwise>
    </choose>
</select>

Attributes

<choose> and <otherwise> have no attributes. <when> attributes:

Property	Description
test	Required OGNL expression returning boolean; when true the SQL inside is rendered.

trim (where, set) tags

<trim>, <where>, and <set> help avoid malformed SQL. Consider this example:

<select id="queryUser">
    select * from users
    where
    <if test="age != null">
        and age = #{age}
    </if>
</select>

If age is null, the generated SQL becomes:

select * from users
where

That is invalid SQL; <where> fixes this.

where tag (example 1)

When <if> produces content, <where> automatically adds the where keyword.

<select id="queryUser">
    select * from users
    <where>
        <if test="age != null">
            age = #{age}
        </if>
    </where>
</select>

Depending on the value of age, the generated statement will be one of the following:

select * from users;
select * from users where age = ?

where tag (example 2)

With multiple conditions, <where> also trims leading and/or.

<select id="queryUser">
    select * from users
    <where>
        <if test="age != null">
            age = #{age}
        </if>
        <if test="name != null">
            and name = #{name}
        </if>
    </where>
</select>

set tag

Use <set> for dynamic UPDATE columns:

<update id="queryUser">
    update users
    <set>
        <if test="age != null">age=#{age},</if>
        <if test="name != null">name=#{name},</if>
    </set>
    where id=#{id}
</update>

<set> only trims separators; it does not supply missing update columns. Reject the update before calling the mapper if all fields are null.

trim tag

<trim> can replace <where> or <set> for special cases.

Use trim tag to replace where tag

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

Use trim tag to replace set tag

<trim prefix="set" suffixOverrides=",">
    ...
</trim>

trim attributes

Property	Description
prefix	Optional Prefix to add when rendering SQL.
suffix	Optional Suffix to add when rendering SQL.
prefixOverrides	Optional Trim these prefixes from the generated SQL (use `
suffixOverrides	Optional Trim these suffixes from the generated SQL (use `

foreach tag

<foreach> is commonly used for IN clauses or value lists.

<select id="queryByIds">
  select * from users
  where id in
  <foreach item="item" index="index" collection="list"
           open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

Simple foreach can be replaced by rules

<select id="queryByIds">
    select * from users where @{in,id in :list}
</select>

• See IN rule

foreach attributes

Property	Description
collection	Required Data to iterate (array, collection, etc.).
item	Required Variable name for the current element; accessible inside the tag.
open	Optional Text prepended before iteration output.
close	Optional Text appended after iteration output.
separator	Optional Separator between items.

bind tag

<bind> creates a variable via OGNL and binds it to the context.

<select id="queryByLike">
    <bind name="pattern" value="'%' + title + '%'" />
    select * from users
    where title like #{pattern}
</select>

bind attributes

Property	Description
name	Required Name of the variable to create.
value	Required OGNL expression whose result is bound to name.