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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>net.xdclas</groupId>
<artifactId>xd-mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.5</version>
</dependency>
</dependencies>
</project>
resources/config/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.31.101:50000/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/VideoMapper.xml"/>
</mappers>
</configuration>
/resources/mapper/VideoMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="net.xdclass.online_class.dao.VideoMapper">
<select id="selectById" resultType="net.xdclass.online_class.domain.Video">
select * from video where id = #{video_id}
</select>
</mapper>
VideoMapper
package net.xdclass.online_class.dao;
import net.xdclass.online_class.domain.Video;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface VideoMapper {
/**
* 根据视频 id 查找视频对象
* @param videoId
* @return
*/
Video selectById(@Param("video_id") int videoId);
/**
* 查询全部视频列表
*/
@Select("select * from video")
List<Video> selectList();
}
Video
package net.xdclass.online_class.domain;
import java.util.Date;
public class Video {
private int id;
private String titile;
private String summary;
private String coverImg;
private int price;
private Date createTime;
private double point;
public String getSummary() {
return summary;
}
public void setSummary(String summary) {
this.summary = summary;
}
public String getCoverImg() {
return coverImg;
}
public void setCoverImg(String coverImg) {
this.coverImg = coverImg;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public double getPoint() {
return point;
}
public void setPoint(double point) {
this.point = point;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitile() {
return titile;
}
public void setTitile(String titile) {
this.titile = titile;
}
@Override
public String toString() {
return "Video{" +
"id=" + id +
", titile='" + titile + '\'' +
", summary='" + summary + '\'' +
", coverImg='" + coverImg + '\'' +
", price=" + price +
", createTime=" + createTime +
", point=" + point +
'}';
}
}
SqlSessionDemo
package net.xdclass.online_class;
import net.xdclass.online_class.dao.VideoMapper;
import net.xdclass.online_class.domain.Video;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class SqlSessionDemo {
public static void main(String[] args) throws IOException {
String resource = "config/mybatis-config.xml";
//读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
//构建Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取Session
try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
VideoMapper videoMapper = sqlSession.getMapper(VideoMapper.class);
//xml方式
Video video = videoMapper.selectById(44);
//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}
//注解方式
List<Video> videoList = videoMapper.selectList();
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
', 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}]
}
}
}
控制台打印sql
-
内置的日志工厂提供日志功能, 使用log4j配置打印sql,添加依赖
<dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.5</version> </dependency> -
在应用的classpath中创建名称为
log4j.properties的文件log4j.rootLogger=ERROR, stdout # TRACE会打印更多信息 log4j.logger.net.xdclass=DEBUG # 细化到打印某个mapper # log4j.logger.net.xdclass.online_class.dao.VideoMapper.selectById=TRACE log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
参数别名
常用的查询
-
默认参数查询
-
单个参数，可以使用别名，也可以使用默认的名称，默认名称的话可以随意
-
使用参数别名
Video selectById(@Param("video_id") int videoId); select * from video where id = #{video_id}
-
-
模糊查询，mysql自带函数使用
-
多参数查询一定要使用别名@Param注解
/**
* 根据评分和标题模糊查询
*/
List<Video> selectByPointAndTitleLike(@Param("point") double point, @Param("title") String title);
/**
* video.xml
*/
<select id="selectByPointAndTitleLike" resultType="net.xdclass.online_class.domain.Video">
select * from video where point=#{point} and title like concat('%',#{title},'%')
</select>
驼峰字段映射java对象和数据库字段
数据库的字段名：_
Java对象的数据:mM
- 方法一：使用别名
- 方法二：Mybatis自带配置（加在文件顶部）
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 下换线转驼峰 -->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.31.101:50000/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/VideoMapper.xml"/>
</mappers>
</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列席对比
JDBC Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean
BOOLEAN boolean
TINYINT byte
SMALLINT short
INTEGER INTEGER
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB Clob
BLOB Blob
ARRAY Array
DISTINCT mapping of underlying type
STRUCT Struct
REF Ref
DATALINK java.net.URL
新增记录并获取主键
新增记录 useGeneratedKeys="true" keyProperty="id" keyColumn="id"
<insert id="add" parameterType="net.xdclass.online_class.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
VALUES (#{title,jdbcType=VARCHAR},#{summary,jdbcType=VARCHAR},#{coverImg,jdbcType=VARCHAR},
#{price,jdbcType=INTEGER},#{createTime,jdbcType=TIMESTAMP},#{point,jdbcType=DOUBLE});
</insert>
/**
* 新增一条视频记录
*
* @param video
* @return
*/
int add(Video video);
//新增一条记录
Video video = new Video();
video.setTitle("面试专题");
video.setCoverImg("xdclass/aaa.png");
video.setPoint(9.4);
video.setCreateTime(new Date());
video.setPrice(9900);
video.setSummary("这是面试专题概要");
videoMapper.add(video);
//mybatis插入完成会帮忙将主键自动回写到POJO的id属性中
System.out.println(video.getId());
foreach批量插入
- 批量插入多条视频记录
- foreach: 用于循环拼接的内置标签，常用于 批量新增、in查询等常见
包含以下属性：
collection：必填，值为要迭代循环的集合类型，情况有多种
入参是List类型的时候，collection属性值为list
入参是Map类型的时候，collection 属性值为map的key值
item：每一个元素进行迭代时的别名
index：索引的属性名，在集合数组情况下值为当前索引值，当迭代对象是map时，这个值是map的key
open：整个循环内容的开头字符串
close：整个循环内容的结尾字符串
separator: 每次循环的分隔符
<insert id="addBatch" parameterType="net.xdclass.online_class.domain.Video">
INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
VALUES
<foreach collection="list" item="video" index="index" separator=",">
(#{video.title,jdbcType=VARCHAR},#{video.summary,jdbcType=VARCHAR},#{video.coverImg,jdbcType=VARCHAR},
#{video.price,jdbcType=INTEGER},#{video.createTime,jdbcType=TIMESTAMP},#{video.point,jdbcType=DOUBLE})
</foreach>
</insert>
/**
* 批量插入
* @return
* @param list
*/
int addBatch(List<Video> list);
//新增一条记录
Video video = new Video();
video.setTitle("面试专题");
video.setCoverImg("xdclass/aaa.png");
video.setPoint(9.4);
video.setCreateTime(new Date());
video.setPrice(9900);
video.setSummary("这是面试专题概要");
videoMapper.add(video);
Video video2 = new Video();
video.setTitle("面试专题2");
video.setCoverImg("xdclass/aaa.png");
video.setPoint(9.2);
video.setCreateTime(new Date());
video.setPrice(9200);
video.setSummary("这是面试专题概要");
videoMapper.add(video);
List<Video> list = new ArrayList<>();
list.add(video);
list.add(video2);
int rows = videoMapper.addBatch(list);
System.out.println(rows); //2
更新
问题：存在其他不想被更新的字段却置为null或者默认值了
<update id="updateVideo" parameterType="net.xdclass.online_class.domain.Video">
update video
set
title = #{title,jdbcType=VARCHAR},
summary = #{summary,jdbcType=VARCHAR},
cover_img = #{coverImg,jdbcType=VARCHAR},
price = #{price,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
point = #{point,jdbcType=DOUBLE}
where
id = #{id}
</update>
/**
* 更新操作
*/
int updateVideo(Video video);
Video video = new Video();
video.setId(56);
video.setTitle("面试专题xxx");
video.setCoverImg("xdclass/xxx.pngxxx");
videoMapper.updateVideo(video);
选择更新
可以选择更新非空字段
if 标签可以通过判断传入的值来确定查询条件，test 指定一个OGNL表达式
常见写法
//当前字段符合条件才更新这个字段的值
<if test='title != null and id == 87 '> title = #{title}, </if>
<if test="title!=null"> title = #{title}, </if>
<update id="updateVideoSelective" parameterType="net.xdclass.online_class.domain.Video">
update video
<trim prefix="set" suffixOverrides=",">
<if test="title != null"> title = #{title,jdbcType=VARCHAR},</if>
<if test="summary != null"> summary = #{summary,jdbcType=VARCHAR},</if>
<if test="coverImg != null"> cover_img = #{coverImg,jdbcType=VARCHAR},</if>
<if test="price !=0"> price = #{price,jdbcType=INTEGER},</if>
<if test="createTime != null"> create_time = #{createTime,jdbcType=TIMESTAMP},</if>
<!--一定要看POJO的类里面是基本数据类型，还是包装数据类型 -->
<if test="point != null"> point = #{point,jdbcType=DOUBLE},</if>
</trim>
where
id = #{id}
</update>
/**
* 动态选择更新
* @param video
* @return
*/
int updateVideoSelective(Video video);
Video video = new Video();
video.setId(55);
video.setTitle("面试专题xxx");
video.setCoverImg("xdclass/xxx.pngxxx");
videoMapper.updateVideoSelective(video);
DEBUG [main] - ==> Preparing: update video set title = ?, cover_img = ? where id = ?
DEBUG [main] - ==> Parameters: 面试专题xxx(String), xdclass/xxx.pngxxx(String), 55(Integer)
DEBUG [main] - <== Updates: 1
删除
为什么要转义字符:
由于MyBatis的sql写在XML里面， 有些sql的语法符号和xml里面的冲突
大于等于
小于等于
delete删除语法
删除某个时间段之后，且金额小于10000元的数据
<delete id="deleteByCreateTimeAndPrice" parameterType="java.util.Map">
delete from video where create_time <![CDATA[ > ]]> #{createTime} and price <![CDATA[ < ]]> #{price}
</delete>
/**
* 根据时间和价格删除
* @return
*/
int deleteByCreateTimeAndPrice(Map<String,Object> map);
Map<String, Object> map = new HashMap<>();
map.put("createTime", "2021-01-11 09:33:21");
map.put("price", 10000);
int rows = videoMapper.deleteByCreateTimeAndPrice(map);
System.out.println(rows);
DEBUG [main] - ==> Preparing: delete from video where create_time > ? and price < ?
DEBUG [main] - ==> Parameters: 2021-01-11 09:33:21(String), 10000(Integer)
DEBUG [main] - <== Updates: 2
2
mybatis-config.xml常见属性
configuration（配置）
properties（属性）
settings（设置）
typeAliases（类型别名）
typeHandlers（类型处理器）
objectFactory（对象工厂）
plugins（插件，少用）
environments（环境配置，不配多环境，基本在Spring里面配置）
environment（环境变量）
transactionManager（事务管理器）
dataSource（数据源）
databaseIdProvider（数据库厂商标识）
mappers（映射器）
官方文档：https://mybatis.org/mybatis-3/zh/configuration.html#
typeAlias别名的使用
类型别名:给类取个别名，可以不用输入类的全限定名
<typeAliases>
<typeAlias type="net.xdclass.online_class.domain.Video" alias="Video"/>
</typeAliases>
<!-- 不用写全限定名 net.xdclass.online_class.domain.Video 写 Video即可-->
<select id="selectByPointAndTitleLike" resultType="Video">
select * from video where point=#{point} and title like concat('%',#{title},'%')
</select>
包扫描：包下的全部类的名字作为类名，上面需要配置多个，包更加简单
<typeAliases>
<package name="net.xdclass.online_class.domain"/>
</typeAliases>
<!-- 不用写全限定名 net.xdclass.online_class.domain.Video 写 Video即可-->
<select id="selectByPointAndTitleLike" resultType="Video">
select * from video where point=#{point} and title like concat('%',#{title},'%')
</select>
本身就内置：很多别名，比如Integer、String、List、Map 等
sql片段
根据业务需要，自定制要查询的字段，并可以复用
<sql id="base_video_field">
id,title,summary,cover_img
</sql>
<select id="selectById" parameterType="java.lang.Integer" resultType="Video">
select <include refid="base_video_field"/> from video where id = # {video_id,jdbcType=INTEGER}
</select>
复杂sql查询
Mybatis的SQL语句返回结果有两种
-
resultType
- 查询出的字段在相应的pojo中必须有和它相同的字段对应，或者基本数据类型
- 适合简单查询
-
resultMap
- 需要自定义字段，或者多表查询，一对多等关系，比resultType更强大
- 适合复杂查询
resultMap单表查询
<resultMap id="VideoResultMap" type="Video">
<!--
id 指定查询列的唯一标示
column 数据库字段的名称
property pojo类的名称
-->
<id column="id" property="id" jdbcType="INTEGER" />
<result column="video_tile" property="title" jdbcType="VARCHAR" />
<result column="summary" property="summary" jdbcType="VARCHAR" />
<result column="cover_img" property="coverImg" jdbcType="VARCHAR" />
</resultMap>
<select id="selectBaseFieldByIdWithResultMap" resultMap="VideoResultMap">
select id , title as video_tile, summary, cover_img from video where id = #{video_id}
</select>
/**
* 根据id查询视频
* @param id
* @return
*/
Video selectBaseFieldByIdWithResultMap(@Param("video_id") int id);
Video video = videoMapper.selectBaseFieldByIdWithResultMap(45);
System.out.println(video);
DEBUG [main] - ==> Preparing: select id , title as video_tile, summary, cover_img from video where id = ?
DEBUG [main] - ==> Parameters: 45(Integer)
DEBUG [main] - <== Total: 1
Video{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
package net.xdclass.online_class.domain;
import java.util.Date;
/**
* `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
* `name` varchar(128) DEFAULT NULL COMMENT '昵称',
* `pwd` varchar(124) DEFAULT NULL COMMENT '密码',
* `head_img` varchar(524) DEFAULT NULL COMMENT '头像',
* `phone` varchar(64) DEFAULT '' COMMENT '手机号',
* `create_time` datetime DEFAULT NULL COMMENT '创建时间',
*/
public class User {
private int id;
private String name;
private String pwd;
private String headImg;
private String phone;
private Date createTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getHeadImg() {
return headImg;
}
public void setHeadImg(String headImg) {
this.headImg = headImg;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", headImg='" + headImg + '\'' +
", phone='" + phone + '\'' +
", createTime=" + createTime +
'}';
}
}
VideoOrder
package net.xdclass.online_class.domain;
import java.util.Date;
/**
* `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
* `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
* `state` int(11) DEFAULT NULL COMMENT '0表示未支付，1表示已支付',
* `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
* `total_fee` int(11) DEFAULT NULL COMMENT '支付金额，单位分',
* `video_id` int(11) DEFAULT NULL COMMENT '视频主键',
* `video_title` varchar(256) DEFAULT NULL COMMENT '视频标题',
* `video_img` varchar(256) DEFAULT NULL COMMENT '视频图片',
* `user_id` int(12) DEFAULT NULL COMMENT '用户id',
*/
public class VideoOrder {
private int id;
private String outTradeNo;
private int state;
private Date createTime;
private int totalFee;
private int videoId ;
private String videoTitle;
private String videoImg;
private int userId;
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOutTradeNo() {
return outTradeNo;
}
public void setOutTradeNo(String outTradeNo) {
this.outTradeNo = outTradeNo;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getTotalFee() {
return totalFee;
}
public void setTotalFee(int totalFee) {
this.totalFee = totalFee;
}
public int getVideoId() {
return videoId;
}
public void setVideoId(int videoId) {
this.videoId = videoId;
}
public String getVideoTitle() {
return videoTitle;
}
public void setVideoTitle(String videoTitle) {
this.videoTitle = videoTitle;
}
public String getVideoImg() {
return videoImg;
}
public void setVideoImg(String videoImg) {
this.videoImg = videoImg;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "VideoOrder{" +
"id=" + id +
", outTradeNo='" + outTradeNo + '\'' +
", state=" + state +
", createTime=" + createTime +
", totalFee=" + totalFee +
", videoId=" + videoId +
", videoTitle='" + videoTitle + '\'' +
", videoImg='" + videoImg + '\'' +
", userId=" + userId +
", user=" + user +
'}';
}
}
VideoOrderMapper
package net.xdclass.online_class.dao;
import net.xdclass.online_class.domain.VideoOrder;
import java.util.List;
public interface VideoOrderMapper {
/**
* 查询全部订单，关联用户信息
* @return
*/
List<VideoOrder> queryVideoOrderList();
}
VideoOrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="net.xdclass.online_class.dao.VideoOrderMapper">
<resultMap id="VideoOrderResultMap" type="VideoOrder">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="out_trade_no" property="outTradeNo"/>
<result column="create_time" property="createTime"/>
<result column="state" property="state"/>
<result column="total_fee" property="totalFee"/>
<result column="video_id" property="videoId"/>
<result column="video_title" property="videoTitle"/>
<result column="video_img" property="videoImg"/>
<!--
association 配置属性一对一
property 对应videoOrder里面的user属性名
javType 这个属性的类型
-->
<association property="user" javaType="User">
<id property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="headImg" column="head_img"/>
<result property="createTime" column="create_time"/>
<result property="phone" column="phone"/>
</association>
</resultMap>
<!--一对一管理查询订单， 订单内部包含用户属性-->
<select id="queryVideoOrderList" resultMap="VideoOrderResultMap">
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
</select>
</mapper>
查询结果
List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderList();
System.out.println(videoOrderList);
DEBUG [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
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 6
[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
package net.xdclass.online_class.domain;
import java.util.Date;
import java.util.List;
/**
* `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
* `name` varchar(128) DEFAULT NULL COMMENT '昵称',
* `pwd` varchar(124) DEFAULT NULL COMMENT '密码',
* `head_img` varchar(524) DEFAULT NULL COMMENT '头像',
* `phone` varchar(64) DEFAULT '' COMMENT '手机号',
* `create_time` datetime DEFAULT NULL COMMENT '创建时间',
*/
public class User {
private int id;
private String name;
private String pwd;
private String headImg;
private String phone;
private Date createTime;
//List
private List<VideoOrder> videoOrderList;
public List<VideoOrder> getVideoOrderList() {
return videoOrderList;
}
public void setVideoOrderList(List<VideoOrder> videoOrderList) {
this.videoOrderList = videoOrderList;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getHeadImg() {
return headImg;
}
public void setHeadImg(String headImg) {
this.headImg = headImg;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", headImg='" + headImg + '\'' +
", phone='" + phone + '\'' +
", createTime=" + createTime +
", videoOrderList=" + videoOrderList +
'}';
}
}
VideoOrderMapper.xml
<resultMap id="UserOrderResultMap" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="headImg" column="head_img"/>
<result property="createTime" column="create_time"/>
<result property="phone" column="phone"/>
<!--
property 填写pojo类中集合类属性的名称
ofType 集合里面的pojo对象
-->
<collection property="videoOrderList" ofType="VideoOrder">
<!--配置主键，管理order的唯一标识-->
<id column="order_id" property="id"/>
<result column="user_id" property="userId"/>
<result column="out_trade_no" property="outTradeNo"/>
<result column="create_time" property="createTime"/>
<result column="state" property="state"/>
<result column="total_fee" property="totalFee"/>
<result column="video_id" property="videoId"/>
<result column="video_title" property="videoTitle"/>
<result column="video_img" property="videoImg"/>
</collection>
</resultMap>
<select id="queryUserOrder" resultMap="UserOrderResultMap">
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
</select>
VideoOrderMapper
package net.xdclass.online_class.dao;
import net.xdclass.online_class.domain.User;
import net.xdclass.online_class.domain.VideoOrder;
import java.util.List;
public interface VideoOrderMapper {
/**
* 查询全部订单，关联用户信息
* @return
*/
List<VideoOrder> queryVideoOrderList();
/**
* 查询全部用户的全部订单
* @return
*/
List<User> queryUserOrder();
}
测试结果
List<User> userList = videoOrderMapper.queryUserOrder();
System.out.println(userList);
DEBUG [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
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 10
[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 映射的一个集合列表，处理的是一对多的关联关系。
<!-- column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
<resultMap id="唯一的标识" type="映射的pojo对象">
<id column="表的主键字段,或查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
<result column="表的一个字段" jdbcType="字段类型" property="映射到pojo对象的一个属性"/>
<association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
<id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
<result column="表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
</association>
<!-- 集合中的property 需要为oftype定义的pojo对象的属性-->
<collection property="pojo的集合属性名称" ofType="集合中单个的pojo对象类型">
<id column="集合中pojo对象对应在表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
<result column="任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />
</collection>
</resultMap>
一级缓存
-
什么是缓存
- 程序经常要调用的对象存在内存中,方便其使用时可以快速调用,不必去数据库或者其他持久化设备中查询，主要就是提高性能
-
Mybatis一级缓存（默认是开启的）
- 简介：一级缓存的作用域是SQLSession，同一个SqlSession中执行相同的SQL查询(相同的SQL和参数)，第一次会去查询数据库并写在缓存中，第二次会直接从缓存中取
- 基于PerpetualCache 的 HashMap本地缓存
- 默认开启一级缓存
-
失效策略：当执行SQL时候两次查询中间发生了增删改的操作，即insert、update、delete等操作commit后会清空该SQLSession缓存; 比如sqlsession关闭，或者清空等
演示效果（同一个SQLSession中两次查询只发出了一次SQL查询）
for (int i = 0; i < 2; i++) {
Video video = videoMapper.selectById(44);
System.out.println(video);
}
DEBUG [main] - ==> Preparing: select * from video where id = ?
DEBUG [main] - ==> Parameters: 44(Integer)
DEBUG [main] - <== Total: 1
Video{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}
Video{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里面配置
<!--开启mapper的namespace下的二级缓存-->
<!--
eviction:代表的是缓存回收策略，常见下面两种。
(1) LRU,最近最少使用的，一处最长时间不用的对象
(2) FIFO,先进先出，按对象进入缓存的顺序来移除他们
flushInterval:刷新间隔时间，单位为毫秒，这里配置的是100秒刷新，如果不配置它，当SQL被执行的时候才会去刷新缓存。
size:引用数目，代表缓存最多可以存储多少个对象，设置过大会导致内存溢出
readOnly:只读，缓存数据只能读取而不能修改，默认值是false
-->
<cache eviction="LRU" flushInterval="100000" readOnly="true" size="1024"/>
全局配置：
<settings>
<!--这个配置使全局的映射器(二级缓存)启用或禁用缓存，全局总开关，这里关闭，mapper中开启了也没用-->
<setting name="cacheEnabled" value="true" />
</settings>
演示
public class SqlSessionCacheDemo {
public static void main(String [] args) throws IOException {
String resouce = "config/mybatis-config.xml";
//读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resouce);
//构建Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取Session
try{
SqlSession sqlSession1 = sqlSessionFactory.openSession();
VideoMapper videoMapper1 = sqlSession1.getMapper(VideoMapper.class);
Video video1 = videoMapper1.selectById(44);
System.out.println(video1.toString());
sqlSession1.commit();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
VideoMapper videoMapper2 = sqlSession1.getMapper(VideoMapper.class);
Video video2 = videoMapper2.selectById(44);
System.out.println(video2.toString());
sqlSession2.commit();
}catch (Exception e){
e.printStackTrace();
}
}
}
DEBUG [main] - Cache Hit Ratio [net.xdclass.online_class.dao.VideoMapper]: 0.0
DEBUG [main] - ==> Preparing: select * from video where id = ?
DEBUG [main] - ==> Parameters: 44(Integer)
DEBUG [main] - <== Total: 1
Video{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}
DEBUG [main] - Cache Hit Ratio [net.xdclass.online_class.dao.VideoMapper]: 0.5
Video{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"
<select id="selectById" parameterType="java.lang.Integer" resultType="Video" useCache="false">
select <include refid="base_video_field"/> from video where id = #{video_id,jdbcType=INTEGER}
</select>
懒加载
- 什么是懒加载： 按需加载，先从单表查询，需要时再从关联表去关联查询，能大大提高数据库性能,并不是所有场景下使用懒加载都能提高效率
- Mybatis懒加载： resultMap里面的association、collection有延迟加载功能
<!--全局参数设置-->
<settings>
<!--延迟加载总开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--将aggressiveLazyLoading设置为false表示按需加载，默认为true-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
VideoOrderMapper.xml
<resultMap id="VideoOrderResultMapLazy" type="VideoOrder">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="out_trade_no" property="outTradeNo"/>
<result column="create_time" property="createTime"/>
<result column="state" property="state"/>
<result column="total_fee" property="totalFee"/>
<result column="video_id" property="videoId"/>
<result column="video_title" property="videoTitle"/>
<result column="video_img" property="videoImg"/>
<!--
select： 指定延迟加载需要执行的statement id
column： 和select查询关联的字段
-->
<association property="user" javaType="User" column="user_id" select="findUserByUserId"/>
</resultMap>
<!--一对一管理查询订单， 订单内部包含用户属性 懒加载 -->
<select id="queryVideoOrderListLazy" resultMap="VideoOrderResultMapLazy">
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
</select>
<select id="findUserByUserId" resultType="User">
select * from user where id=#{id}
</select>
VideoOrderMapper
/**
* 查询全部订单，关联用户信息, 懒加载
* @return
*/
List<VideoOrder> queryVideoOrderListLazy();
SqlSessionDemo
//懒加载
List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderListLazy();
for (VideoOrder videoOrder : videoOrderList) {
//这个不会触发懒加载查询
System.out.println(videoOrder.getVideoTitle());
//这里会触发懒加载查询
System.out.println(videoOrder.getUser().getName());
}
控制台输出
DEBUG [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
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 6
DEBUG [main] - ==> Preparing: select * from user where id=?
DEBUG [main] - ==> Parameters: 5(Integer)
DEBUG [main] - <== Total: 1
大乔
DEBUG [main] - ==> Preparing: select * from user where id=?
DEBUG [main] - ==> Parameters: 6(Integer)
DEBUG [main] - <== Total: 1
Wiggin
Wiggin
Wiggin
大乔
DEBUG [main] - ==> Preparing: select * from user where id=?
DEBUG [main] - ==> Parameters: 4(Integer)
DEBUG [main] - <== Total: 1
网二