动态 SQL
- 动态 SQL 是 MyBatis 的强大特性之一
- 使用动态sql可以减少在java代码层面进行的sql语句的拼接,可以彻底摆脱这种痛苦。
环境搭建
- 下面讲解的所有测试例子 ,我都使用下面这个blog类型进行测试
public class Blog {
private String id;
private String title;
private String author;
private Timestamp createTime;
private int views;
}
where
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
- 简单说就是where标签会自动判断后面有没有sql语句,如果没有, 就将这个where省去了, 如果后面有sql语句就不会省略where关键字, 而且如果后面有and或者or拼接异常, 他还会帮我们进行处理, 很方便很智能
if
- 使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分
- 其标签内部加入test语句, 可以判断真与假
- 下面演示一个查询的例子
<select id="selectBlogNeed" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="id != null">
id=#{id}
</if>
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</where>
</select>
- 他的每一个if都会判断我是否传入了某个字段比入id, 如果传入了就在sql语句中拼接, 如果没有传入就不拼接部分
- 比如我现在传入title和views
public void selectNeed() {
Map<String, Object> map = new HashMap<>();
map.put("title", "爽歪歪歪");
//map.put("author", "bike");
map.put("views", 9998);
for (Blog blog : getBlogDao().selectBlogNeed(map)) {
System.out.println(blog);
}
close();
}
- 拼接的是第二个if下面的句子,而且and这个关键字已经被我们智能去掉了
choose、when、otherwise
- 有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
- 而when和java中的case差不多. otherwise就是java中的default
- 下面我演示一个例子我传入很多条件, 但是只找成功匹配的第一个语句进行拼接
<select id="selectBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="id != null">
id=#{id}
</when>
<when test="title != null">
title=#{title}
</when>
<when test="author != null">
author=#{author}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
- 我给map中传入id和title, 但是只会匹配id然后就去执行sql语句了
@org.junit.Test
public void selectChoose() {
Map<String, Object> map = new HashMap<>();
map.put("id", 1);
//map.put("title", "爽歪歪");
//map.put("author", "bike");
for (Blog blog : getBlogDao().selectBlogChoose(map)) {
System.out.println(blog);
}
close();
}
set
- 这个和where标签很相似, 就是很智能, 会删掉多余或者错误的逗号,主要使用在update语句中,在其内部搭配使用if标签就可以实现动态sql
- 下面演示update语句搭配set标签
<update id="update" parameterType="map">
update blog
<set>
<if test="newTitle != null">
title=#{newTitle},
</if>
<if test="newAuthor != null">
author=#{newAuthor},
</if>
<if test="newViews != null">
views = #{newViews}
</if>
</set>
<where>
<if test="id != null">
id=#{id}
</if>
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</where>
</update>
- 我要修改title和作者author, 我通过id和旧的title去判断
@org.junit.Test
public void update() {
Map<String, Object> map = new HashMap<>();
map.put("id", 3);
map.put("newTitle", "爽歪歪");
map.put("title", "爽歪歪歪");
map.put("newAuthor", "Faker");
getBlogDao().update(map);
close();
}
trim
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
- 用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。
set使用trim的定义如下, 会移除最后面错误的 ,
<trim prefix="SET" suffixOverrides=",">
...
</trim>
foreach
- 动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 或者OR 条件语句的时候)
- foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头poen与结尾close的字符串以及集合项迭代之间的分隔符separator。这个元素也不会错误地添加多余的分隔符,看它多智能!
- 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
- 下面演示构建in语句的时候
<select id="selectBlogForEach" parameterType="list" resultType="blog">
select * from blog
<where>
id in
<foreach collection="list" open="(" separator="," close=")" item="item">
#{item}
</foreach>
</where>
</select>
- 我先要要查id为1和3的文章
public void selectForEach() {
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(3);
for (Blog selectBlogForEach : getBlogDao().selectBlogForEach(list)) {
System.out.println(selectBlogForEach);
}
close();
}
- 如果是使用or关键字就照着下面使用
<select id="selectBlogForEach" parameterType="list" resultType="blog">
select * from blog
<where>
<foreach collection="list" open="(" separator=" or " close=")" item="item">
id=#{item}
</foreach>
</where>
</select>
sql
- 有时候,我么会把一些经常复用的sql片段单独摘出来,形成一个sql片段, 这个就是使用的sql标签实现的, 使用的时候直接使用include标签指定id就可以使用
- 最好基于单表来使用sql片段, 以免出错, 还有最好不要有where标签或者set标签, 尽量使用if标签就行
- 以便我们提高sql代码的复用
<sql id="repeat">
<if test="id != null">
id=#{id}
</if>
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</sql>
- 使用的时候, 直接使用include标签绑定id即可
<update id="update" parameterType="map">
update blog
<set>
<if test="newTitle != null">
title=#{newTitle},
</if>
<if test="newAuthor != null">
author=#{newAuthor},
</if>
<if test="newViews != null">
views = #{newViews}
</if>
</set>
<where>
<!-- <if test="id != null">
id=#{id}
</if>
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
<if test="views != null">
and views = #{views}
</if>-->
<include refid="repeat"/>
</where>
</update>
总结
- 动态sql就是指根据不同的条件生成不同的sql语句
- 所谓的动态sql本质还是sql语句, 只是可以在sql语句层面增加了逻辑代码
- 动态sql就是在拼接sql语句,我们只要保证sql的正确性,只是增加了一些逻辑代码去按照sql的格式排练组合一些sql语句