JingBin's Home

Mybatis 框架实战

我们容易忽视慢变量,但慢变量才是牵引历史进程的火车头。 - 何帆/变量

  • Mybatis 持久层:简化工作量、灵活
  • Spring 粘合剂:整合框架 AOP IOC DI
  • SpringMvc 表现层:方便前后端数据的传输

Mybatis:

  • 1.是对jdbc的封装,
  • 2.将sql语句放在映射文件中(xml),
  • 3.自动将输入参数映射到sql语句的动态参数上,
  • 4.自动将sql语句执行的结果映射成java对象

入门示例:

1.创建项目mubatis-01

2.导入jar:

  • mybatis-3.2.8
  • mysql-connect
  • log4j-1.2.17

3.加入配置文件

(1)db.properties

1
2
3
4
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mysql_0219
jdbc.username = root
jdbc.password = 123456

(2)log4j.properties
https://blog.csdn.net/sinat_30185177/article/details/73550377

1
2
3
log4j.rootLogger=DEBUG,A1
log4j.logger.org.mybatis=DEBUG
...

(3) mybatis核心配置文件:mybatis-config.xml

(4) BlogMapper.xml

包:com.jingbin.mybatis.mapper

4.编写接口:BlogMapper

5.创建pojo:Blog

6.创建工具类:MyBatisUtil

7.编写测试类:

1
2
3
4
5
6
testSelectBlog
发现要连接数据库,学习mysql内容
学习配置好了后:运行报错:Invalid bound statement (not found): mapper.BlogMapper.selectBlog
解决:https://www.cnblogs.com/cailijuan/p/9505244.html
1)使用创建接口的方式
2)不是用接口的方式

8.列名和属性名不一致的情况

数据库里的列名为author_id,属性名为authorId。在BlogMapper.xml里:

1.使用别名

select author_id as authorId from Blog where id=#{id}

2.使用 resultMap

1
2
3
4
5
6
7
8
<resultMap type="Blog" id="blogResultMap">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="author_id" property="authorId" jdbcType="INTEGER"/>
</resultMap>
<select id="selectBlog" parameterType="Integer" resultMap="blogResultMap">
select * from blog where id = #{id}
</select>

9.模糊查询之#$的区别

模糊查询:根据博客名字查询博客列表

1)使用#传参
2)使用$传参

#是占位符?,$是字符串拼接。

mybatis定义:

  • 使用$。如果参数是单指类型(简单类型),并且只有一个参数,则花括号里只能写value占位。
  • 使用$可以直接将%写里面,可能有sql注入的风险,建议最好使用#。参数是字符串要使用 ‘’
  • 当参数表示表名或列名的时候,只能使用$
1
2
3
4
5
<!-- 使用 $ 不区分大小写的查询 lower-->
<select id="selectBlogByTitle2" parameterType="string" resultType="Blog">
select * from blog where lower(title) like lower('%${value}%')
</select>
</mapper>

10.查询排序

需求:按照某一列排序

select * from blog order by CONVERT(${value} USING gbk)

gbk:输入中文时排序成功,否则会失败。且使用gbk规避魅族(gb2313)不排序问题。

11.分页-多参数传递

需求:查询分页数据

  • 1)使用索引

按照参数的顺序,从0开始

select * from blog limit #{0}, #{1}

  • 2)使用注解

注解的value值要和mapper的占位参数一致。

1
2
select * from blog limit #{offset}, #{pageSize}
List<Blog> selectBlogByPage2(@Param(value = "offset") int offset, @Param(value = "pageSize") int pageSize);
  • 3)使用map(常用)

注意:mapper中的参数占位符要和测试中的map的key一一对应

1
2
3
4
5
6
7
8
select * from blog limit #{offset}, #{pageSize}
// 测试
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Map<String, Object> objectMap = new HashMap<>();
objectMap.put("offset", 0);
objectMap.put("pageSize", 2);
List<Blog> blogList = blogMapper.selectBlogByPage3(objectMap);

12.插入功能和获取刚刚插入的id

  • 1)插入记录

需求:新增一个博客记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<insert id="insertBlog" parameterType="Blog">
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>
// 提交
session.commit();
  • 2)获取自增id
    方式1:在mapper中配置insert节点的属性 useGeneratedKeyskeyProperty节点
    1
    <insert id="insertBlog" parameterType="Blog" useGeneratedKeys="true" keyProperty="id"/>

方式2:在全局配置文件中配置setting

1
2
3
4
5
6
7
<!--定义数据库链接配置-->
<properties resource="db.properties"/>
<!--具体的insert也得配置 keyProperty节点-->
<settings>
<setting name="useGeneratedKeys" value="true"/>
</settings>

方式3:适用于没有自增主键的数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<insert id="insertBlogOracle" parameterType="Blog">
<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
select seq.nextval as id from dual
</selectKey>
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>
<insert id="insertBlogMysql" parameterType="Blog">
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID()
</selectKey>
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>

13.修改功能和修改部分字段注意的问题

1
2
3
4
5
6
7
8
9
10
11
<update id="updateBlog" parameterType="Blog">
update `blog`
set
`name` = #{name},
`age` = #{age},
`title` = #{title},
`author_id` = #{author_id},
`featured` = #{featured}
where
`id` = #{id}
</update>

注意:如果没有为对象设置所有的要修改的属性,那么未设置的属性会用成员变量的默认值填充。
解决:
方式1:数据库查一遍,再返回的数据修改。缺点:又执行了一遍数据库操作
方式2:查询语句里增加if else。

14.删除记录

1
2
3
<delete id="deleteBlogById">
delete from blog where id=#{id}
</delete>

动态sql
批量删除:使用动态sql实现
if、[choose、when、otherwise]、where、set、trim、foreach、sql片段

15.if

需求:

  • 1.查询已激活的并且博客的名字是包含某个查询字符串的记录
  • 2.如果用户没有输入任何查询字符串,那么就显示所有已激活的记录

// 如果用户输入了查询字符串
select * from blog
where state = ‘ACTIVE’
and title like ‘%o%’

// 用户没有输入查询字符串
select * from blog
where state = ‘ACTIVE’

1
2
3
4
5
select * from blog
where state = 'ACTIVE'
<if test="value != null and value!=''">
and title like value[%%] 具体见代码
</if>

16.choose、when、otherwise

需求:

  • 1、查询已激活的
  • 2、如果用户输入了标题的查询关键字,则根据关键字查询
  • 3、否则根据blog风格样式查询
  • 4、如果什么都没有输入,则显示推荐的博客
1
2
3
4
5
6
7
8
9
<select id="selectActiveBlogByTitleOrStyle" parameterType="Blog" resultType="Blog">
select * from blog
where state = 'ACTIVE'
<choose>
<when test="title != null and title!=''">and lower(title) like lower(#{title})</when>
<when test="style != null and style!=''">and style = #{style}</when>
<otherwise> and featured = true</otherwise>
</choose>
</select>

17.where

需求:多条件查询,根据状态,标题,是否被推荐
自动修补查询条件,查询语句中的where关键字使用标签替代,不能省略 and or 关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="selectBlogByCondition" parameterType="Blog" resultType="Blog">
select * from blog
<where>
<if test="state != null and state!=''">
state = #{state}
</if>
<if test="title != null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured != null">
and featured = #{featured}
</if>
</where>
</select>

18.set

需求:按需修改,修改执行的列,未指定的不修改
set 会自动去掉if语句后面的逗号

1
2
3
4
5
6
7
8
9
10
11
12
13
<update id="updateBlogByCondition" parameterType="Blog">
update `blog`
<set>
<if test="name != null">`name` = #{name},</if>
<if test="age != null">`age` = #{age},</if>
<if test="title != null">`title` = #{title},</if>
<if test="author_id != null">`author_id` = #{author_id},</if>
<if test="featured != null">`featured` = #{featured},</if>
<if test="state != null">`state` = #{state},</if>
<if test="style != null">`style` = #{state},</if>
</set>
where `id` = #{id}
</update>

19.trim

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<select id="selectBlogByConditionTrim" parameterType="Blog" resultType="Blog">
select * from blog
-- prefixOverrides 去掉前面的 and 或 or
<trim prefix="where" prefixOverrides="and | or">
<if test="state != null and state!=''">
state = #{state}
</if>
<if test="title != null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured != null">
and featured = #{featured}
</if>
</trim>
</select>
<update id="updateBlogByConditionTrim" parameterType="Blog">
update `blog`
-- suffixOverrides 去掉后面的 ,
<trim prefix="set" suffixOverrides=",">
<if test="name != null">`name` = #{name},</if>
<if test="age != null">`age` = #{age},</if>
<if test="title != null">`title` = #{title},</if>
<if test="author_id != null">`author_id` = #{author_id},</if>
<if test="featured != null">`featured` = #{featured},</if>
<if test="state != null">`state` = #{state},</if>
<if test="style != null">`style` = #{state},</if>
</trim>
where `id` = #{id}
</update>

20.foreach

需求:批量删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!-- parameterType 与 collection 一致-->
<delete id="deleteBlogList" parameterType="list">
delete from blog where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
@Test
public void testDeleteBlogList() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Integer> asList = Arrays.asList(1, 2);
int count = blogMapper.deleteBlogList(asList);
// 提交
session.commit();
session.close();
System.out.println("更新了" + count + "条记录");
}

21.sql片段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!--sql片段-->
<sql id="columnBase">
`id`,
`name`,
`age`,
`title`,
`author_id`,
`featured`,
`state`,
`style`
</sql>
<!--通过id查询博客-->
<select id="selectBlog" parameterType="Integer" resultType="Blog">
select
<include refid="columnBase"/>
from blog where id = #{id}
</select>

22 23.逆向功工程-代码的生成

使用 Mybatis 官方工具直接生成对应的mapper文件
全部生成 - 无实际意义,具体项目中不适用

24.缓存 - mybatis的一级缓存

一级缓存:
是session级别的缓存,一级缓存默认存在。
当在同一个session范围内执行查询的时候,如果执行相同的查询,那么第二次查询会从缓存中获取数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
* mybatis的一级缓存
*/
@Test
public void testSelectBlogCacheOne1() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog1 = blogMapper.selectBlog(3);
System.out.println("结果已查询" + blog1);
Blog blog2 = blogMapper.selectBlog(3);
System.out.println("结果已查询:从缓存中获取数据" + blog2);
session.close();
System.out.println("session关闭");
}

25.缓存 - 一级缓存被刷新的情况

如果两次查询中间有增删改操作,sql session缓存区会被自动清空,说明下一次查询会重新执行sql语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
* mybatis的一级缓存,中间有增删改会刷新
*/
@Test
public void testSelectBlogCacheOne2() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog1 = blogMapper.selectBlog(3);
System.out.println("结果已查询" + blog1);
blog1.setFeatured(true);
blogMapper.updateBlog(blog1);
Blog blog2 = blogMapper.selectBlog(3);
System.out.println("结果已查询:从新执行查询" + blog2);
session.commit();
session.close();
System.out.println("session关闭");
}

26.缓存 - 开启二级缓存

默认不开启。如果在不同的session范围内执行相同的数据查询,那么每次查询将会执行独立的数据库检索过程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/**
* mybatis的二级缓存
*/
@Test
public void testSelectBlogCacheLevelTwo1() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
Blog blog1 = blogMapper1.selectBlog(3);
System.out.println("结果已查询" + blog1);
session1.close();
SqlSession session2 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper2 = session2.getMapper(BlogMapper.class);
Blog blog2 = blogMapper2.selectBlog(3);
System.out.println("结果已查询:从缓存中获取数据" + blog2);
session2.close();
System.out.println("session关闭");
}

开启二级缓存
1.在mapper文件中设置
2.在实体类中实现序列化接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public class Blog implements Serializable {
private static final long serialVersionUID = 1L;
}
/**
* mybatis的二级缓存
*/
@Test
public void testSelectBlogCacheLevelTwo1() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
Blog blog1 = blogMapper1.selectBlog(3);
System.out.println("结果已查询" + blog1);
session1.close();
// Cache Hit Ratio [mapper.BlogMapper]: 0.5
SqlSession session2 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper2 = session2.getMapper(BlogMapper.class);
Blog blog2 = blogMapper2.selectBlog(3);
System.out.println("结果已查询:从缓存中获取数据" + blog2);
session2.close();
System.out.println("session关闭");
}

27.缓存 - 二级缓存被刷新的情况

和一级缓存相同,中间有增删改则二级缓存会被清空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
* mybatis的二级缓存 缓存被刷新
*/
@Test
public void testSelectBlogCacheLevelTwo2() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
Blog blog1 = blogMapper1.selectBlog(3);
blog1.setFeatured(true);
blogMapper1.updateBlog(blog1);
System.out.println("结果已查询" + blog1);
session1.close();
SqlSession session2 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper2 = session2.getMapper(BlogMapper.class);
Blog blog2 = blogMapper2.selectBlog(3);
System.out.println("缓存被刷新,重新查询" + blog2);
session2.close();
System.out.println("session关闭");
}

28.嵌套查询 - 一对一和多对一

高级结果映射
一、关联映射(适用于一对一和多对一的情况)

1).创建pojo
(1)在Blog中创建Author对象

1
private Author author;

(2)创建Author的pojo

2).配置mapper

BlogMapper.java

1
2
3
4
5
6
7
8
9
<resultMap type="Blog" id="blogResultMap">
<id column="id" property="id" jdbcType="INTEGER"></id>
<association property="author" column="author_id" javaType="Author"
select="me.jing.mapper.AuthorMapper.selectAuthorById">
</association>
</resultMap>
<select id="selectBlogById" parameterType="int" resultMap="blogResultMap">
select * from blog where id = #{id}
</select>

AuthorMapper.java

1
2
3
4
5
6
7
8
9
<resultMap type="Author" id="authorResultMap">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="favourite_section" property="favouriteSection" jdbcType="VARCHAR"/>
</resultMap>
<select id="selectAuthorById" parameterType="int" resultMap="authorResultMap">
select * from author where id = #{id}
</select>

3) 接口

(1) Blog

1
Blog selectBlogById(Integer id);

(2) Author

1
Author selectAuthorById(Integer id);

4) 测试

查询Blog列表时,执行的sql语句是 1+n,但是如果blog关联的author有重复的数据,那么从一级缓存中查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
* 通过id查具体的博客,查询时 再拿到作者id 去作者表查具体的作者信息
*/
@Test
public void testSelectBlogById() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
Blog blog = blogMapper1.selectBlogById(3);
session1.close();
System.out.println("结果已查询" + blog);
}

29.嵌套查询 - 显示信息列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="selectBlogList" resultMap="blogResultMap">
select * from blog
</select>
List<Blog> selectBlogList();
@Test
public void testSelectBlogList() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
List<Blog> blogs = blogMapper1.selectBlogList();
session1.close();
System.out.println("结果已查询" + blogs);
}

30.嵌套查询-一对多的配置 31.嵌套查询-一对多的查询配置

执行流程:1.先执行单表查询,2.再利用单表查询的结果继续执行其他单表查询,3.最后组装结果映射。

通过post的id 查询post表的内容,同时通过此id查询评论表 comment 里的数据列表,即这篇文章的评论列表

sql:

1
2
3
4
5
6
7
8
9
10
11
create table post(id int,blog_id int,author_id int,
section varchar(20),subject varchar(20),draft varchar(20));
insert into post(id,blog_id,author_id,section,subject,draft)
values('1','3','15','section','subject','draft');
create table comment(id int,
name varchar(20),comment varchar(20));
insert into comment(id,name,comment,post_id)
values('1','评论name','我觉得很棒 评论','1');

PostMapper.xml

1
2
3
4
5
6
7
8
9
10
<resultMap type="Post" id="postResultMap">
<id column="id" property="id" jdbcType="INTEGER"></id>
<collection property="commentList" column="id" javaType="ArrayList" ofType="Comment"
select="me.jing.mapper.CommentMapper.selectCommentListByPostId"></collection>
</resultMap>
<select id="selectPostById" parameterType="int" resultMap="postResultMap">
select * from post where id = #{id}
</select>

CommentMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
<resultMap type="Comment" id="commentResultMap">
<id column="id" property="id" jdbcType="INTEGER"></id>
</resultMap>
<!--通过post_id查询评论列表-->
<select id="selectCommentListByPostId" parameterType="int" resultMap="commentResultMap">
select * from comment where post_id = #{postId}
</select>
<select id="selectCommentById" parameterType="int" resultMap="commentResultMap">
select * from comment where id = #{id}
</select>

测试:

1
2
3
4
5
6
7
8
9
10
11
12
/**
* 通过post的id 查询post表的内容,
* 同时通过此id查询评论表 comment 里的数据列表,即这篇文章的评论列表
*/
@Test
public void selectPostById() {
SqlSession session1 = MyBatisUtil.getSqlSession();
PostMapper mapper = session1.getMapper(PostMapper.class);
Post post = mapper.selectPostById(1);
System.out.println("结果已查询" + post);
session1.close();
}

32.高级结果映射-嵌套结果

1).先执行关联查询,一次性将所有数据都查询出来
2).再将所有查询出来的列组织成嵌套的结果对象

1
2
3
4
SELECT * FROM
blog b
LEFT JOIN author a
ON b.author_id = a.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
b.id as blog_id,
b.title as blog_title,
b.author_id as blog_author_id,
b.state as blog_state,
b.featured as blog_featured,
b.style as blog_style,
a.id as author_id,
a.username as author_username,
a.password as author_password,
a.email as author_email
FROM blog b
LEFT JOIN author a
ON b.author_id = a.id;

BlogMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<resultMap id="blogResultMapNested" type="Blog">
<id column="blog_id" property="id"/>
<result column="blog_title" property="title"/>
<result column="blog_state" property="state"/>
<result column="blog_featured" property="featured"/>
<result column="blog_style" property="style"/>
<!--<result column="blog_author_id" property="author_id"/>-->
<association property="author" column="blog_author_id" javaType="Author">
<id column="author_id" property="id"/>
<result column="author_username" property="username"/>
<result column="author_password" property="password"/>
<result column="author_email" property="email"/>
</association>
</resultMap>
<!--32.高级结果映射-嵌套结果-->
<select id="selectBlogListNested" resultMap="blogResultMapNested">
SELECT
b.id as blog_id,
b.title as blog_title,
b.author_id as blog_author_id,
b.state as blog_state,
b.featured as blog_featured,
b.style as blog_style,
a.id as author_id,
a.username as author_username,
a.password as author_password
FROM blog b
LEFT JOIN author a
ON b.author_id = a.id;
</select>
/**
* 嵌套查询
*/
List<Blog> selectBlogListNested();
/**
* 嵌套查询
*/
@Test
public void testSelectBlogListNested() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
List<Blog> blogs = blogMapper1.selectBlogListNested();
session1.close();
System.out.println("结果已查询2:" + blogs);
}

33.为什么要做扩展结果集|34.扩展结果的实现

1).mapper

BlogCustomMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<sql id="baseColumn">
b.id,
b.`title`,
b.`author_id`,
b.`state`,
b.`featured`,
b.`style`,
a.username as authorUsername
</sql>
<select id="selectBlogById" parameterType="int" resultType="BlogCustom">
select
<include refid="baseColumn"/>
from blog b
left join author a
on b.author_id = a.id
where b.id = #{id}
</select>

BlogCustomMapper.java

1
2
3
public interface BlogCustomMapper {
BlogCustom selectBlogById(Integer id);
}

2)vo.BlogCustom.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/**
* 扩展Blog
*/
public class BlogCustom extends Blog {
private static final long serialVersionUID = 1L;
private String authorUsername;
public String getAuthorUsername() {
return authorUsername;
}
public void setAuthorUsername(String authorUsername) {
this.authorUsername = authorUsername;
}
@Override
public String toString() {
return "BlogCustom{" +
"authorUsername='" + authorUsername + '\'' +
'}';
}
}
<!--定义别名-->
<typeAliases>
<!--<typeAlias type="pojo.Blog" alias="Blog"/>-->
<package name="me.jing.pojo"/>
<package name="me.jing.pojo.vo"/>
</typeAliases>

3)测试

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
* 通过id查具体的博客,查询时 再拿到作者id 去作者表查具体的作者 username
*/
@Test
public void testSelectBlogById() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogCustomMapper mapper = session1.getMapper(BlogCustomMapper.class);
BlogCustom blog = mapper.selectBlogById(3);
session1.close();
System.out.println("结果已查询" + blog);
}

35.高级结果映射 - 构造方式映射

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Bolg.xml
public Blog(Integer id, String title) {
this.id = id;
this.title = title;
System.out.println("构造函数调用-----");
}
<!--构造方法映射-->
<resultMap id="blogResultMapConstructor" type="Blog">
<constructor>
<idArg column="id" javaType="int"/>
<arg column="title" javaType="string"/>
</constructor>
</resultMap>
<select id="selectBlogByIdConstructor" parameterType="int" resultMap="blogResultMapConstructor">
select * from blog where id = #{id}
</select>
/**
* 构造方式映射
*/
Blog selectBlogByIdConstructor(Integer id);
/**
* 构造方式映射
*/
@Test
public void testSelectBlogByIdConstructor() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
Blog blog = blogMapper1.selectBlogByIdConstructor(3);
session1.close();
System.out.println("结果已查询:" + blog);
}

36.鉴别器

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `vehicle`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`vin` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`year` YEAR(4) DEFAULT NULL,
`make` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`model` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`color` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`vehicle_type` INT(11) DEFAULT NULL,
`door_count` INT(11) DEFAULT NULL,
`all_wheel_drive` TINYINT(1) DEFAULT NULL,
PRIMARY KEY(`id`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
VehicleMapper.xml
<resultMap type="Vehicle" id="vehicleResultMap">
<id column="id" property="id" jdbcType="INTEGER"/>
<discriminator javaType="int" column="vehicle_type">
<case value="1" resultType="Car">
<result column="door_count" property="doorCount"/>
</case>
<case value="2" resultType="Suv">
<result column="all_wheel_drive" property="allWheelDrive"/>
</case>
</discriminator>
</resultMap>
<select id="selectVehicleById" parameterType="int" resultMap="vehicleResultMap">
select * from vehicle where id = #{id}
</select>
Vehicle selectVehicleById(Integer id);

pojo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
public class Vehicle {
private Integer id;
private Integer vehicle_type;
private String vin;
private String make;
private String model;
private String color;
private Boolean all_wheel_drive;
}
public class Suv extends Vehicle {
// 全轮驱动 awd
private Boolean allWheelDrive;
public Suv() {
}
public Boolean getAllWheelDrive() {
return allWheelDrive;
}
public void setAllWheelDrive(Boolean allWheelDrive) {
this.allWheelDrive = allWheelDrive;
}
@Override
public String toString() {
return "Suv{" +
"allWheelDrive=" + allWheelDrive +
'}';
}
}
public class Car extends Vehicle {
// 门的数量
private Integer doorCount;
public Integer getDoorCount() {
return doorCount;
}
public void setDoorCount(Integer doorCount) {
this.doorCount = doorCount;
}
@Override
public String toString() {
return "Car{" +
"doorCount=" + doorCount +
'}';
}
}

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
* 鉴别器
*/
@Test
public void testSelectVehicleById() {
SqlSession session1 = MyBatisUtil.getSqlSession();
VehicleMapper mapper = session1.getMapper(VehicleMapper.class);
Vehicle vehicle = mapper.selectVehicleById(1);
Vehicle vehicle2 = mapper.selectVehicleById(2);
if (vehicle instanceof Car) {
Car car = (Car) vehicle;
Suv suv = (Suv) vehicle2;
System.out.println("car:" + car);
System.out.println("suv:" + suv);
} else {
Car car = (Car) vehicle2;
Suv suv = (Suv) vehicle;
System.out.println("car:" + car);
System.out.println("suv:" + suv);
}
System.out.println("vehicle:" + vehicle);
System.out.println("vehicle2:" + vehicle2);
session1.close();
}

37.延迟加载 - 不配置延迟加载的情况

测试一:
没有配置任何选项,执行两遍查询,性能比较低

无论是否查询blog的任意属性,都会执行author的任何查询,就是说 始终会执行两次查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
查询blog----
Opening JDBC Connection
Created connection 504858437.
Setting autocommit to false on JDBC Connection [[email protected]1e178745]
==> Preparing: select * from blog where id = ?
==> Parameters: 3(Integer)
====> Preparing: select * from author where id = ?
====> Parameters: 15(Integer)
<==== Total: 1
<== Total: 1
Resetting autocommit to true on JDBC Connection [[email protected]1e178745]
Closing JDBC Connection [[email protected]1e178745]
Returned connection 504858437 to pool.
查询blog的title属性----
Android开发艺术探索
查询blog的author属性----
景彬
结果已查询

38.延迟加载 - 配置lazyloadingenabled

测试二:
如果不查询blog的任意属性,那么不会执行author的查询

配置了全局属性:

1
2
3
4
5
<settings>
<!--<setting name="useGeneratedKeys" value="true"/>-->
<!--延迟加载 配置lazyloadingenabled-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/**
* 测试延迟加载
* 没有配置 lazyLoadingEnabled 时,会始终执行两次查询
*/
@Test
public void testSelectBlogByIdLazyLoading() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
System.out.println("查询blog----");
Blog blog = blogMapper1.selectBlogById(3);
session1.close();
// System.out.println("查询blog的title属性----");
// System.out.println(blog.getTitle());
// System.out.println("查询blog的author属性----");
// System.out.println(blog.getAuthor().getUsername());
System.out.println("结果已查询");
}

日志打印:

1
2
3
4
5
6
7
8
9
10
11
查询blog----
Opening JDBC Connection
Created connection 210156003.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
==> Preparing: select * from blog where id = ?
==> Parameters: 3(Integer)
<== Total: 1
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Returned connection 210156003 to pool.
结果已查询

39.延迟加载 - 测试用例1和2的比较

40.延迟加载 - 积极的延迟加载

延迟加载:用属性就加载,不用就不加载。
积极的延迟加载:只要用属性,就把所有要查询的属性sql全部执行一遍。

测试三:
只要查询bolg的任意属性,都会执行author数据的查询

配置了全局属性(同测试2的配置):

1
2
3
4
5
<settings>
<!--<setting name="useGeneratedKeys" value="true"/>-->
<!--延迟加载 配置lazyloadingenabled-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/**
* 测试延迟加载
* 没有配置 lazyLoadingEnabled 时,会始终执行两次查询
*/
@Test
public void testSelectBlogByIdLazyLoading() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
System.out.println("查询blog----");
Blog blog = blogMapper1.selectBlogById(3);
session1.close();
System.out.println("查询blog的title属性----");
System.out.println(blog.getTitle());
// System.out.println("查询blog的author属性----");
// System.out.println(blog.getAuthor().getUsername());
System.out.println("结果已查询");
}

41.延迟加载 - 非积极延迟加载

测试四:
延迟加载,并且是非积极的。如果需要访问blog的非author属性,则不执行关联的author查询。

配置了全局属性:

配置后,没有调用 blog.getAuthor() 属性,就只会执行一次,调用 blog.getAuthor() 属性才回再执行查询author表一次。

1
2
3
4
5
6
7
8
<!--具体的insert也得配置 keyProperty-->
<settings>
<setting name="useGeneratedKeys" value="true"/>
<!--延迟加载 配置lazyloadingenabled-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--非积极的延迟加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
* 测试延迟加载
*/
@Test
public void testSelectBlogByIdLazyLoading() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
System.out.println("查询blog----");
Blog blog = blogMapper1.selectBlogById(3);
session1.close();
System.out.println("查询blog的title属性----");
System.out.println(blog.getTitle());
// System.out.println("查询blog的author属性----");
// System.out.println(blog.getAuthor().getUsername());
System.out.println("结果已查询");
}

打印:

1
2
3
4
5
6
7
8
9
10
11
12
13
查询blog----
Opening JDBC Connection
Created connection 210156003.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
==> Preparing: select * from blog where id = ?
==> Parameters: 3(Integer)
<== Total: 1
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Returned connection 210156003 to pool.
查询blog的title属性----
Android开发艺术探索
结果已查询

测试五:

配置了全局属性 同配置四

延迟加载,并且是不积极的。如果访问了blog的author属性,则执行关联的author查询

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
* 测试延迟加载
*/
@Test
public void testSelectBlogByIdLazyLoading() {
SqlSession session1 = MyBatisUtil.getSqlSession();
BlogMapper blogMapper1 = session1.getMapper(BlogMapper.class);
System.out.println("查询blog----");
Blog blog = blogMapper1.selectBlogById(3);
session1.close();
System.out.println("查询blog的title属性----");
System.out.println(blog.getTitle());
System.out.println("查询blog的author属性----");
System.out.println(blog.getAuthor().getUsername());
System.out.println("结果已查询");
}

打印:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
查询blog----
Opening JDBC Connection
Created connection 210156003.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
==> Preparing: select * from blog where id = ?
==> Parameters: 3(Integer)
<== Total: 1
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Returned connection 210156003 to pool.
查询blog的title属性----
Android开发艺术探索
查询blog的author属性----
Opening JDBC Connection
Checked out connection 210156003 from pool.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
==> Preparing: select * from author where id = ?
==> Parameters: 15(Integer)
<== Total: 1
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c86b9e3]
Returned connection 210156003 to pool.
景彬
结果已查询