用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模板形式。 ifxml <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-otherwisexml <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,setxml <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> foreachxml <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> bindxml <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> GroovyTemplateLanguageDriverGroovyTemplateLanguageDriver实现了LanguageDriver接口,代码见github项目。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- django.db.utils.NotSupportedError: Renaming the 'ap
- Delphi TO C++
- lua loadlib函数 attempt to call global 'loadlib
- An Errors/BindingResult argument is expected to be decl
- lua常用论坛
- Go语言学习笔记 - 环境搭建
- Delphi 与 DirectX 之 DelphiX(67): TDIB.DoSmoothRotate()
- delphi java socket 对象传输
- delphi – ‘工作,请等待’屏幕与线程?
- Groovy:如何从groovy脚本中的方法设置属性/字段/ def?