动态SQL语句
1、什么是动态SQL
动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句.
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
2、if
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如:
<select id="selectLike"
resultType="Blog" parameterType="map">
SELECT * FROM BLOG
WHERE 1=1
<if test="title != null">
AND title = #{title}
</if>
<if test="author != null">
AND author = #{author}
</if>
</select>
这条语句提供了可选的查找文本功能。如果不传入 “title”,那么所有处于 “ACTIVE” 状态的 BLOG 都会返回;如果传入了 “title” 参数,那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果(细心的读者可能会发现,“title” 的参数值需要包含查找掩码或通配符字符)。
测试:
@Test
public void testQueryBlogIf(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title","Mybatis如此简单");
map.put("author","张灵玉");
List<Blog> blogs = mapper.queryBlogIf(map);
System.out.println(blogs);
session.close();
}
3、Where、 trim 、foreach
where
示例:
<select id="selectLike"
resultType="Blog" parameterType="map">
SELECT * FROM BLOG
WHERE
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
AND author = #{author}
</if>
</select>
如果没有匹配的条件会怎么样?最终这条 SQL 会变成这样:
SELECT * FROM BLOG
WHERE
这会导致查询失败。如果匹配的只是第二个条件又会怎样?这条 SQL 会是这样:
SELECT * FROM BLOG
WHERE
AND author = #{author}
这个查询也会失败。
所以要用where标签
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
改进:
<select id="selectLike"
resultType="Blog" parameterType="map">
SELECT * FROM BLOG
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
AND author = #{author}
</if>
</where>
</select>
trim
通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND|OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
用于动态更新语句叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
<update id="updateAuthorIfNecessary">
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}
</update>
这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
set 元素等价的自定义 trim 元素吧:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
foreach
接口:
List<Blog> queryBlogForeach(Map map);
xml:
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
测试:
@Test
public void testQueryBlogForeach(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}
4、choose、when、otherwise
我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>