Mybatis Doc Dynamic SQL

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities.

if

The most common thing to do in dynamic SQL is conditionally include a part of a where clause.

<select id="findActiveBlogWithTitleLike"  resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

choose, when, otherwise

Sometimes we don’t want all of the conditionals to apply, instead we want to choose only one case among many options.

<select id="findActiveBlogLike"   resultType="Blog">
 SELECT * FROM BLOG WHERE state = ‘ACTIVE’
 <choose>
   <when test="title != null">
     AND title like #{title}
   </when>
   <when test="author != null and author.name != null">
     AND author_name like #{author.name}
   </when>
   <otherwise>
     AND featured = 1
   </otherwise>
 </choose>
</select>

trim, where, set

where, With one simple change, everything works fine.

<select id="findActiveBlogLike"    resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

you can customize it by defining your own trim element. For example, the trim equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

There is a similar solution for dynamic update statements called set.

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

If you’re curious about what the equivalent trim element would look like, here it is:

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

foreach

Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition.

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

script

For using dynamic SQL in annotated mapper class, script element can be used.

    @Update({"<script>",
      "update Author",
      "  <set>",
      "    <if test='username != null'>username=#{username},</if>",
      "    <if test='password != null'>password=#{password},</if>",
      "    <if test='email != null'>email=#{email},</if>",
      "    <if test='bio != null'>bio=#{bio}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
    void updateAuthorValues(Author author);

bind

The bind element lets you create a variable out of an OGNL expression and bind it to the context.

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

Multi-db vendor support

If a databaseIdProvider was configured a "_databaseId" variable is available for dynamic code, so you can build different statements depending on database vendor.

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

Pluggable Scripting Languages For Dynamic SQL

MyBatis supports pluggable scripting languages, so you can plug a language driver and use that language to write your dynamic SQL queries.
You can plug a language by implementing the following interface:

public interface LanguageDriver {
  ......
}