前言:

推荐免费MyBatis基础教程视频:【狂神说Java】Mybatis最新完整教程IDEA版通俗易懂_哔哩哔哩_bilibili

动态SQL和IF语句

BlogMapper.java

1
2
// 查询博客
List<Blog> queryBlogIF(Map map);

BlogMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jokerdig.mapper.BlogMapper">
<!--动态SQL中if实现条件查询-->
<!-- where标签可以帮我们智能的去除不需要的and或or-->
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title!=null">
and title = #{title}
</if>
<if test="author!=null">
and author = #{author}
</if>
</where>
</select>
</mapper>

BlogTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void test1(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

HashMap map = new HashMap();
// 添加title改变查询结果
map.put("title","Hey,Joker");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}

动态SQL常用标签

Chose…When…OtherWise

BlogMapper.java

1
2
// 查询 chose
List<Blog> queryBlogChose(Map map);

BolgMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!--动态SQL中chose实现条件查询-->
<select id="queryBlogChose" 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>

BlogTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void test2(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

HashMap map = new HashMap();
// 添加title改变查询结果
map.put("author","JokerDaxiong");
List<Blog> blogs = mapper.queryBlogChose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}

sqlSession.close();
}

Set

更新时去除多余逗号

BlogMapper.java

1
2
 // 更新博客 使用set标签
int updateBlog(Map map);

BolgMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<!--    更新博客 使用set标签-->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title!=null">
title=#{title},
</if>
<if test="author!=null">
author=#{author},
</if>
</set>
where id = #{id}
</update>

BlogTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
@Test
public void test3(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

HashMap map = new HashMap();
// 添加title改变查询结果
map.put("title","测试set");
map.put("author","JokerDaxiong");
map.put("id","3");
mapper.updateBlog(map);
sqlSession.close();
}

trim(where,set)

trim包括了where和set,trim可以通过自定义trim元素来定制where元素的功能。(使用并不是很多)

1
2
3
4
5
6
7
8
<!--where-->
<trim prefix="where" prefixOverrides="and|or">
.....
</trim>
<!--set-->
<trim prefix="set" suffixOverrides=",">
.....
</trim>

动态SQL和Foreach

SQL片段

有的时候,我们可能会把一些公共的SQL提取出一些SQL片段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 <!--   提取公共sql片段-->
<sql id="queryColl">
<if test="title!=null">
and title = #{title}
</if>
<if test="author!=null">
and author = #{author}
</if>
</sql>
<!-- 使用include标签引用sql片段-->
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<where>
<include refid="queryColl"></include>
</where>
</select>

注意事项

  • 最好基于单表来定义SQL片段
  • 不要存在where和set标签

Foreach

动态SQL的另外一个常用的操作需求是对一个集合进行遍历,通常是在构建in条件语句的时候

官方举例

1
2
3
4
5
6
7
8
9
<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>

练习

BlogMapper.java

1
2
// 查询 id 为 123的信息 使用foreach
List<Blog> queryBlogForEach(Map map);

BlogMapper.xml

1
2
3
4
5
6
7
8
9
<!--    使用foreach遍历-->
<select id="queryBlogForEach" parameterType="map" resultType="Blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>

BlogTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void test4(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForEach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}

动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式,去排列组合就可以了