动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素，大大精简了元素种类:
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
1.if
if元素用于解决条件SQL语句拼接问题,传统JDBC根据条件拼接SQL语句简直就是噩梦,if元素的出现解放了我们的双手。下面通过例子来介绍if元素
<!-- sql片段,用于元素复用 --><sql id="blogColumns">bid,title,`state`,content,`issUer`,createTime,readNum,tag</sql><select id="findBlogWithTitleLike" resultType="blog">select <include refid="blogColumns"/> from blogwhere createTime > '2020-01-01'<!-- 如果传入的title参数不为空,那么就会拼接if中的sql语句根据title模糊查询数据,if元素只有一个test属性且为必填,test属性用于条件表达式的判断,你可以test属性中使用and 或 or,and表示并且,or表示或者 --><if test="title!=null">and title like concat(#{title},'%')</if><!-- if中使用多个条件 --><!--<if test="title!=null and readNum!=null">and title like concat(#{title},'%') and readNum >= 10</if>--></select>
如果传入的title参数不为空,那么就会拼接if中的sql语句根据title模糊查询数据,if元素只有一个test属性且为必填,test属性用于条件表达式的判断,你可以test属性中使用and 或 or,and表示并且,or表示或者。
2.choose、when、otherwise
choose、when、otherwise元素用于多条件的判断,如果不想使用if元素你可以用choose、when、otherwise元素代替if元素,choose、when、otherwise很像Java中的switch,otherwise相当于switch的default,用于分支条件的判断。
<!-- 动态sql之choose、when、otherwise -->
<select id="findBlogWithChoose" parameterType="blog"
resultType="blog">
select <include refid="blogColumns"/> from blog where createTime > '2020-01-01'
<choose>
<when test="title !=null"> and title like concat(#{title},'%')</when>
<when test="title !=null and readNum!=null">
and title like concat(#{title},'%') and readNum > #{readNum}
</when>
<otherwise>
and `state`=#{state}
</otherwise>
</choose>
</select>
对应测试代码:
@Test
public void findBlogWithChoose(){
Blog blog=new Blog().setTitle("养猪").setReadNum(100);
List<Blog> blogList =
sqlSession.selectList("com.fly.mapper.BlogMapper.findBlogWithChoose",blog);
System.out.println(blogList);
}
3.trim、where、set
虽然if能臭名昭著的SQL拼接问题,但对于if条件情况仍然无力:
情况1:当if中的条件不满足
<select id="findBlog" resultType="blog">
select * from blog where
<if test="title!=null"> and title like concat(#{title},'%')</if>
</select>
例如上面的sql语句,如果if中的条件不满足时,最终的SQL语句如下,执行这段SQL语句会出现语法错误。
select * from blog where
情况2:if中第二个条件不满足时
<select id="findBlog" resultType="blog">
select * from blog where
<if test="state=1"> state=1 </if>
<if test="title!=null"> and title like concat(#{title},'%')</if>
</select>
最终的SQL语句为:
#语法错误
select * from blog where and title like concat(#{title},'%')
3.1 where
对于上面的问题我们可以使用where元素解决条件表达式拼接错误,where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。使用where元素修改如下:
<select id="findBlog" resultType="blog">
select * from blog
<where>
<if test="state=1">
state=1
</if>
<if test="title!=null"> and title like concat(#{title},'%')</if>
</where>
</select>
3.2 trim
trim元素的属性如下:
- prefix:在trim标签内sql语句加上前缀。
- suffix:在trim标签内sql语句加上后缀。
- prefixOverrides:表示自动覆盖trim元素中的第一个元素。|号表示或者的意思
- suffixOverrides:表示自动覆盖trim元素中的最后一个元素。|号表示或者的意思
使用trim定制where功能:
<select id="findBlogWithTrim" parameterType="blog"
resultType="blog">
select <include refid="blogColumns"/> from blog
<!-- prefix指定添加前缀的内容 suffix用于指定添加后缀的内容 -->
<trim prefix="where" suffix="state=1">
title like concat(#{title},'%') and
</trim>
</select>
<!-- 最终的sql语句是:select * from blog where title like concat(?,'%') and state=1 -->
trim元素prefixOverrides与suffixOverrides属性的使用:
<select id="findBlogWithTrimTest" resultType="blog">
select * from blog
<!--
prefixOverrides表示会自动剔除trim元素的指定内容为前缀的内容,and|or表示自动
覆盖第一个and或or
suffixOverrides表示自动剔除trim元素的指定内容为后缀的内容,suffixOverrides="or readNum>10000"
表示会剔除trim元素以"or readNum>10000"为后缀的内容。
-->
<trim prefix="where" suffixOverrides="or readNum>10000" prefixOverrides="and|or">
<if test="title!=null">
and title like concat(#{title},'%')
</if>
<if test="state!=null">
and `state`=#{state}
</if>
or readNum>10000
</trim>
</select>
<!-- 最终生成的sql语句:select * from blog where title like concat(?,'%') and `state`=? -->
3.3 set
set元素用于动态更新语句,set 元素可以用于动态包含需要更新的列，忽略其它不更新的列,一般用于update语句中。set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
set实现update语句修改(效果跟上面是一样的):
<update id="updateBlogWithSet" parameterType="blog">
update blog <set>
<if test="title!=null">
title=#{title},
</if>
<if test="readNum!=null">
readNum=#{readNum},
</if>
</set>
where bid=#{bid}
</update>
<!-- 生成sql:update blog SET title=?, readNum=? where bid=? -->
trim定制set元素功能:
<update id="updateBlogWithTrim" parameterType="blog">
update blog
<!-- suffixOverrides去除trim中后缀为,的内容 -->
<trim prefix="set" suffixOverrides=",">
<if test="title!=null">
title=#{title},
</if>
<if test="readNum!=null">
readNum=#{readNum},
</if>
</trim>
where bid=#{bid}
</update>
<!-- 生成sql:update blog SET title=?, readNum=? where bid=? -->
4.foreach
foreach元素用于对集合进行遍历（尤其是在构建 IN 条件语句的时候）。foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！
提示 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。
foreach元素的属性如下:
- item：集合中元素迭代时的别名，该参数为必选。
- index：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选
- open：foreach代码的开始符号，一般是(和close=”)”合用。常用在in(),values()时。该参数可选
- separator：元素之间的分隔符，例如在in()的时候，separator=”,”会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
- close: foreach代码的关闭符号，一般是)和open=”(“合用。常用在in(),values()时。该参数可选。
- collection: 要做foreach的对象，作为入参时，List对象默认用”list”代替作为键，数组对象有”array”代替作为键，Map对象没有默认的键。当然在作为入参时可以使用@Param(“keyName”)来设置键，设置keyName后，list,array将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：如果User有属性List ids。入参是User对象，那么这个collection = “ids”.如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = “ids.id”
foreach接收List或数组实现in()功能
<select id="findBlogWithList" resultType="blog">
select * from blog
where bid in
<foreach collection="list" item="item" index="index"
open="(" close=")" separator=",">
#{item}
</foreach>
</select>
<!-- 生成sql:select * from blog where bid in ( ? , ? ) -->
对应test代码:
@Test
public void findBlogWithList(){
List<Integer> list=new ArrayList<>();
list.add(1);
list.add(2);
List<Blog> blogList =sqlSession.selectList("com.fly.mapper.BlogMapper.findBlogWithList",list);
System.out.println(blogList);
}
5.bind
bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
6.多数据支持
首先你要在mybatis全局配置文件配置数据库厂商,假设配置如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 多数据库支持 -->
<databaseIdProvider type="DB_VENDOR">
<property name="mysql" value="mysql"/>
<property name="SQL Server" value="sqlserver"/>
<property name="DB2" value="db2"/>
<property name="Oracle" value="oracle" />
</databaseIdProvider>
</configuration>
如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
7.Script
要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。
@Update({"<script>",
"update Author",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" <if test='bio != null'>bio=#{bio}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);
若有收获，就点个赞吧
0 人点赞
