原理:
MyBatis使用功能强大的OGNL表达式创建动态SQL表达式,常用的表达有:if,choose(when,otherwise),trim(where,set),foreach;下面分别介绍这四个的用法及可插拔脚本语言:
if:
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
choose:
<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>
choose类似java中的swith语句,谁先匹配用哪个when语句,否则使用otherwise语句
trim:
先看一个反例:
<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>
</select>
如果以上条件没有一个能匹配:SQL 语句为:
SELECT * FROM BLOG WHERE
如果仅仅第二个条件匹配,SQL语句为:
SELECT * FROM BLOG WHERE AND title like ‘someTitle’
解决方案有两种:
第一种解决方案:
<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>
该方案中where元素只会在至少有一个子元素的条件返回SQL子句的情况下才会插入where子句,而且,若语句的开头为and或or,where元素会将他们去除
第二种方案:
使用trim定制where元素功能,和where元素等价:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<trim prefix="where" prefixOverrides="and | or">
<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>
foreach:
foreach元素功能强大,允许指定集合,声明可以在元素体内使用的集合项和索引变量,允许指定开头与结尾的字符串以及在迭代结果之间放置分隔符。用法如下:
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
动态SQL中的可插拔脚本语言:
MyBatis从3.2支持可插拔脚本语言,从而允许插入一种脚本语言驱动,并基于这种语言编写动态SQL查询语句,流程为:
1.实现LanguageDriver接口:
public interface LanguageDriver {
ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
}
2.配置mybatis-config.xml文件设置默认语言:
<typeAliases>
<typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
</typeAliases>
<settings>
<setting name="defaultScriptingLanguage" value="myLanguage"/>
</settings>
3.指定特定语言,使用lang属性:
<select id="selectBlog" lang="myLanguage">
SELECT * FROM BLOG
</select>
4.如果使用的是映射器接口类,在抽象方法上加上@Lang注解即可:
public interface Mapper {
@Lang(MyLanguageDriver.class)
@Select("SELECT * FROM BLOG")
List<Blog> selectBlog();
}