MyBatis Dynamic SQL

Dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL. I f you have experience with JDBC or other similar frameworks, you can see how painful it is to stitch SQL statements according to different conditions. W hen stitching, make sure you don't forget the necessary spaces, and be careful to save the comma at the end of the list of lists. The feature of dynamic SQL can be used to get rid of this pain completely.

Typically, using dynamic SQL cannot be a separate part, and MyBatis certainly uses a powerful dynamic SQL language to improve this situation, which can be used in any SQL mapping statement.

Dynamic SQL elements are similar to text processors that use JSTL or other similar XML-based text processors. I n previous versions of MyBatis, there were many elements to understand. M yBatis 3 has greatly improved them, and now uses less than half of the original elements. MyBatis uses powerful OGNL-based expressions to eliminate other elements.

• if
• choose (when, otherwise)
• trim (where, set)
• Foreach

if

What dynamic SQL usually does is conditionally include part of the where clause. Like what:

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

This statement provides an optional text-find type feature. If "title" is not passed in, all LOG in the "ACTIVE" state is returned, wherein, if "title" is passed in, the BLOG result of the fuzzy search for "title" content is returned (in this case, careful readers will find that the parameter values can contain some masks or wildcards).

What if you want to search optionally through the "title" and "author" criteria? First, change the name of the statement to make it more meaningful;

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

choose, when, otherwise

Sometimes we don't want to use all the conditional statements, we just want to choose one or two of them. In this case, MyBatis provides the select element, which is a bit like the switch statement in Java.

Or the above example, but this time it becomes to provide "title" to look up by "title", to provide "author" to look up, and if neither is provided, to return all eligible BLOGs (the reality may be that the administrator selects the BLOG list according to a certain strategy, rather than returning a large number of meaningless random results).

<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

The previous few examples have appropriately solved a notorious dynamic SQL problem. Now consider going back to the "if" example, and this time we'll set "ACTIVE s 1" as a dynamic condition to see what happens.

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

What if none of these conditions match? Eventually this SQL will be like this:

SELECT * FROM BLOG
WHERE

This causes the query to fail. W hat if only the second condition matches? This SQL ends up like this:

SELECT * FROM BLOG
WHERE
AND title like 'someTitle'

The query also fails. This problem cannot be solved simply by conditional sentences, and if you have ever been forced to write like this, you probably never want to write like this again.

MyBatis has a simple treatment that works in 90% of cases. W here it doesn't work, you can customize the processing to make it work. A simple modification can get the desired effect:

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

Where element knows to insert the "WHERE" clause only if more than one if condition has a value. Also, if the last content begins with "AND" or "OR," the where elements also know how to remove them.

If the where element doesn't play as normal, we can still customize the features we want by customizing the trim element. For example, a custom trim element equivalent to the where element is:

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

The prefixOverrides property ignores the sequence of text separated by the pipeline (note that spaces in this example are also necessary). The result is that everything specified in the prefixOverrides property is removed and inserted into the prefix property.

A similar solution for dynamically updating statements is called set. S et elements can be used to dynamically contain columns that need to be updated, while deverting others. Like what:

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

Here, the set element dynamically pre-sets the keyword and also eliminates unrelated commas, which are likely to be left after the resulting assignment statement after the conditional statement is used.

If you're interested in what an equivalent custom trim element looks like, it should be its true face:

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

Note that what we ignore here is the value in the suffix, and once again the value in the prefix is attached.

Foreach

Another common requirement for dynamic SQL is the need to traverse a collection, usually when building IN conditional statements. Like what:

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

The foreach element is very powerful and allows you to specify a collection that declares collection items and index variables that can be used in the element body. I t also allows you to specify open-close matching strings and place separators in the middle of iterations. This element is very intelligent, so it doesn't accidentally attach extra separators.

Note You can pass a List instance or array as an argument object to MyBatis, and when you do, MyBatis automatically wraps it in a Map with the name key. The List instance will use "list" as the key, while the array instance will have the key "array".

So far we've completed a discussion involving XML profiles and XML mapping files. The next section explores the Java API in detail so that you can get the most out of the mapping you've created.

bind

bind element can create a variable from an OGNL expression and bind it to the context. Like what:

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

Multi-db vendor support

A databaseIdProvider with _databaseId variable is available for dynamic code so that specific statements can be built against different database vendors. For example, here's an example:

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

The scripting language that can be plugged in dynamic SQL

MyBatis supports pluggable scripting languages starting with 3.2, so you can write dynamic SQL queries based on that language after inserting a language driver.

You can insert a language by implementing the following interface:

    public interface LanguageDriver {
      ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
      SqlSource createSqlSource(Configuration configuration, XNode script, Class parameterType);
      SqlSource createSqlSource(Configuration configuration, String script, Class parameterType);
    }

Once you have a custom language driver, you can set it as the default language .xml mybatis-config file:

<typeAliases>
  <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
</typeAliases>
<settings>
  <setting name="defaultScriptingLanguage" value="myLanguage"/>
</settings>

In addition to setting the default language, you can also specify a specific language for a particular statement, which can be done by lang property:

<select id="selectBlog" lang="myLanguage">
  SELECT * FROM BLOG
</select>

Or add an annotation to the map @Lang to complete:

    public interface Mapper {
      @Lang(MyLanguageDriver.class)
      @Select("SELECT * FROM BLOG")
      List selectBlog();
    }

Note You can use Apache Velocity as a dynamic language, please refer to the MyBatis-Velocity project for more details.

All the xml tags you see earlier are provided by the default MyBatis language, which is driven by xml Language Drive org.apache.ibatis.scripting.xmltags.XmlLanguageDriver