Write MyBatis dynamic SQL with groovy templates

MyBatis Dynamic SQL Introduction

MyBatis has a powerful feature, dynamic SQL. With this function, the SQL statements defined in the Mapper do not have to be static, but can be dynamically adjusted according to the parameters passed in. Here is an example of an if statement in the official MyBatis document:

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

In addition to the if label, MyBatis also provides choose, when,otherwise,trim,where, set,foreach,bind , and other tags.

Groovy Templates

The XML tags provided for simple dynamic sql,mybatis are sufficient, and the XML written out is not too ugly. But for complex dynamic sql,xml tags it seems to be weak. After all, XML is better suited for describing data than if-else logic. Let's take a look at how to write dynamic SQL with a more powerful groovy template. I'll turn the dynamic SQL example one by one in MyBatis Official document into groovy template form.

If

Xml

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

Groovy

<select id="findActiveBlogLike" resultType="Blog">
<![CDATA[
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
    <% if (param.title != null) { %>
        AND title like #{title}
    <% } %>
    <% if (param.author != null && param.author.name != null) { %>
        AND author_name like #{author.name}
    <% } %>
]]>
</select>

Choose-when-otherwise

Xml

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

Groovy

<select id="findActiveBlogLike2" resultType="Blog">
<![CDATA[
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
    <% if (param.title != null) { %>
        AND title like #{title}
    <% } else if (param.author != null && param.author.name != null) { %>
        AND author_name like #{author.name}
    <% } else {%>
        AND featured = 1
    <% } %>
]]>
</select>

Trim,where,set

Xml

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

Groovy

<select id="findActiveBlogLike3" resultType="Blog">
<![CDATA[
    SELECT * FROM BLOG
    <%
        def hasWhere = false
        if (param.state != 0) {
            print 'WHERE state = #{state}'
            hasWhere = true
        }
        if (param.title != null) {
            print "${hasWhere ? 'AND' : 'WHERE'} title like #{title}"
            hasWhere = true
        }
        if (param.author != null && param.author.name != null) {
            print "${hasWhere ? 'AND' : 'WHERE'} author_name like #{author.name}"
        }
    %>
]]>
</select>

Foreach

Xml

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

Groovy

<select id="selectPostIn" resultType="Post">
<![CDATA[
    SELECT * FROM POST P WHERE ID in 
    <%= "(${param.join(',')})" %>
]]>
</select>

Bind

Xml

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

Groovy

<select id="selectBlogsLike" resultType="Blog">
<![CDATA[
    <% pattern = "%${param.title}%" %>
    SELECT * FROM BLOG
    WHERE title LIKE #{pattern}
]]>
</select>

Implementation principle

The MyBatis framework allows you to define plug-ins and write dynamic SQL in a custom scripting language. You just have to implement the Languagedriver interface and modify the configuration. You can set the custom languagedriver as the default, as follows:

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

You can also set the statement individually, as follows:

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

Groovytemplatelanguagedriver

Groovytemplatelanguagedriver implements the Languagedriver interface, which is shown in the GitHub project.