用Groovy模板写MyBatis动态SQL

MyBatis动态SQL简介

MyBatis有个强大的功能，动态SQL。有了这个功能，定义在Mapper里的SQL语句，就不必是静止不变的了，而是可以根据传入的参数，动态调整。下面是MyBatis官方文档里的一个if语句的例子：

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

除了if标签以外，MyBatis还提供了choose，when，otherwise，trim，where，set，foreach，bind等标签。

Groovy模板

对于简单的动态SQL，MyBatis提供的XML标签足够用了，写出来的XML也不至于太难看。但是对于复杂的动态SQL，XML标签就显得心有余而力不足。毕竟，XML更适合描述数据，而不是if-else等逻辑。下面我们就来看看，如何用更加强大的Groovy模板来写动态SQL。我会将MyBatis官方文档中的动态SQL例子一一转为Groovy模板形式。

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>

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

实现原理

MyBatis框架允许你定义插件，用自定义的脚本语言写动态SQL。你只要实现LanguageDriver接口，并修改配置就可以了。可以将自定义LanguageDriver设置为默认，如下所示：

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

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

GroovyTemplateLanguageDriver

GroovyTemplateLanguageDriver实现了LanguageDriver接口，代码见github项目。

用Groovy模板写MyBatis动态SQL,古老的榕树,5-wow.com