用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还提供了choosewhenotherwisetrimwheresetforeachbind等标签。

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

<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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。