Mybatis3.X
原生 JDBC 访问数据库的缺点
原生 JDBC 缺点
- 每次要加载驱动，连接信息是硬编码
- SQL 和 Java 代码耦合一起，改动不易
- 参数设置缺少灵活性
- 处理结果集麻烦，不能自动映射到实体类
- 连接资源不能复用，资源浪费
ORM 框架介绍
-
什么是 ORM 框架
- 对数据库的表和 POJO(Plain Ordinary Java Object)Java 对象的做映射的框架
-
市面上的 ORM 框架
-
hibernate (ssh)
- 比较笨重，SQL 调优麻烦
- 推荐指数：不推荐
-
JPA - Spring Data JPA
- Java Persistence API, 轻量级，部分中小项目适合
- 推荐指数：次之
-
MyBatis
- 半自动化(半 ORM 框架)，便于写 SQL，轻量级，在阿里等大厂广泛使用
- 推荐指数：推荐
-
Mybaits3.X
-
什么是 MyBatis ？
- Apahce 的一个开源项目
- 一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射
- 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作
- 通过简单的 XML 或注解来配置和映射 Java 对象 到 数据库中的记录
- 官方地址：https://mybatis.org/mybatis-3/
-
新建一个测试的 maven 项目
-
核心流程：https://mybatis.org/mybatis-3/zh/getting-started.html
- 每个基于 MyBatis 的应用都是以一个 SqlSessionFactory 的实例为核心
- SqlSessionFactory 的实例可以通过 SqlSessionFactoryBuilder 获得
- SqlSessionFactoryBuilder 可以从 XML 配置文件或一个预先配置的 Configuration 实例来构建出 SqlSessionFactory 实例
- 工厂设计模式里面 需要获取 SqlSession ，里面提供了在数据库执行 SQL 命令所需的所有方法
快速入门
pom.xml
1<?xml version="1.0" encoding="UTF-8"?>
2<project xmlns="http://maven.apache.org/POM/4.0.0"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5 <modelVersion>4.0.0</modelVersion>
6
7 <groupId>net.xdclas</groupId>
8 <artifactId>xd-mybatis</artifactId>
9 <version>1.0-SNAPSHOT</version>
10
11
12 <dependencies>
13 <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
14 <dependency>
15 <groupId>org.mybatis</groupId>
16 <artifactId>mybatis</artifactId>
17 <version>3.5.4</version>
18 </dependency>
19
20 <dependency>
21 <groupId>mysql</groupId>
22 <artifactId>mysql-connector-java</artifactId>
23 <version>8.0.19</version>
24 </dependency>
25
26 <dependency>
27 <groupId>org.slf4j</groupId>
28 <artifactId>slf4j-log4j12</artifactId>
29 <version>1.7.5</version>
30 </dependency>
31
32 </dependencies>
33
34</project>
resources/config/mybatis-config.xml
1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE configuration
3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
5<configuration>
6 <environments default="development">
7 <environment id="development">
8 <transactionManager type="JDBC"/>
9 <dataSource type="POOLED">
10 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
11 <property name="url" value="jdbc:mysql://192.168.31.101:50000/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
12 <property name="username" value="root"/>
13 <property name="password" value="123456"/>
14 </dataSource>
15 </environment>
16 </environments>
17 <mappers>
18 <mapper resource="mapper/VideoMapper.xml"/>
19 </mappers>
20</configuration>
/resources/mapper/VideoMapper.xml
1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5
6<mapper namespace="net.xdclass.online_class.dao.VideoMapper">
7 <select id="selectById" resultType="net.xdclass.online_class.domain.Video">
8 select * from video where id = #{video_id}
9 </select>
10
11</mapper>
VideoMapper
1package net.xdclass.online_class.dao;
2
3import net.xdclass.online_class.domain.Video;
4import org.apache.ibatis.annotations.Param;
5import org.apache.ibatis.annotations.Select;
6
7import java.util.List;
8
9public interface VideoMapper {
10 /**
11 * 根据视频 id 查找视频对象
12 * @param videoId
13 * @return
14 */
15 Video selectById(@Param("video_id") int videoId);
16
17 /**
18 * 查询全部视频列表
19 */
20 @Select("select * from video")
21 List<Video> selectList();
22}
Video
1package net.xdclass.online_class.domain;
2
3import java.util.Date;
4
5public class Video {
6 private int id;
7 private String titile;
8 private String summary;
9 private String coverImg;
10 private int price;
11 private Date createTime;
12 private double point;
13
14 public String getSummary() {
15 return summary;
16 }
17
18 public void setSummary(String summary) {
19 this.summary = summary;
20 }
21
22 public String getCoverImg() {
23 return coverImg;
24 }
25
26 public void setCoverImg(String coverImg) {
27 this.coverImg = coverImg;
28 }
29
30 public int getPrice() {
31 return price;
32 }
33
34 public void setPrice(int price) {
35 this.price = price;
36 }
37
38 public Date getCreateTime() {
39 return createTime;
40 }
41
42 public void setCreateTime(Date createTime) {
43 this.createTime = createTime;
44 }
45
46 public double getPoint() {
47 return point;
48 }
49
50 public void setPoint(double point) {
51 this.point = point;
52 }
53
54 public int getId() {
55 return id;
56 }
57
58 public void setId(int id) {
59 this.id = id;
60 }
61
62 public String getTitile() {
63 return titile;
64 }
65
66 public void setTitile(String titile) {
67 this.titile = titile;
68 }
69
70 @Override
71 public String toString() {
72 return "Video{" +
73 "id=" + id +
74 ", titile='" + titile + '\'' +
75 ", summary='" + summary + '\'' +
76 ", coverImg='" + coverImg + '\'' +
77 ", price=" + price +
78 ", createTime=" + createTime +
79 ", point=" + point +
80 '}';
81 }
82}
SqlSessionDemo
1package net.xdclass.online_class;
2
3import net.xdclass.online_class.dao.VideoMapper;
4import net.xdclass.online_class.domain.Video;
5import org.apache.ibatis.io.Resources;
6import org.apache.ibatis.session.SqlSession;
7import org.apache.ibatis.session.SqlSessionFactory;
8import org.apache.ibatis.session.SqlSessionFactoryBuilder;
9
10import java.io.IOException;
11import java.io.InputStream;
12import java.util.List;
13
14public class SqlSessionDemo {
15 public static void main(String[] args) throws IOException {
16 String resource = "config/mybatis-config.xml";
17 //读取配置文件
18 InputStream inputStream = Resources.getResourceAsStream(resource);
19 //构建Session工厂
20 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
21 //获取Session
22 try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
23 VideoMapper videoMapper = sqlSession.getMapper(VideoMapper.class);
24
25 //xml方式
26 Video video = videoMapper.selectById(44);
27 //System.out.println(video.toString());//Video{id=44, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='null', price=3980, createTime=null, point=9.3}
28
29 //注解方式
30 List<Video> videoList = videoMapper.selectList();
31 System.out.println(videoList.toString());//[Video{id=30, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/jvm_detail.jpeg', coverImg='null', price=3980, createTime=null, point=9.1}, Video{id=31, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/shiro_detail.jpeg', coverImg='null', price=2980, createTime=null, point=8.9}, Video{id=32, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/maven_detail.jpeg', coverImg='null', price=1980, createTime=null, point=8.9}, Video{id=33, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/js_detail.jpeg', coverImg='null', price=2980, createTime=null, point=8.8}, Video{id=34, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/html5_detail.jpeg', coverImg='null', price=3980, createTime=null, point=8.8}, Video{id=35, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_frontend/pc%E5%AE%9E%E6%88%98/pc_detail.jpeg', coverImg='null', price=3980, createTime=null, point=8.7}, Video{id=36, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_frontend/es67/es67_detail.png
32 ', coverImg='null', price=3980, createTime=null, point=8.7}, Video{id=37, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/dubbo_project_new.jpeg', coverImg='null', price=14880, createTime=null, point=9.1}, Video{id=38, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/linux_base_detail.png', coverImg='null', price=3980, createTime=null, point=9.1}, Video{id=39, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/video/react_detail.png', coverImg='null', price=6980, createTime=null, point=9.1}, Video{id=40, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_frontend/%E5%B0%8F%E7%A8%8B%E5%BA%8F/wx_app_detail.png', coverImg='null', price=5980, createTime=null, point=9.1}, Video{id=41, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_backend/elasticsearch7_detail.jpeg', coverImg='null', price=4880, createTime=null, point=8.7}, Video{id=42, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/2019_frontend/element/elementui_detail.png', coverImg='null', price=5980, createTime=null, point=8.7}, Video{id=43, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/python/python_detail.png', coverImg='null', price=3980, createTime=null, point=8.9}, Video{id=44, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='null', price=3980, createTime=null, point=9.3}, Video{id=45, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/video/docker.png', coverImg='null', price=5980, createTime=null, point=9.3}, Video{id=47, titile='null', summary='https://file.xdclass.net/video/2020/node/node_detail.png', coverImg='null', price=6980, createTime=null, point=8.9}, Video{id=46, titile='null', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/javase/javase_detail.png', coverImg='null', price=3980, createTime=null, point=8.8}]
33
34 }
35 }
36}
控制台打印 SQL
-
内置的日志工厂提供日志功能, 使用 log4j 配置打印 sql,添加依赖
1<dependency> 2 <groupId>org.slf4j</groupId> 3 <artifactId>slf4j-log4j12</artifactId> 4 <version>1.7.5</version> 5 </dependency> -
在应用的 classpath 中创建名称为
log4j.properties的文件1log4j.rootLogger=ERROR, stdout 2# TRACE会打印更多信息 3log4j.logger.net.xdclass=DEBUG 4# 细化到打印某个mapper 5# log4j.logger.net.xdclass.online_class.dao.VideoMapper.selectById=TRACE 6log4j.appender.stdout=org.apache.log4j.ConsoleAppender 7log4j.appender.stdout.layout=org.apache.log4j.PatternLayout 8log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
参数别名
常用的查询
-
默认参数查询
-
单个参数，可以使用别名，也可以使用默认的名称，默认名称的话可以随意
-
使用参数别名
1Video selectById(@Param("video_id") int videoId); 2 3select * from video where id = #{video_id}
-
-
模糊查询，MySQL 自带函数使用
-
多参数查询一定要使用别名 @Param 注解
1/**
2 * 根据评分和标题模糊查询
3 */
4 List<Video> selectByPointAndTitleLike(@Param("point") double point, @Param("title") String title);
5
6 /**
7 * video.xml
8 */
9 <select id="selectByPointAndTitleLike" resultType="net.xdclass.online_class.domain.Video">
10 select * from video where point=#{point} and title like concat('%',#{title},'%')
11 </select>
驼峰字段映射 Java 对象和数据库字段
数据库的字段名：_
Java 对象的数据:mM
- 方法一：使用别名
- 方法二：MyBatis 自带配置（加在文件顶部）
mybatis-config.xml
1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE configuration
3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
5<configuration>
6 <!-- 下换线转驼峰 -->
7 <settings>
8 <setting name="mapUnderscoreToCamelCase" value="true"/>
9 </settings>
10 <environments default="development">
11 <environment id="development">
12 <transactionManager type="JDBC"/>
13 <dataSource type="POOLED">
14 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
15 <property name="url" value="jdbc:mysql://192.168.31.101:50000/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
16 <property name="username" value="root"/>
17 <property name="password" value="123456"/>
18 </dataSource>
19 </environment>
20 </environments>
21 <mappers>
22 <mapper resource="mapper/VideoMapper.xml"/>
23 </mappers>
24</configuration>
入参 parameterType 和取值类型
-
parameterType 参数类型（一般不需要写，@Param 可以自动转换识别）
- 可以是基本类型
parameterType="java.lang.Long"
parameterType="java.lang.String" - 可以是 Java 集合 List 或者 Map (批量插入的时候)
parameterType="java.util.Map"
parameterType="java.util.List" - 可以是 Java 自定义对象
parameterType="net.xdclass.online_class.domain.Video"
- 可以是基本类型
-
取参数值，具体某个字段的类型，从 Java 类型映射到数据库类型
- 例子 #{title, jdbcType=VARCHAR}
- 注意:
- 多数情况不加是正常使用，但是如果出现报错：无效的列类型，则是缺少 jdbcType;
- 只有当字段可为 NULL 时才需要 jdbcType 属性
- 常见的数据库类型和 Java 列席对比
1JDBC Type Java Type
2
3CHAR String
4VARCHAR String
5LONGVARCHAR String
6NUMERIC java.math.BigDecimal
7DECIMAL java.math.BigDecimal
8BIT boolean
9BOOLEAN boolean
10TINYINT byte
11SMALLINT short
12INTEGER INTEGER
13INTEGER int
14BIGINT long
15REAL float
16FLOAT double
17DOUBLE double
18BINARY byte[]
19VARBINARY byte[]
20LONGVARBINARY byte[]
21DATE java.sql.Date
22TIME java.sql.Time
23TIMESTAMP java.sql.Timestamp
24CLOB Clob
25BLOB Blob
26ARRAY Array
27DISTINCT mapping of underlying type
28STRUCT Struct
29REF Ref
30DATALINK java.net.URL
新增记录并获取主键
新增记录 useGeneratedKeys="true" keyProperty="id" keyColumn="id"
1<insert id="add" parameterType="net.xdclass.online_class.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
2 INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
3 VALUES (#{title,jdbcType=VARCHAR},#{summary,jdbcType=VARCHAR},#{coverImg,jdbcType=VARCHAR},
4 #{price,jdbcType=INTEGER},#{createTime,jdbcType=TIMESTAMP},#{point,jdbcType=DOUBLE});
5 </insert>
6
7
8/**
9
10* 新增一条视频记录
11*
12* @param video
13* @return
14 */
15 int add(Video video);
16
17
18 //新增一条记录
19 Video video = new Video();
20 video.setTitle("面试专题");
21 video.setCoverImg("xdclass/aaa.png");
22 video.setPoint(9.4);
23 video.setCreateTime(new Date());
24 video.setPrice(9900);
25 video.setSummary("这是面试专题概要");
26 videoMapper.add(video);
27
28 //mybatis插入完成会帮忙将主键自动回写到POJO的id属性中
29 System.out.println(video.getId());
foreach 批量插入
- 批量插入多条视频记录
- foreach: 用于循环拼接的内置标签，常用于 批量新增、in 查询等常见
1包含以下属性：
2collection：必填，值为要迭代循环的集合类型，情况有多种
3入参是List类型的时候，collection属性值为list
4入参是Map类型的时候，collection 属性值为map的key值
5
6item：每一个元素进行迭代时的别名
7index：索引的属性名，在集合数组情况下值为当前索引值，当迭代对象是map时，这个值是map的key
8open：整个循环内容的开头字符串
9close：整个循环内容的结尾字符串
10separator: 每次循环的分隔符
1<insert id="addBatch" parameterType="net.xdclass.online_class.domain.Video">
2 INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
3 VALUES
4 <foreach collection="list" item="video" index="index" separator=",">
5 (#{video.title,jdbcType=VARCHAR},#{video.summary,jdbcType=VARCHAR},#{video.coverImg,jdbcType=VARCHAR},
6 #{video.price,jdbcType=INTEGER},#{video.createTime,jdbcType=TIMESTAMP},#{video.point,jdbcType=DOUBLE})
7 </foreach>
8 </insert>
9
10/**
11 * 批量插入
12 * @return
13 * @param list
14 */
15 int addBatch(List<Video> list);
16
17
18//新增一条记录
19 Video video = new Video();
20 video.setTitle("面试专题");
21 video.setCoverImg("xdclass/aaa.png");
22 video.setPoint(9.4);
23 video.setCreateTime(new Date());
24 video.setPrice(9900);
25 video.setSummary("这是面试专题概要");
26 videoMapper.add(video);
27
28 Video video2 = new Video();
29 video.setTitle("面试专题2");
30 video.setCoverImg("xdclass/aaa.png");
31 video.setPoint(9.2);
32 video.setCreateTime(new Date());
33 video.setPrice(9200);
34 video.setSummary("这是面试专题概要");
35 videoMapper.add(video);
36
37 List<Video> list = new ArrayList<>();
38 list.add(video);
39 list.add(video2);
40
41 int rows = videoMapper.addBatch(list);
42
43 System.out.println(rows); //2
更新
问题：存在其他不想被更新的字段却置为 null 或者默认值了
1<update id="updateVideo" parameterType="net.xdclass.online_class.domain.Video">
2 update video
3 set
4 title = #{title,jdbcType=VARCHAR},
5 summary = #{summary,jdbcType=VARCHAR},
6 cover_img = #{coverImg,jdbcType=VARCHAR},
7 price = #{price,jdbcType=INTEGER},
8 create_time = #{createTime,jdbcType=TIMESTAMP},
9 point = #{point,jdbcType=DOUBLE}
10 where
11 id = #{id}
12 </update>
13
14
15
16 /**
17 * 更新操作
18 */
19 int updateVideo(Video video);
20
21
22
23 Video video = new Video();
24 video.setId(56);
25 video.setTitle("面试专题xxx");
26 video.setCoverImg("xdclass/xxx.pngxxx");
27 videoMapper.updateVideo(video);
选择更新
1可以选择更新非空字段
1if 标签可以通过判断传入的值来确定查询条件，test 指定一个OGNL表达式
2
3常见写法
4
5//当前字段符合条件才更新这个字段的值
6<if test='title != null and id == 87 '> title = #{title}, </if>
7
8<if test="title!=null"> title = #{title}, </if>
1<update id="updateVideoSelective" parameterType="net.xdclass.online_class.domain.Video">
2 update video
3 <trim prefix="set" suffixOverrides=",">
4 <if test="title != null"> title = #{title,jdbcType=VARCHAR},</if>
5 <if test="summary != null"> summary = #{summary,jdbcType=VARCHAR},</if>
6 <if test="coverImg != null"> cover_img = #{coverImg,jdbcType=VARCHAR},</if>
7 <if test="price !=0"> price = #{price,jdbcType=INTEGER},</if>
8 <if test="createTime != null"> create_time = #{createTime,jdbcType=TIMESTAMP},</if>
9 <!--一定要看POJO的类里面是基本数据类型，还是包装数据类型 -->
10 <if test="point != null"> point = #{point,jdbcType=DOUBLE},</if>
11 </trim>
12 where
13 id = #{id}
14 </update>
15
16
17
18 /**
19 * 动态选择更新
20 * @param video
21 * @return
22 */
23 int updateVideoSelective(Video video);
24
25
26
27 Video video = new Video();
28 video.setId(55);
29 video.setTitle("面试专题xxx");
30 video.setCoverImg("xdclass/xxx.pngxxx");
31 videoMapper.updateVideoSelective(video);
32
33
34DEBUG [main] - ==> Preparing: update video set title = ?, cover_img = ? where id = ?
35DEBUG [main] - ==> Parameters: 面试专题xxx(String), xdclass/xxx.pngxxx(String), 55(Integer)
36DEBUG [main] - <== Updates: 1
删除
为什么要转义字符:
1由于MyBatis的sql写在XML里面， 有些sql的语法符号和xml里面的冲突
2 大于等于
1小于等于
delete 删除语法
1删除某个时间段之后，且金额小于10000元的数据
1<delete id="deleteByCreateTimeAndPrice" parameterType="java.util.Map">
2 delete from video where create_time <![CDATA[ > ]]> #{createTime} and price <![CDATA[ < ]]> #{price}
3 </delete>
4
5
6
7 /**
8 * 根据时间和价格删除
9 * @return
10 */
11 int deleteByCreateTimeAndPrice(Map<String,Object> map);
12
13
14
15 Map<String, Object> map = new HashMap<>();
16 map.put("createTime", "2021-01-11 09:33:21");
17 map.put("price", 10000);
18 int rows = videoMapper.deleteByCreateTimeAndPrice(map);
19 System.out.println(rows);
20
21
22
23DEBUG [main] - ==> Preparing: delete from video where create_time > ? and price < ?
24DEBUG [main] - ==> Parameters: 2021-01-11 09:33:21(String), 10000(Integer)
25DEBUG [main] - <== Updates: 2
262
mybatis-config.xml 常见属性
1configuration（配置）
2 properties（属性）
3 settings（设置）
4 typeAliases（类型别名）
5 typeHandlers（类型处理器）
6 objectFactory（对象工厂）
7 plugins（插件，少用）
8 environments（环境配置，不配多环境，基本在Spring里面配置）
9 environment（环境变量）
10 transactionManager（事务管理器）
11 dataSource（数据源）
12 databaseIdProvider（数据库厂商标识）
13 mappers（映射器）
官方文档：https://mybatis.org/mybatis-3/zh/configuration.html#
typeAlias 别名的使用
类型别名:给类取个别名，可以不用输入类的全限定名
1<typeAliases>
2 <typeAlias type="net.xdclass.online_class.domain.Video" alias="Video"/>
3 </typeAliases>
4
5
6 <!-- 不用写全限定名 net.xdclass.online_class.domain.Video 写 Video即可-->
7 <select id="selectByPointAndTitleLike" resultType="Video">
8 select * from video where point=#{point} and title like concat('%',#{title},'%')
9 </select>
包扫描：包下的全部类的名字作为类名，上面需要配置多个，包更加简单
1<typeAliases>
2 <package name="net.xdclass.online_class.domain"/>
3 </typeAliases>
4
5
6 <!-- 不用写全限定名 net.xdclass.online_class.domain.Video 写 Video即可-->
7 <select id="selectByPointAndTitleLike" resultType="Video">
8 select * from video where point=#{point} and title like concat('%',#{title},'%')
9 </select>
本身就内置：很多别名，比如 Integer、String、List、Map 等
SQL 片段
根据业务需要，自定制要查询的字段，并可以复用
1<sql id="base_video_field">
2 id,title,summary,cover_img
3 </sql>
4
5 <select id="selectById" parameterType="java.lang.Integer" resultType="Video">
6 select <include refid="base_video_field"/> from video where id = # {video_id,jdbcType=INTEGER}
7 </select>
复杂 SQL 查询
MyBatis 的 SQL 语句返回结果有两种
-
resultType
- 查询出的字段在相应的 pojo 中必须有和它相同的字段对应，或者基本数据类型
- 适合简单查询
-
resultMap
- 需要自定义字段，或者多表查询，一对多等关系，比 resultType 更强大
- 适合复杂查询
resultMap 单表查询
1<resultMap id="VideoResultMap" type="Video">
2 <!--
3 id 指定查询列的唯一标示
4 column 数据库字段的名称
5 property pojo类的名称
6 -->
7 <id column="id" property="id" jdbcType="INTEGER" />
8 <result column="video_tile" property="title" jdbcType="VARCHAR" />
9 <result column="summary" property="summary" jdbcType="VARCHAR" />
10 <result column="cover_img" property="coverImg" jdbcType="VARCHAR" />
11 </resultMap>
12
13 <select id="selectBaseFieldByIdWithResultMap" resultMap="VideoResultMap">
14 select id , title as video_tile, summary, cover_img from video where id = #{video_id}
15 </select>
16
17
18
19 /**
20 * 根据id查询视频
21 * @param id
22 * @return
23 */
24 Video selectBaseFieldByIdWithResultMap(@Param("video_id") int id);
25
26
27
28 Video video = videoMapper.selectBaseFieldByIdWithResultMap(45);
29 System.out.println(video);
30
31
32
33DEBUG [main] - ==> Preparing: select id , title as video_tile, summary, cover_img from video where id = ?
34DEBUG [main] - ==> Parameters: 45(Integer)
35DEBUG [main] - <== Total: 1
36Video{id=45, title='Docker实战视频教程入门到高级dockerfile/compose-Harbor', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/video/docker.png', coverImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/docker/docker.png', price=0, createTime=null, point=null}
ResultMap 复杂对象一对多查询结果映射 association
MyBatis 复杂对象映射配置 ResultMap 的 association
association：映射到 POJO 的某个复杂类型属性，比如订单 order 对象里面包含 user 对象
User
1package net.xdclass.online_class.domain;
2
3import java.util.Date;
4
5/**
6 * `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
7 * `name` varchar(128) DEFAULT NULL COMMENT '昵称',
8 * `pwd` varchar(124) DEFAULT NULL COMMENT '密码',
9 * `head_img` varchar(524) DEFAULT NULL COMMENT '头像',
10 * `phone` varchar(64) DEFAULT '' COMMENT '手机号',
11 * `create_time` datetime DEFAULT NULL COMMENT '创建时间',
12 */
13public class User {
14
15 private int id;
16
17 private String name;
18
19 private String pwd;
20
21 private String headImg;
22
23 private String phone;
24
25 private Date createTime;
26
27
28 public int getId() {
29 return id;
30 }
31
32 public void setId(int id) {
33 this.id = id;
34 }
35
36 public String getName() {
37 return name;
38 }
39
40 public void setName(String name) {
41 this.name = name;
42 }
43
44 public String getPwd() {
45 return pwd;
46 }
47
48 public void setPwd(String pwd) {
49 this.pwd = pwd;
50 }
51
52 public String getHeadImg() {
53 return headImg;
54 }
55
56 public void setHeadImg(String headImg) {
57 this.headImg = headImg;
58 }
59
60 public String getPhone() {
61 return phone;
62 }
63
64 public void setPhone(String phone) {
65 this.phone = phone;
66 }
67
68 public Date getCreateTime() {
69 return createTime;
70 }
71
72 public void setCreateTime(Date createTime) {
73 this.createTime = createTime;
74 }
75
76 @Override
77 public String toString() {
78 return "User{" +
79 "id=" + id +
80 ", name='" + name + '\'' +
81 ", pwd='" + pwd + '\'' +
82 ", headImg='" + headImg + '\'' +
83 ", phone='" + phone + '\'' +
84 ", createTime=" + createTime +
85 '}';
86 }
87}
VideoOrder
1package net.xdclass.online_class.domain;
2
3import java.util.Date;
4
5/**
6 * `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
7 * `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
8 * `state` int(11) DEFAULT NULL COMMENT '0表示未支付，1表示已支付',
9 * `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
10 * `total_fee` int(11) DEFAULT NULL COMMENT '支付金额，单位分',
11 * `video_id` int(11) DEFAULT NULL COMMENT '视频主键',
12 * `video_title` varchar(256) DEFAULT NULL COMMENT '视频标题',
13 * `video_img` varchar(256) DEFAULT NULL COMMENT '视频图片',
14 * `user_id` int(12) DEFAULT NULL COMMENT '用户id',
15 */
16public class VideoOrder {
17
18
19 private int id;
20
21 private String outTradeNo;
22
23 private int state;
24
25 private Date createTime;
26
27 private int totalFee;
28
29
30 private int videoId ;
31
32 private String videoTitle;
33
34
35 private String videoImg;
36
37
38 private int userId;
39
40 private User user;
41
42
43 public int getId() {
44 return id;
45 }
46
47 public void setId(int id) {
48 this.id = id;
49 }
50
51 public String getOutTradeNo() {
52 return outTradeNo;
53 }
54
55 public void setOutTradeNo(String outTradeNo) {
56 this.outTradeNo = outTradeNo;
57 }
58
59 public int getState() {
60 return state;
61 }
62
63 public void setState(int state) {
64 this.state = state;
65 }
66
67 public Date getCreateTime() {
68 return createTime;
69 }
70
71 public void setCreateTime(Date createTime) {
72 this.createTime = createTime;
73 }
74
75 public int getTotalFee() {
76 return totalFee;
77 }
78
79 public void setTotalFee(int totalFee) {
80 this.totalFee = totalFee;
81 }
82
83 public int getVideoId() {
84 return videoId;
85 }
86
87 public void setVideoId(int videoId) {
88 this.videoId = videoId;
89 }
90
91 public String getVideoTitle() {
92 return videoTitle;
93 }
94
95 public void setVideoTitle(String videoTitle) {
96 this.videoTitle = videoTitle;
97 }
98
99 public String getVideoImg() {
100 return videoImg;
101 }
102
103 public void setVideoImg(String videoImg) {
104 this.videoImg = videoImg;
105 }
106
107 public int getUserId() {
108 return userId;
109 }
110
111 public void setUserId(int userId) {
112 this.userId = userId;
113 }
114
115 public User getUser() {
116 return user;
117 }
118
119 public void setUser(User user) {
120 this.user = user;
121 }
122
123 @Override
124 public String toString() {
125 return "VideoOrder{" +
126 "id=" + id +
127 ", outTradeNo='" + outTradeNo + '\'' +
128 ", state=" + state +
129 ", createTime=" + createTime +
130 ", totalFee=" + totalFee +
131 ", videoId=" + videoId +
132 ", videoTitle='" + videoTitle + '\'' +
133 ", videoImg='" + videoImg + '\'' +
134 ", userId=" + userId +
135 ", user=" + user +
136 '}';
137 }
138}
VideoOrderMapper
1package net.xdclass.online_class.dao;
2
3import net.xdclass.online_class.domain.VideoOrder;
4
5import java.util.List;
6
7public interface VideoOrderMapper {
8
9 /**
10 * 查询全部订单，关联用户信息
11 * @return
12 */
13 List<VideoOrder> queryVideoOrderList();
14
15}
VideoOrderMapper.xml
1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5
6<mapper namespace="net.xdclass.online_class.dao.VideoOrderMapper">
7
8
9 <resultMap id="VideoOrderResultMap" type="VideoOrder">
10 <id column="id" property="id"/>
11
12 <result column="user_id" property="userId"/>
13 <result column="out_trade_no" property="outTradeNo"/>
14 <result column="create_time" property="createTime"/>
15 <result column="state" property="state"/>
16 <result column="total_fee" property="totalFee"/>
17 <result column="video_id" property="videoId"/>
18 <result column="video_title" property="videoTitle"/>
19 <result column="video_img" property="videoImg"/>
20
21
22 <!--
23 association 配置属性一对一
24 property 对应videoOrder里面的user属性名
25 javType 这个属性的类型
26 -->
27 <association property="user" javaType="User">
28 <id property="id" column="user_id"/>
29 <result property="name" column="name"/>
30 <result property="headImg" column="head_img"/>
31 <result property="createTime" column="create_time"/>
32 <result property="phone" column="phone"/>
33 </association>
34
35 </resultMap>
36
37 <!--一对一管理查询订单， 订单内部包含用户属性-->
38 <select id="queryVideoOrderList" resultMap="VideoOrderResultMap">
39 select
40 o.id id,
41 o.user_id ,
42 o.out_trade_no,
43 o.create_time,
44 o.state,
45 o.total_fee,
46 o.video_id,
47 o.video_title,
48 o.video_img,
49 u.name,
50 u.head_img,
51 u.create_time,
52 u.phone
53 from video_order o left join user u on o.user_id = u.id
54 </select>
55
56</mapper>
查询结果
1List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderList();
2 System.out.println(videoOrderList);
3
4DEBUG [main] - ==> Preparing: select o.id id, o.user_id , o.out_trade_no, o.create_time, o.state, o.total_fee, o.video_id, o.video_title, o.video_img, u.name, u.head_img, u.create_time, u.phone from video_order o left join user u on o.user_id = u.id
5DEBUG [main] - ==> Parameters:
6DEBUG [main] - <== Total: 6
7[VideoOrder{id=1, outTradeNo='werr23', state=1, createTime=null, totalFee=32, videoId=47, videoTitle='Nodejs教程零基础入门到项目实战前端视频教程', videoImg='null', userId=5, user=User{id=5, name='大乔', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTKFAPgcL5zMhldHKR8fR9TKHjNru8fuTia5dDpiaPvyMlahbsQG4VYh94SCYvupVmdu8sF7ia39bPnicg/132', phone='65345', createTime=null}}, VideoOrder{id=2, outTradeNo='2342323', state=1, createTime=null, totalFee=42, videoId=45, videoTitle='Docker实战视频教程入门到高级dockerfile/compose-Harbor', videoImg='null', userId=6, user=User{id=6, name='Wiggin', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTIlPAtqFWzr6zQa33esvNNy1MsNw3Ik4O4nGzzcLSW9y9ia8xticev4RtT4JVK5USjUPQqfJarC3lOQ/132', phone='234324', createTime=null}}, VideoOrder{id=3, outTradeNo='534332', state=1, createTime=null, totalFee=11, videoId=47, videoTitle='Nodejs教程零基础入门到项目实战前端视频教程', videoImg='null', userId=6, user=User{id=6, name='Wiggin', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTIlPAtqFWzr6zQa33esvNNy1MsNw3Ik4O4nGzzcLSW9y9ia8xticev4RtT4JVK5USjUPQqfJarC3lOQ/132', phone='234324', createTime=null}}, VideoOrder{id=4, outTradeNo='2342323', state=1, createTime=null, totalFee=42, videoId=17, videoTitle='互联网架构多线程并发编程高级教程', videoImg='null', userId=6, user=User{id=6, name='Wiggin', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTIlPAtqFWzr6zQa33esvNNy1MsNw3Ik4O4nGzzcLSW9y9ia8xticev4RtT4JVK5USjUPQqfJarC3lOQ/132', phone='234324', createTime=null}}, VideoOrder{id=5, outTradeNo='3424521', state=1, createTime=null, totalFee=32, videoId=45, videoTitle='Docker实战视频教程入门到高级dockerfile/compose-Harbor', videoImg='null', userId=5, user=User{id=5, name='大乔', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTKFAPgcL5zMhldHKR8fR9TKHjNru8fuTia5dDpiaPvyMlahbsQG4VYh94SCYvupVmdu8sF7ia39bPnicg/132', phone='65345', createTime=null}}, VideoOrder{id=6, outTradeNo='23', state=1, createTime=null, totalFee=42, videoId=17, videoTitle='互联网架构多线程并发编程高级教程', videoImg='null', userId=4, user=User{id=4, name='网二', pwd='null', headImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/upload/video/video_cover.png', phone='435234234', createTime=null}}]
ResultMap 复杂对象一对多映射配置 collection
collection：一对多查询结果映射，比如 user 有多个订单
User
1package net.xdclass.online_class.domain;
2
3import java.util.Date;
4import java.util.List;
5
6/**
7 * `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
8 * `name` varchar(128) DEFAULT NULL COMMENT '昵称',
9 * `pwd` varchar(124) DEFAULT NULL COMMENT '密码',
10 * `head_img` varchar(524) DEFAULT NULL COMMENT '头像',
11 * `phone` varchar(64) DEFAULT '' COMMENT '手机号',
12 * `create_time` datetime DEFAULT NULL COMMENT '创建时间',
13 */
14public class User {
15
16 private int id;
17
18 private String name;
19
20 private String pwd;
21
22 private String headImg;
23
24 private String phone;
25
26 private Date createTime;
27
28 //List
29 private List<VideoOrder> videoOrderList;
30
31 public List<VideoOrder> getVideoOrderList() {
32 return videoOrderList;
33 }
34
35 public void setVideoOrderList(List<VideoOrder> videoOrderList) {
36 this.videoOrderList = videoOrderList;
37 }
38
39 public int getId() {
40 return id;
41 }
42
43 public void setId(int id) {
44 this.id = id;
45 }
46
47 public String getName() {
48 return name;
49 }
50
51 public void setName(String name) {
52 this.name = name;
53 }
54
55 public String getPwd() {
56 return pwd;
57 }
58
59 public void setPwd(String pwd) {
60 this.pwd = pwd;
61 }
62
63 public String getHeadImg() {
64 return headImg;
65 }
66
67 public void setHeadImg(String headImg) {
68 this.headImg = headImg;
69 }
70
71 public String getPhone() {
72 return phone;
73 }
74
75 public void setPhone(String phone) {
76 this.phone = phone;
77 }
78
79 public Date getCreateTime() {
80 return createTime;
81 }
82
83 public void setCreateTime(Date createTime) {
84 this.createTime = createTime;
85 }
86
87 @Override
88 public String toString() {
89 return "User{" +
90 "id=" + id +
91 ", name='" + name + '\'' +
92 ", pwd='" + pwd + '\'' +
93 ", headImg='" + headImg + '\'' +
94 ", phone='" + phone + '\'' +
95 ", createTime=" + createTime +
96 ", videoOrderList=" + videoOrderList +
97 '}';
98 }
99}
VideoOrderMapper.xml
1<resultMap id="UserOrderResultMap" type="User">
2
3 <id property="id" column="id"/>
4 <result property="name" column="name"/>
5 <result property="headImg" column="head_img"/>
6 <result property="createTime" column="create_time"/>
7 <result property="phone" column="phone"/>
8
9 <!--
10 property 填写pojo类中集合类属性的名称
11 ofType 集合里面的pojo对象
12 -->
13 <collection property="videoOrderList" ofType="VideoOrder">
14
15 <!--配置主键，管理order的唯一标识-->
16 <id column="order_id" property="id"/>
17 <result column="user_id" property="userId"/>
18 <result column="out_trade_no" property="outTradeNo"/>
19 <result column="create_time" property="createTime"/>
20 <result column="state" property="state"/>
21 <result column="total_fee" property="totalFee"/>
22 <result column="video_id" property="videoId"/>
23 <result column="video_title" property="videoTitle"/>
24 <result column="video_img" property="videoImg"/>
25 </collection>
26 </resultMap>
27
28 <select id="queryUserOrder" resultMap="UserOrderResultMap">
29 select
30 u.id,
31 u.name,
32 u.head_img,
33 u.create_time,
34 u.phone,
35 o.id order_id,
36 o.out_trade_no,
37 o.user_id,
38 o.create_time,
39 o.state,
40 o.total_fee,
41 o.video_id,
42 o.video_title,
43 o.video_img
44 from user u left join video_order o on u.id = o.user_id
45
46 </select>
VideoOrderMapper
1package net.xdclass.online_class.dao;
2
3import net.xdclass.online_class.domain.User;
4import net.xdclass.online_class.domain.VideoOrder;
5
6import java.util.List;
7
8public interface VideoOrderMapper {
9
10 /**
11 * 查询全部订单，关联用户信息
12 * @return
13 */
14 List<VideoOrder> queryVideoOrderList();
15
16 /**
17 * 查询全部用户的全部订单
18 * @return
19 */
20 List<User> queryUserOrder();
21}
测试结果
1List<User> userList = videoOrderMapper.queryUserOrder();
2 System.out.println(userList);
3
4
5
6DEBUG [main] - ==> Preparing: select u.id, u.name, u.head_img, u.create_time, u.phone, o.id order_id, o.out_trade_no, o.user_id, o.create_time, o.state, o.total_fee, o.video_id, o.video_title, o.video_img from user u left join video_order o on u.id = o.user_id
7DEBUG [main] - ==> Parameters:
8DEBUG [main] - <== Total: 10
9[User{id=5, name='大乔', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTKFAPgcL5zMhldHKR8fR9TKHjNru8fuTia5dDpiaPvyMlahbsQG4VYh94SCYvupVmdu8sF7ia39bPnicg/132', phone='65345', createTime=Sun Jul 01 19:31:36 CST 2018, videoOrderList=[VideoOrder{id=1, outTradeNo='werr23', state=1, createTime=Sun Jul 01 19:31:36 CST 2018, totalFee=32, videoId=47, videoTitle='Nodejs教程零基础入门到项目实战前端视频教程', videoImg='null', userId=5, user=null}, VideoOrder{id=5, outTradeNo='3424521', state=1, createTime=Sun Jul 01 19:31:36 CST 2018, totalFee=32, videoId=45, videoTitle='Docker实战视频教程入门到高级dockerfile/compose-Harbor', videoImg='null', userId=5, user=null}]}, User{id=6, name='Wiggin', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTIlPAtqFWzr6zQa33esvNNy1MsNw3Ik4O4nGzzcLSW9y9ia8xticev4RtT4JVK5USjUPQqfJarC3lOQ/132', phone='234324', createTime=Sun Jul 01 22:35:37 CST 2018, videoOrderList=[VideoOrder{id=2, outTradeNo='2342323', state=1, createTime=Sun Jul 01 22:35:37 CST 2018, totalFee=42, videoId=45, videoTitle='Docker实战视频教程入门到高级dockerfile/compose-Harbor', videoImg='null', userId=6, user=null}, VideoOrder{id=3, outTradeNo='534332', state=1, createTime=Sun Jul 01 22:35:37 CST 2018, totalFee=11, videoId=47, videoTitle='Nodejs教程零基础入门到项目实战前端视频教程', videoImg='null', userId=6, user=null}, VideoOrder{id=4, outTradeNo='2342323', state=1, createTime=Sun Jul 01 22:35:37 CST 2018, totalFee=42, videoId=17, videoTitle='互联网架构多线程并发编程高级教程', videoImg='null', userId=6, user=null}]}, User{id=4, name='网二', pwd='null', headImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/upload/video/video_cover.png', phone='435234234', createTime=Sun Jul 01 08:52:47 CST 2018, videoOrderList=[VideoOrder{id=6, outTradeNo='23', state=1, createTime=Sun Jul 01 08:52:47 CST 2018, totalFee=42, videoId=17, videoTitle='互联网架构多线程并发编程高级教程', videoImg='null', userId=4, user=null}]}, User{id=3, name='niatouguai', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/WXTcCMevfMa6dpxkWtaaCdDH0TSdwHxNYfFpoRp6aJdnI9sMaWAEOIwia6JlZibntwSypIdicRcnSxqsFWiaEQFR6A/132', phone='234235', createTime=Sun Jul 01 02:28:23 CST 2018, videoOrderList=[VideoOrder{id=0, outTradeNo='null', state=0, createTime=Sun Jul 01 02:28:23 CST 2018, totalFee=0, videoId=0, videoTitle='null', videoImg='null', userId=0, user=null}]}, User{id=2, name='coffee', pwd='null', headImg='https://thirdwx.qlogo.cn/mmopen/vi_32/cer7IibrbEfP3cAJnpe96DDnb5Y82ShLuial2HuQpnUbak7k4G4tT6Wk7A5cmYiclI8iaJia5Aia8UZMWyEgVW3nvjkA/132', phone='56453445', createTime=Sun Jul 01 00:42:30 CST 2018, videoOrderList=[VideoOrder{id=0, outTradeNo='null', state=0, createTime=Sun Jul 01 00:42:30 CST 2018, totalFee=0, videoId=0, videoTitle='null', videoImg='null', userId=0, user=null}]}, User{id=1, name='铁锤', pwd='null', headImg='https://thirdwx.qlogo.cn/mmopen/vi_32/cer7IibrbEfP3cAJnpe96DDnb5Y82ShLuial2HuQpnUbak7k4G4tT6Wk7A5cmYiclI8iaJia5Aia8UZMWyEgVW3nvjkA/132', phone='2342452', createTime=Sat Jun 30 23:15:35 CST 2018, videoOrderList=[VideoOrder{id=0, outTradeNo='null', state=0, createTime=Sat Jun 30 23:15:35 CST 2018, totalFee=0, videoId=0, videoTitle='null', videoImg='null', userId=0, user=null}]}, User{id=7, name='天际浮云', pwd='null', headImg='http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTLm1cr9F5xkbib3sSWzgic7OQfxkRva6RICTAk7wwVd5BN51oVdbdF0PLtGSzciaZMs4lTgHVSWlzQyA/132', phone='23423534', createTime=Mon Jul 02 02:41:39 CST 2018, videoOrderList=[VideoOrder{id=0, outTradeNo='null', state=0, createTime=Mon Jul 02 02:41:39 CST 2018, totalFee=0, videoId=0, videoTitle='null', videoImg='null', userId=0, user=null}]}]
复杂对象查询总结
- association 映射的是一个 pojo 类，处理一对一的关联关系。
- collection 映射的一个集合列表，处理的是一对多的关联关系。
1<!-- column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
2<resultMap id="唯一的标识" type="映射的pojo对象">
3 <id column="表的主键字段,或查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
4 <result column="表的一个字段" jdbcType="字段类型" property="映射到pojo对象的一个属性"/>
5
6 <association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
7 <id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
8 <result column="表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
9 </association>
10
11 <!-- 集合中的property 需要为oftype定义的pojo对象的属性-->
12 <collection property="pojo的集合属性名称" ofType="集合中单个的pojo对象类型">
13 <id column="集合中pojo对象对应在表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
14 <result column="任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />
15 </collection>
16</resultMap>
一级缓存
-
什么是缓存
- 程序经常要调用的对象存在内存中,方便其使用时可以快速调用,不必去数据库或者其他持久化设备中查询，主要就是提高性能
-
MyBatis 一级缓存（默认是开启的）
- 简介：一级缓存的作用域是 SQLSession，同一个 SqlSession 中执行相同的 SQL 查询(相同的 SQL 和参数)，第一次会去查询数据库并写在缓存中，第二次会直接从缓存中取
- 基于 PerpetualCache 的 HashMap 本地缓存
- 默认开启一级缓存
-
失效策略：当执行 SQL 时候两次查询中间发生了增删改的操作，即 insert、update、delete 等操作 commit 后会清空该 SQLSession 缓存; 比如 sqlsession 关闭，或者清空等
演示效果（同一个 SQLSession 中两次查询只发出了一次 SQL 查询）
1for (int i = 0; i < 2; i++) {
2 Video video = videoMapper.selectById(44);
3 System.out.println(video);
4 }
5
6
7
8DEBUG [main] - ==> Preparing: select * from video where id = ?
9DEBUG [main] - ==> Parameters: 44(Integer)
10DEBUG [main] - <== Total: 1
11Video{id=44, title='2020版全新JDK8~JDK13全套新特性教程', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8.png', price=3980, createTime=Fri Oct 11 06:14:00 CST 2019, point=9.3}
12Video{id=44, title='2020版全新JDK8~JDK13全套新特性教程', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8.png', price=3980, createTime=Fri Oct 11 06:14:00 CST 2019, point=9.3}
二级缓存
- 简介：二级缓存是 namespace 级别的，多个 SqlSession 去操作同一个 namespace 下的 Mapper 的 SQL 语句，多个 SqlSession 可以共用二级缓存,如果两个 mapper 的 namespace 相同，（即使是两个 mapper，那么这两个 mapper 中执行 SQL 查询到的数据也将存在相同的二级缓存区域中，但是最后是每个 Mapper 单独的名称空间）
- 基于 PerpetualCache 的 HashMap 本地缓存，可自定义存储源，如 Ehcache/Redis 等
- 默认是没有开启二级缓存
- 操作流程：第一次调用某个 namespace 下的 SQL 去查询信息，查询到的信息会存放该 mapper 对应的二级缓存区域。 第二次调用同个 namespace 下的 mapper 映射文件中相同的 SQL 去查询信息，会去对应的二级缓存内取结果
- 失效策略：执行同个 namespace 下的 mapepr 映射文件中增删改 SQL，并执行了 commit 操作,会清空该二级缓存
- 注意：实现二级缓存的时候，MyBatis 建议返回的 POJO 是可序列化的， 也就是建议实现 Serializable 接口
- 缓存淘汰策略：会使用默认的 LRU 算法来收回（最近最少使用的）
- 如何开启某个二级缓存 mapper.xml 里面配置
1<!--开启mapper的namespace下的二级缓存-->
2 <!--
3 eviction:代表的是缓存回收策略，常见下面两种。
4 (1) LRU,最近最少使用的，一处最长时间不用的对象
5 (2) FIFO,先进先出，按对象进入缓存的顺序来移除他们
6
7 flushInterval:刷新间隔时间，单位为毫秒，这里配置的是100秒刷新，如果不配置它，当SQL被执行的时候才会去刷新缓存。
8
9 size:引用数目，代表缓存最多可以存储多少个对象，设置过大会导致内存溢出
10
11 readOnly:只读，缓存数据只能读取而不能修改，默认值是false
12 -->
13<cache eviction="LRU" flushInterval="100000" readOnly="true" size="1024"/>
14
15
16全局配置：
17<settings>
18<!--这个配置使全局的映射器(二级缓存)启用或禁用缓存，全局总开关，这里关闭，mapper中开启了也没用-->
19 <setting name="cacheEnabled" value="true" />
20</settings>
演示
1public class SqlSessionCacheDemo {
2
3
4 public static void main(String [] args) throws IOException {
5
6 String resouce = "config/mybatis-config.xml";
7
8 //读取配置文件
9 InputStream inputStream = Resources.getResourceAsStream(resouce);
10
11 //构建Session工厂
12 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
13
14 //获取Session
15 try{
16
17 SqlSession sqlSession1 = sqlSessionFactory.openSession();
18 VideoMapper videoMapper1 = sqlSession1.getMapper(VideoMapper.class);
19 Video video1 = videoMapper1.selectById(44);
20 System.out.println(video1.toString());
21 sqlSession1.commit();
22
23 SqlSession sqlSession2 = sqlSessionFactory.openSession();
24 VideoMapper videoMapper2 = sqlSession1.getMapper(VideoMapper.class);
25 Video video2 = videoMapper2.selectById(44);
26 System.out.println(video2.toString());
27 sqlSession2.commit();
28
29
30 }catch (Exception e){
31 e.printStackTrace();
32 }
33
34 }
35
36}
37
38
39DEBUG [main] - Cache Hit Ratio [net.xdclass.online_class.dao.VideoMapper]: 0.0
40DEBUG [main] - ==> Preparing: select * from video where id = ?
41DEBUG [main] - ==> Parameters: 44(Integer)
42DEBUG [main] - <== Total: 1
43Video{id=44, title='2020版全新JDK8~JDK13全套新特性教程', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8.png', price=3980, createTime=Fri Oct 11 06:14:00 CST 2019, point=9.3}
44DEBUG [main] - Cache Hit Ratio [net.xdclass.online_class.dao.VideoMapper]: 0.5
45Video{id=44, title='2020版全新JDK8~JDK13全套新特性教程', summary='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8_detail.png', coverImg='https://xd-video-pc-img.oss-cn-beijing.aliyuncs.com/xdclass_pro/video/jdk8/jdk8.png', price=3980, createTime=Fri Oct 11 06:14:00 CST 2019, point=9.3}
一级缓存和二级缓存使用顺序
- 优先查询二级缓存-》查询一级缓存-》数据库
如果需要控制全局 mapper 里面某个方法不使用缓存，可以配置 useCache="false"
1<select id="selectById" parameterType="java.lang.Integer" resultType="Video" useCache="false">
2 select <include refid="base_video_field"/> from video where id = #{video_id,jdbcType=INTEGER}
3</select>
懒加载
- 什么是懒加载： 按需加载，先从单表查询，需要时再从关联表去关联查询，能大大提高数据库性能,并不是所有场景下使用懒加载都能提高效率
- MyBatis 懒加载： resultMap 里面的 association、collection 有延迟加载功能
1<!--全局参数设置-->
2<settings>
3 <!--延迟加载总开关-->
4 <setting name="lazyLoadingEnabled" value="true"/>
5 <!--将aggressiveLazyLoading设置为false表示按需加载，默认为true-->
6 <setting name="aggressiveLazyLoading" value="false"/>
7</settings>
VideoOrderMapper.xml
1<resultMap id="VideoOrderResultMapLazy" type="VideoOrder">
2 <id column="id" property="id"/>
3
4 <result column="user_id" property="userId"/>
5 <result column="out_trade_no" property="outTradeNo"/>
6 <result column="create_time" property="createTime"/>
7 <result column="state" property="state"/>
8 <result column="total_fee" property="totalFee"/>
9 <result column="video_id" property="videoId"/>
10 <result column="video_title" property="videoTitle"/>
11 <result column="video_img" property="videoImg"/>
12
13 <!--
14 select： 指定延迟加载需要执行的statement id
15 column： 和select查询关联的字段
16 -->
17 <association property="user" javaType="User" column="user_id" select="findUserByUserId"/>
18
19 </resultMap>
20
21 <!--一对一管理查询订单， 订单内部包含用户属性 懒加载 -->
22 <select id="queryVideoOrderListLazy" resultMap="VideoOrderResultMapLazy">
23
24 select
25 o.id id,
26 o.user_id ,
27 o.out_trade_no,
28 o.create_time,
29 o.state,
30 o.total_fee,
31 o.video_id,
32 o.video_title,
33 o.video_img
34 from video_order o
35
36 </select>
37
38 <select id="findUserByUserId" resultType="User">
39 select * from user where id=#{id}
40 </select>
VideoOrderMapper
1/**
2 * 查询全部订单，关联用户信息, 懒加载
3 * @return
4 */
5 List<VideoOrder> queryVideoOrderListLazy();
SqlSessionDemo
1//懒加载
2 List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderListLazy();
3 for (VideoOrder videoOrder : videoOrderList) {
4 //这个不会触发懒加载查询
5 System.out.println(videoOrder.getVideoTitle());
6 //这里会触发懒加载查询
7 System.out.println(videoOrder.getUser().getName());
8 }
控制台输出
1DEBUG [main] - ==> Preparing: select o.id id, o.user_id , o.out_trade_no, o.create_time, o.state, o.total_fee, o.video_id, o.video_title, o.video_img from video_order o
2DEBUG [main] - ==> Parameters:
3DEBUG [main] - <== Total: 6
4DEBUG [main] - ==> Preparing: select * from user where id=?
5DEBUG [main] - ==> Parameters: 5(Integer)
6DEBUG [main] - <== Total: 1
7大乔
8DEBUG [main] - ==> Preparing: select * from user where id=?
9DEBUG [main] - ==> Parameters: 6(Integer)
10DEBUG [main] - <== Total: 1
11Wiggin
12Wiggin
13Wiggin
14大乔
15DEBUG [main] - ==> Preparing: select * from user where id=?
16DEBUG [main] - ==> Parameters: 4(Integer)
17DEBUG [main] - <== Total: 1
18网二