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节点的属性 useGeneratedKeys和keyProperty节点
    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 [com.mysql.cj.jdbc.ConnectionImpl@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 [com.mysql.cj.jdbc.ConnectionImpl@1e178745]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@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.
景彬
结果已查询