Write MyBatis dynamic SQL with groovy templates

Source: Internet
Author: User
Tags cdata


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.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.