mybatis笔记
mybatis 学习笔记
转载请注明来源 https://www.cnblogs.com/sogeisetsu/ 原文可以到的github下载 [https://github.com/sogeisetsu/myBlog/blob/master/2020-4-18 mybaits 学习笔记 171812.md](https://github.com/sogeisetsu/myBlog/blob/master/2020-4-18 mybaits 学习笔记 171812.md)
0.简介
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
1.下载安装
GitHub:mybatis3.5.2
中文文档:mybatis中文文档
maven仓库
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
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>org.suyuesheng</groupId>
<artifactId>osuo</artifactId>
<version>1.0-SNAPSHOT</version>
<repositories>
<repository>
<id>alimaven</id>
<name>aliyun maven</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.2.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.2.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.2.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.1.2.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>utf-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
sql
create database if not exists mybatisstudy character set utf8;
show variables like '%chara%';
use mybatisstudy;
create table user (
id int not null primary key ,
name varchar(20) not null ,
pwd varchar(20) not null
) character set utf8;
desc user;
insert into user values (1,'老张','1234567');
insert into user values (2,'laoliu','12233');
insert into user values (3,'思思','12233');
SELECT * FROM user;
1.1创建模块
mybatis.config.xml
这是mybatis的配置文件
<?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://localhost:3307/mybatisstudy?characterEncoding=utf-8&useUnicode=true"/>
<property name="username" value="root"/>
<property name="password" value="su15990904343"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
备注:在xml文件中 &
需要用 &
来表示,如果 mysql-connector
的版本大于6,driver应该是com.mysql.cj.jdbc.Driver
。
sqlsession 的util文件
package cn.ssm.suyuesheng.utils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class SqlsessionUtil {
private static SqlSession sqlSession;
private static SqlSessionFactory sqlSessionFactory;
static {
InputStream resourceAsStream = SqlsessionUtil.class.getResourceAsStream("mybatis.config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession();
}
public static SqlSession getSqlSession() {
return sqlSession;
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
mapper的配置 跳转到mapper详细配置
<?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="cn.ssm.suyuesheng.Dao.UserDao">
<select id="findAll" resultType="cn.ssm.suyuesheng.domain.User">
select * from user
</select>
</mapper>
注意:namespace
和resultType
应该把路径写全,id
对应的是方法名称,namespace
对应的是DAO类(在mybatis里叫Mapper类)。在这里我将UserMapper.xml
放在了src/main/resourse里面,如果把UserMapper.xml
放在src/main/java里面需要在pom.xml里面配置过滤
过滤这样配置
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
filtering
默认是false,故不配置filtering
。待到需要的时候可以自行配置
2 .使用模块
package cn.ssm.suyuesheng.Dao;
import cn.ssm.suyuesheng.domain.User;
import cn.ssm.suyuesheng.utils.SqlsessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void TestOne(){
//方法一
SqlSession sqlSession = SqlsessionUtil.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> users = mapper.findAll();
System.out.println(users);
sqlSession.close();
}
@Test
public void TestTwo(){
//方式二
SqlSession sqlSession = SqlsessionUtil.getSqlSession();
List<User> list = sqlSession.selectList("cn.ssm.suyuesheng.Dao.UserDao.findAll");
for (User user : list) {
System.out.println(user);
}
}
}
2.1mapper(映射器)详细配置
参考资料 https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#insert_update_and_delete
2.1.1 crud的实现
接口
package cn.ssm.suyuesheng.Dao;
import cn.ssm.suyuesheng.domain.User;
import java.util.List;
public interface UserDao {
List<User> findAll();
User findById(int id);
boolean insertUser(User user);
boolean update(User user);
boolean remove(int id);
}
这个接口有增查删改等功能,功能的实现要到对应mapper的xml文件去声明
2.1.2UserMapper.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="cn.ssm.suyuesheng.Dao.UserDao">
<!-- findAll方法-->
<select id="findAll" resultType="cn.ssm.suyuesheng.domain.User">
select * from user
</select>
<!-- findById-->
<select id="findById" parameterType="int" resultType="cn.ssm.suyuesheng.domain.User">
select * from user where id=#{id}
</select>
<!-- insertUser-->
<insert id="insertUser" parameterType="cn.ssm.suyuesheng.domain.User">
insert into user values(#{id},#{name},#{pwd})
</insert>
<!-- update-->
<update id="update" parameterType="cn.ssm.suyuesheng.domain.User">
UPDATE user SET pwd=#{pwd},name=#{name} where id=${id}
</update>
<!-- remove-->
<delete id="remove" parameterType="int" >
delete from user where id=${id}
</delete>
</mapper>
namespace 对应接口名称
命名解析: 为了减少输入量,MyBatis对所有的命名配置元素(包括语句,结果映射,缓存等)使用了如下的命名解析规则。
- 完全限定名(比如"com.mypackage.MyMapper.selectAllThings")将被直接查找并且找到即用。
- 短名称(比如"selectAllThings")如果全局唯一也可以作为一个单独的引用。如果不唯一,有两个或两个以上的相同名称(比如“com.foo.selectAllThings ”和“com.bar.selectAllThings”), 那么使用时就会收到错误报告说短名称是不唯一的,这种情况下就必须使用完全限定名。
原文链接:https://blog.csdn.net/weixin_36210698/article/details/82992771
mapper里面的四个标签select
、update
、insert
和delete
对应sql的增查删改
具体请到 https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#insert_update_and_delete
- select 标签的几个重要参数参数
id
方法名称parameterType
参数类- 参数类的使用:参数类需要和接口中定义的一样,在标签里使用时要
${参数名称(和在接口中定义的一致)}
;如果参数类不是基础数据类型而是一个正常的类的话,在标签里使用要${类的参数名称}
。比方说resultType
是User
,User
类里面有name
、id
、pwd
三个参数。那么在标签里填写sql语句时要是用User
类的参数应该这样(假设select操作):select * from user where id=${id} and name=${name} and pwd=${pwd}
。注意${}
里面的参数名称和User
类的参数名称一致
- 参数类的使用:参数类需要和接口中定义的一样,在标签里使用时要
resultType
返回类
- update delete insert 的几个重要参数
id
方法名称parameterType
参数类
mapper.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="ssm.suyuesheng.mapper.UserMapper">
<resultMap id="UserMap" type="ssm.suyuesheng.domain.User">
<result column="pwd" property="password"/>
</resultMap>
<select id="findAll" resultMap="UserMap">
select * from user
</select>
<select id="findById" parameterType="_int" resultType="user">
select id,name,pwd from user where id=#{id}
</select>
<delete id="removeById" parameterType="_int">
delete from user where id=#{id}
</delete>
<update id="update" parameterType="user">
update user set name=#{name},pwd=#{password} where id=#{id}
</update>
</mapper>
使用定义的sql语句👇
public void TestCRUD(){
//update
SqlSession sqlSession = SqlsessionUtil.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
User user = mapper.findById(5);
user.setName("老李");
user.setPwd("hesu1244");
boolean update = mapper.update(user);
System.out.println(update);
sqlSession.commit();
mapper.findAll().forEach(System.out::println);
//delete
mapper.remove(1);
sqlSession.commit();
}
请注意:增删改操作完成后一定要 sqlSession.commit();
来提交事务
3.xml配置
https://mybatis.org/mybatis-3/zh/configuration.html#environments
配置文件的文档结构
properties
和environments
配置
<properties resource="cn/ssm/suyuesheng/mybatisconfig.properties"/>
指定了properties后可以在后面以${属性名称}
来使用如👇
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="su15990904343"/>
</dataSource>
</environment>
</environments>
properties标签内部也可定义属性,当标签定义的属性和resource文件的属性冲突时,以resource指定的文件里的属性之为准相关属性有可能会和pom.xml冲突,解决冲突的办法有两个,一个是修改.properties
文件里属性的名称,二是修改过滤规则https://blog.csdn.net/u011781521/article/details/79052725。按笔记中提到的过滤配置方案就可以避免这种情况
-
事务管理器(transactionManager)
包括JDBC和MANAGED -
datasource
数据来源,type属性,有三种内建的数据源类型(也就是type="[UNPOOLED|POOLED|JNDI]"
):可以理解为数据库连接池,type="UNPOOLED"
就是不用连接池。 -
environments
故名思意支持多个environment
(environments
中文可以翻译为工作环境)。但在工作时只能用一个environment
,用哪个可以在default属性里面设置,如下👇
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
<environment id="oracle">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
</dataSource>
</environment>
</environments>
类型别名(typeAliases)
类型别名可为 Java 类型设置一个缩写名字。 它仅用于 XML 配置,意在降低冗余的全限定类名书写。如下👇,将cn.ssm.suyuesheng.domain.User
指定为User
,这样在mapper
配置时指定类型更方便
config.xml
<!-- 指定类型别名-->
<typeAliases>
<typeAlias type="cn.ssm.suyuesheng.domain.User" alias="User"/>
</typeAliases>
Usermapper.xml
<select id="findById" parameterType="int" resultType="User">
select * from user where id=#{id}
</select>
package
指定使用的包,在config里面的aliases里package指定了使用的包之后,使用包里的类可以使用类名称首字母小写👇
config.xml
<package name="cn.ssm.suyuesheng.domain"/>
Usermapper.xml
<!-- 在config里面的aliases里package指定了使用的包之后,使用包里的类可以使用类名称首字母小写-->
<insert id="insertUser" parameterType="user">
insert into user values(#{id},#{name},#{pwd})
</insert>
也可使用注解来定义别名,使用了package
后,在没有注解的情况下,会使用 Bean 的首字母小写的非限定类名来作为它的别名。 比如 domain.blog.Author
的别名为 author
;若有注解,则别名为其注解值。(此时使用小写依然可以)👇
User.java
@Alias("author") //必须在typealias指定package后@Alias("")才会有效果
public class User {
private int id;
private String name;
private String pwd;
Usermapper.xml
<!-- 使用了`package`后,在没有注解的情况下,会使用 Bean 的首字母小写的非限定类名来作为它的别名。 比如 `domain.blog.Author` 的别名为 `author`;若有注解,则别名为其注解值。-->
<update id="update" parameterType="author">
UPDATE user SET pwd=#{pwd},name=#{name} where id=${id}
</update>
为一些基础的类型提供了默认的别名👇
设置 setting
暂时先记住下面几个
映射器 mappers
推荐使用第一个,第二个忽略,第三和第四个有两个要求 1.mapper.xml
文件必须放在src/main/java
里面;2.mapper.xml
的命名必须和接口名一样,比如接口名称UserMapper.java
那么xml的名称应该是UserMapper.xml
。_需要注意的是,虽然只有三和四的操作方式要求接口和配置文件名称一致,但是应该把这一条要求作为一个规范,也就是说以后接口和配置文件名称要保持一致 _
other
先忽略
4.生命周期和作用域
5.结果映射
<resultMap id="UserMap" type="ssm.suyuesheng.domain.User">
<result column="pwd" property="password"/>
</resultMap>
6.日志工厂
在config.xml里面配置settings 里面的setting
-
SLF4J
-
LOG4J (重点)
-
LOG4J2
-
JDK_LOGGING
-
COMMONS_LOGGING
-
STDOUT_LOGGING (重点)
-
NO_LOGGING
LOG4J
在mybatis的config.xml
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
maven导入
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
log4.properties
log4j.rootLogger=DEBUG,console,rollingFile
#表示Logger会在父Logger的appender里输出,默认为true
log4j.additivity.org.apache=true
# 控制台(console)
log4j.appender.console=org.apache.log4j.ConsoleAppender
#指定日志信息的最低输出级别
log4j.appender.console.Threshold=DEBUG
#表示所有消息都会被立即输出,设为false则不输出,默认值是true
log4j.appender.console.ImmediateFlush=true
#默认值是System.out。
log4j.appender.console.Target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%d{yyyy/MM/dd HH:mm:ss,SSS}][%c.%M]%p:%m%n
# 回滚文件(rollingFile)
log4j.appender.rollingFile=org.apache.log4j.RollingFileAppender
log4j.appender.rollingFile.Threshold=WARN
log4j.appender.rollingFile.ImmediateFlush=true
log4j.appender.rollingFile.Append=true
log4j.appender.rollingFile.File=D:/logs/log.log4j
log4j.appender.rollingFile.MaxFileSize=10mb
#指定可以产生的滚动文件的最大数,例如,设为2则可以产生logging.log4j.1,logging.log4j.2两个滚动文件和一个logging.log4j文件。
log4j.appender.rollingFile.MaxBackupIndex=50
log4j.appender.rollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.rollingFile.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n
# 日志输出级别
log4j.logger.cn.ssm=DEBUG
log4j.logger.java.sql=DEBUG
在代码里面使用
Logger logger = Logger.getLogger(Thread.currentThread().getStackTrace()[1].getClass());
public void testLogger(){
logger.error("错误");
logger.warn("haha");
}
7.分页
7.1limit(重点)
Dao
/**
* 分页
* @param map
* @return
*/
List<User> limit(Map<String,Integer> map);
mapper.xml
<select id="limit" parameterType="map" resultMap="UserMap">
select * from user limit #{startPage},#{size}
</select>
使用👇
@Test
public void testOne(){
SqlSession sqlSession = SqlsessionUtil.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
Map<String,Integer> map = new HashMap<String, Integer>();
map.put("startPage", 0);
map.put("size", 2);
List<User> users = userDao.limit(map);
System.out.println(users);
sqlSession.commit();
sqlSession.close();
// userDao.limit()
}
7.2rowbound(面向对象)
7.3pagehelper(插件)
8.面向注解
public interface UserMapper {
List<User> findAll();
@Insert("insert into user values(#{id},#{name},#{password})")
boolean insert(User user);
User findById(@Param("id") int id);
}
以后@param
如果是基本数据类型就用。
$和#的区别
https://blog.csdn.net/u013552450/article/details/72528498
9.lombok
9.1 简介
Lombok项目是一个Java库,它会自动插入编辑器和构建工具中,Lombok提供了一组有用的注释,用来消除Java类中的大量样板代码。仅五个字符(@Data)就可以替换数百行代码从而产生干净,简洁且易于维护的Java类。
来源百度百科 https://baike.baidu.com/item/Lombok/23780246?fr=aladdin
9.2 安装
在idea安装
导包
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<!-- scope 可以去掉 -->
<scope>provided</scope>
</dependency>
9.3 使用
常用注解:
@Setter :注解在类或字段,注解在类时为所有字段生成setter方法,注解在字段上时只为该字段生成setter方法。
@Getter :使用方法同上,区别在于生成的是getter方法。
@ToString :注解在类,添加toString方法。
@EqualsAndHashCode: 注解在类,生成hashCode和equals方法。
@NoArgsConstructor: 注解在类,生成无参的构造方法。
@RequiredArgsConstructor: 注解在类,为类中需要特殊处理的字段生成构造方法,比如final和被@NonNull注解的字段。
@AllArgsConstructor: 注解在类,生成包含类中所有字段的构造方法。
@Data: 注解在类,生成setter/getter、equals、canEqual、hashCode、toString方法,如为final属性,则不会为该属性生成setter方法。
@Slf4j: 注解在类,生成log变量,严格意义来说是常量。
java代码这样写👇
@Alias("student")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String password;
}
因为添加了几个注解,好多方法就自动生成了👇
10 多对一
https://www.jianshu.com/p/018c0f083501
假设学生和老师 老师一个,学生多个
10.1 sql语句
show databases ;
use mybatisstudy;
show tables ;
select * from user;
show create table user;
create table `teacher`(
`id` int primary key ,
`name` varchar(20) not null
)default charset=utf8;
insert into teacher values (1,'老程');
select * from teacher;
create table `student`(
`id` int primary key ,
`name` varchar(20) not null ,
`tid` int,
constraint `t-sid` foreign key (`tid`) references `teacher` (`id`)
)default charset=utf8;
desc student;
insert into student values(1,'老黑',1);
insert into student values(2,'老柳',1);
insert into student values(3,'老六',1);
insert into student values(4,'老苏',1);
insert into student values(5,'老李',1);
select * from student;
关系👇
10.2 环境搭建流程
pom.xml继承父pom.xml
-
建立实体类
-
@Data @NoArgsConstructor @AllArgsConstructor public class Student { private int id; private String name; /** * 关联老师 */ private Teacher teacher; private int tid; }
-
@Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; }
-
-
建立mapper接口
-
public interface StudentMapper { List<Student> findAll(); }
-
public interface TeacherMapper { }
-
-
建立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> <properties resource="mybatisconfig.properties"/> <settings> <setting name="logImpl" value="LOG4J"/> </settings> <typeAliases> <package name="ssm.suyuesheng.domain"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <package name="ssm.suyuesheng.mapper"/> </mappers> </configuration>
-
-
建立mapper.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="ssm.suyuesheng.mapper.StudentMapper"> <resultMap id="studentMap" type="ssm.suyuesheng.domain.Student"> </resultMap> <select id="findAll" resultType="student"> select * from student </select> </mapper>
-
<?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="ssm.suyuesheng.mapper.TeacherMapper"> </mapper>
-
-
实验环境搭建是否成功
-
package ssm.suyuesheng.mapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import ssm.suyuesheng.util.SqlSessionUtil; public class TestStudent { @Test public void testStart(){ SqlSession sqlSession = SqlSessionUtil.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); System.out.println(mapper.findAll()); } }
-
10.3 嵌套查询
<?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="ssm.suyuesheng.mapper.StudentMapper">
<!--按查询嵌套处理-->
<resultMap id="studentMap" type="ssm.suyuesheng.domain.Student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<result property="tid" column="tid"/>
<association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
<select id="findAll" resultMap="studentMap">
select * from student
</select>
<select id="getTeacher" resultType="teacher">
select * from teacher where id=#{tid}
</select>
<!--按结果嵌套处理-->
<resultMap id="studentMap2" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid" ></result>
<association property="teacher" javaType="teacher">
<result property="name" column="tname"></result>
<result property="id" column="tid"/>
</association>
</resultMap>
<select id="findAll2" resultMap="studentMap2">
select s.id sid,s.name sname,t.name tname,s.tid tid
from student s,teacher t
where s.tid=t.id;
</select>
</mapper>
11 一对多
接10
package ssm.suyuesheng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
collection 的用法,要指定ofType作为集合里面对象的类,javaType在collection是ArrayList,(某些情况下可以不写javatype)
<?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="ssm.suyuesheng.mapper.TeacherMapper">
<resultMap id="teacherMap" type="teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="student" resultMap="ssm.suyuesheng.mapper.StudentMapper.studentMap2">
</collection>
</resultMap>
<select id="getTeacherById" resultMap="teacherMap" parameterType="_int">
select s.id sid,s.name sname,s.tid tid,t.name tname
from student s, teacher t
where s.tid=t.id and t.id=#{tid}
</select>
</mapper>
12 .动态sql
12.1搭建环境
sql
create table `blog`(
`id` varchar(50) not null comment'博客id',
`title` varchar(100) not null comment '博客题目',
`author` varchar(30) not null comment '作者',
`creat_time` datetime not null comment '创建时间',
`views` int(30) not null comment '浏览量'
)default charset=utf8;
show tables ;
insert into blog values('01','hello world','sogei','2000-01-01',12);
insert into blog values('02','idea how to use','liusann','2004-05-01 10:05',12);
insert into blog values('03','你好世界','sogei','2006-01-01',123);
insert into blog values('04','MATLAB使用方法','liusann','2007-01-01',122);
insert into blog values('05','WHAT IS JDBC','sogei','2007-10-01',122);
insert into blog values('06','INTO JDK1.8','liusann','2004-06-01 10:05',123);
insert into blog values('07','CREATE A WEBSITE BY USING BOOTSTRAP ','sogei','2020-01-01',122);
insert into blog values('08','母猪的产后护理','本山','2010-01-01',121);
maven环境搭建
参考之前的,无非就是复制黏贴
小知识:开启驼峰命名转换
<settings>
<setting name="logImpl" value="LOG4J"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
12.2 if
<select id="queryBlog" parameterType="map" resultType="blog">
select * from mybatisstudy.blog where 1=1
<if test="title!=null">
and title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
<if test="views!=null">
and views=#{views}
</if>
<if test="creatTime!=null">
and creat_time=#{creatTime}
</if>
</select>
12.3 where
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<select id="queryBlog" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<where>
<if test="title!=null">
title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
<if test="views!=null">
and views=#{views}
</if>
<if test="creatTime!=null">
and creat_time=#{creatTime}
</if>
</where>
</select>
12.4 choose
、 when
、otherwise
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
还是上面的例子,但是策略变为:传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured 的 BLOG(这可能是管理员认为,与其返回大量的无意义随机 Blog,还不如返回一些由管理员挑选的 Blog)。
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<where>
<choose>
<when test="title!=null">
title=#{title}
</when>
<when test="author!=null">
and author=#{author}
</when>
<when test="creatTime!=null">
and creat_time=#{creatTime}
</when>
<otherwise>
and views=#{views}
</otherwise>
</choose>
</where>
</select>
即使choose里面的所有条件都满足,在生成sql语句时也只用一个,这和switch很像
12.5 set
用于更新
<update id="upadteSet" parameterType="blog">
update mybatisstudy.blog
<set>
<if test="author!=null">
author=#{author},
</if>
<if test="creatTime!=null">
creat_time=#{creatTime},
</if>
<if test="title!=null">
title=#{title},
</if>
<if test="views!=null">
views=#{views}
</if>
</set>
<where>
<choose>
<when test="id!=null">
id=#{id}
</when>
<otherwise>
id=ceshi
</otherwise>
</choose>
</where>
</update>
问题: 关于mybatis写mapper.xml时 where、set、if等标签中and
和,
的问题
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
查询文档可知,mybatis的所谓智能只可以删除动态sql里面额外的东西,而不会增加什么,比如下面的代码👇,必然会出错,因为and不会凭空出现,sql语句可能会是select * from mybatisstudy.blog where title= ? author=?
没有and
这必然出错
<select id="queryBlog" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<where>
<if test="title!=null">
title=#{title}
</if>
<if test="author!=null">
author=#{author}
</if>
<if test="views!=null">
views=#{views}
</if>
<if test="creatTime!=null">
creat_time=#{creatTime}
</if>
</where>
</select>
12.6 trim
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。
与 set 元素等价的自定义 trim 元素
先看个例子👇
<select id="queryBlog" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<trim prefix="WHERE" prefixOverrides="AND |OR">
<if test="title!=null">
title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
<if test="views!=null">
and views=#{views}
</if>
<if test="creatTime!=null">
and creat_time=#{creatTime}
</if>
</trim>
</select>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
prefixOverrides
的意思是会自动删除的前缀包括哪些内容
suffixOverrides
的意思是会自动删除的后缀包括哪些内容,在set
标签里面会自动删除的就是,
了
用trim
的一个例子👇
<update id="upadteSet" parameterType="blog">
update mybatisstudy.blog
<trim prefix="SET" suffixOverrides=",">
<if test="author!=null">
author=#{author},
</if>
<if test="creatTime!=null">
creat_time=#{creatTime},
</if>
<if test="title!=null">
title=#{title},
</if>
<if test="views!=null">
views=#{views},
</if>
</trim>
<trim prefix="WHERE" prefixOverrides="AND |OR">
<choose>
<when test="id!=null">
id=#{id}
</when>
<otherwise>
id=ceshi
</otherwise>
</choose>
</trim>
</update>
12.7 froeach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
mapper.xml👇。一个集合里面有一个value为list集合,key为ids的一条。separator为分隔符
<select id="queryForeach" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<where>
<foreach collection="ids" item="id" open="(" separator="or" close=")">
id=#{id}
</foreach>
</where>
</select>
java代码👇
@Test
public void testQuForeach(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
List<String> ids = new ArrayList<>();
ids.add("01");
ids.add("03");
hashMap.put("ids", ids);
System.out.println(hashMap);
try {
// System.out.println(mapper.queryForeach(hashMap));
mapper.queryForeach(hashMap).forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
}
sqlSession.close();
}
mapper语句最终转化为select * from mybatisstudy.blog WHERE ( id=? or id=? )
日志👇
[2020/04/28 00:48:01,873]DEBUG:==> Preparing: select * from mybatisstudy.blog WHERE ( id=? or id=? )
[2020/04/28 00:48:01,904]DEBUG:==> Parameters: 01(String), 03(String)
[2020/04/28 00:48:01,928]DEBUG:<== Total: 2
Blog(id=01, title=hello world, author=sogei, creatTime=Sat Jan 01 00:00:00 CST 2000, views=12)
Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123)
12.8 include
代码复用
在mapper.xml里面sql
和include
相结合可以进行代码复用 ,sql
标签指定id名称,include
标签用refid
属性去指定sql
标签里面的内容,达到代码复用的效果。
<sql id="queryBlogcopy">
<if test="title!=null">
title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
<if test="views!=null">
and views=#{views}
</if>
<if test="creatTime!=null">
and creat_time=#{creatTime}
</if>
</sql>
<select id="queryBlog" parameterType="map" resultType="blog">
select * from mybatisstudy.blog
<trim prefix="WHERE" prefixOverrides="AND |OR">
<include refid="queryBlogcopy"/>
</trim>
</select>
13 缓存
什么是缓存?
缓存技术是一种“以空间换时间”的设计理念,是利用内存空间资源来提高数据检索速度的有效手段之一。Mybatis包含一个非常强大的查询缓存特性,可以非常方便地配置和定制。MyBatis将数据缓存设计成两级结构,分为一级缓存、二级缓存.
作者:不知名的蛋挞
链接:https://www.jianshu.com/p/097e959874d1
来源:简书
mybatis中的缓存?
基本上就是这样。这个简单语句的效果如下:
- 映射语句文件中的所有 select 语句的结果将会被缓存。
- 映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
- 缓存会使用最近最少使用算法(LRU, Least Recently Used)算法来清除不需要的缓存。
- 缓存不会定时进行刷新(也就是说,没有刷新间隔)。
- 缓存会保存列表或对象(无论查询方法返回哪种)的 1024 个引用。
- 缓存会被视为读/写缓存,这意味着获取到的对象并不是共享的,可以安全地被调用者修改,而不干扰其他调用者或线程所做的潜在修改。
一二级缓存区别
13.1 一级缓存(本地缓存)
一级缓存是默认开启的,只在一次sqlsession里面有效(从获得sqsession到close sqlsession),一级缓存无法关闭
java代码👇
/**
*检测一级缓存
*/
@Test
public void testOneH(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
mapper.findAll().forEach(System.out::println);
System.out.println(">=====================");
//在运行一遍mapper.findAll()
mapper.findAll().forEach(System.out::println);
sqlSession.close();
}
日志👇
[2020/04/28 13:15:59,139]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 13:15:59,172]DEBUG:==> Parameters:
[2020/04/28 13:15:59,205]DEBUG:<== Total: 16
Blog(id=01, title=hello world, author=sogei, creatTime=Sat Jan 01 00:00:00 CST 2000, views=12)
Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12)
Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123)
Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122)
Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122)
Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123)
Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122)
Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34)
Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12)
Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12)
Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12)
Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12)
Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12)
Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12)
Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12)
Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)
>=====================
Blog(id=01, title=hello world, author=sogei, creatTime=Sat Jan 01 00:00:00 CST 2000, views=12)
Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12)
Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123)
Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122)
Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122)
Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123)
Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122)
Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34)
Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12)
Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12)
Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12)
Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12)
Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12)
Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12)
Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12)
Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)
[2020/04/28 13:15:59,208]DEBUG:Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5fd4f8f5]
[2020/04/28 13:15:59,208]DEBUG:Returned connection 1607792885 to pool.
看下面的图片可以清除的看到sql查询只用了一次👇,却做了两次相同的查询动作
在原有的两次相同查询基础上,进行一次更新操作,然后再进行一次查询,java代码👇
/**
*检测一级缓存
*/
@Test
public void testOneH(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
System.out.println(mapper.findAll());
System.out.println(">=====================");
//在运行一遍mapper.findAll()
System.out.println(mapper.findAll());
//进行update的操作,这回刷新缓存
Blog blog = new Blog("01", "hello", "更新验证缓存", new Date(), 15);
try {
System.out.println(">======进行update的操作,这回刷新缓存");
mapper.upadteSet(blog);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("》========进行和之前完全相同的查询操作");
System.out.println(mapper.findAll());
sqlSession.close();
}
日志👇
[2020/04/28 13:38:15,192]DEBUG:Opening JDBC Connection
[2020/04/28 13:38:16,314]DEBUG:Created connection 1607792885.
[2020/04/28 13:38:16,319]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 13:38:16,361]DEBUG:==> Parameters:
[2020/04/28 13:38:16,409]DEBUG:<== Total: 16
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 13:33:26 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
>=====================
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 13:33:26 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
>======进行update的操作,这回刷新缓存
[2020/04/28 13:38:16,437]DEBUG:==> Preparing: update mybatisstudy.blog SET author=?, creat_time=?, title=?, views=? WHERE id=?
[2020/04/28 13:38:16,440]DEBUG:==> Parameters: 更新验证缓存(String), 2020-04-28 13:38:16.41(Timestamp), hello(String), 15(Integer), 01(String)
[2020/04/28 13:38:16,449]DEBUG:<== Updates: 1
》========进行和之前完全相同的查询操作
[2020/04/28 13:38:16,450]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 13:38:16,452]DEBUG:==> Parameters:
[2020/04/28 13:38:16,458]DEBUG:<== Total: 16
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 13:38:16 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
[2020/04/28 13:38:16,459]DEBUG:Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5fd4f8f5]
[2020/04/28 13:38:16,459]DEBUG:Returned connection 1607792885 to pool.
可以清除的发现进行刷新操作后,缓存也刷新了,和官网上说的一致
- 映射语句文件中的所有 select 语句的结果将会被缓存。
- 映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
注意:手动清理缓存
sqlSession.clearCache();
一级缓存更新的几个条件
- 进行增、改、删操作
- 手动清理缓存
sqlSession.clearCache();
13.2 二级缓存
二级缓存是mapper级别的缓存,多个SqlSession去操作同一个Mapper的sql语句,多个SqlSession去操作数据库得到数据会存在二级缓存区域,多个SqlSession可以共用二级缓存,二级缓存是跨SqlSession的。
二级缓存是多个SqlSession共享的,其作用域是mapper的同一个namespace,不同的sqlSession两次执行相同namespace下的sql语句且向sql中传递参数也相同即最终执行相同的sql语句,第一次执行完毕会将数据库中查询的数据写到缓存(内存),第二次会从缓存中获取数据将不再从数据库查询,从而提高查询效率。Mybatis默认没有开启二级缓存需要在setting全局参数中配置开启二级缓存。
版权声明:本文为CSDN博主「双斜杠少年」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/u012373815/article/details/47069223
默认情况下,只启用了本地的会话缓存,它仅仅对一个会话中的数据进行缓存。 要启用全局的二级缓存,只需要在你的 SQL 映射文件中添加一行:
<cache/>
13.2.1 开启二级缓存的步骤
-
第一步 在config.xml里面👇,其实
cacheEnabled
是默认开启的,为了代码的可读性,只要用二级缓存,就增加这个setting<!-- 开启全局缓存,其实是默认开启--> <setting name="cacheEnabled" value="true"/>
-
第二步 在mapper.xml里面👇,开启二级缓存
<cache/>
13.2.2 cache
配置
这些属性可以通过 cache 元素的属性来修改。比如:
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
这个更高级的配置创建了一个 FIFO 缓存,每隔 60 秒刷新,最多可以存储结果对象或列表的 512 个引用,而且返回的对象被认为是只读的,因此对它们进行修改可能会在不同线程中的调用者产生冲突。
可用的清除策略有:
LRU
– 最近最少使用:移除最长时间不被使用的对象。FIFO
– 先进先出:按对象进入缓存的顺序来移除它们。SOFT
– 软引用:基于垃圾回收器状态和软引用规则移除对象。WEAK
– 弱引用:更积极地基于垃圾收集器状态和弱引用规则移除对象。默认的清除策略是 LRU。
13.2.3 实例
java代码👇
@Test
public void testTwoH() {
//定义两个sqlsession
SqlSession sqlSession1 = SqlSessionUtil.getSqlSession();
SqlSession sqlSession2 = SqlSessionUtil.getSqlSession();
//分别进行相同的查询操作
BlogMapper mapper = sqlSession1.getMapper(BlogMapper.class);
System.out.println(mapper.findAll());
//关闭第一个sqlsession,一级缓存死掉,原先一级缓存中的内容存入二级缓存
sqlSession1.close();
BlogMapper mapper1 = sqlSession2.getMapper(BlogMapper.class);
System.out.println(mapper1.findAll());
// sqlSession1.close();
sqlSession2.close();
}
日志👇
[2020/04/28 14:34:23,123]DEBUG:Opening JDBC Connection
[2020/04/28 14:34:24,132]DEBUG:Created connection 212683148.
[2020/04/28 14:34:24,136]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 14:34:24,162]DEBUG:==> Parameters:
[2020/04/28 14:34:24,185]DEBUG:<== Total: 16
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 14:24:39 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
[2020/04/28 14:34:24,186]DEBUG:Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cad498c]
[2020/04/28 14:34:24,186]DEBUG:Returned connection 212683148 to pool.
[2020/04/28 14:34:24,186]DEBUG:Cache Hit Ratio [ssm.suyuesheng.mapper.BlogMapper]: 0.5
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 14:24:39 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
可以看到同一mapper下的不同sqlsession使用同一缓存
注意:重点:二级缓存需要一级缓存死掉后才会有效
如果像下面一样,一级缓存没有死,那么二级缓存就不会有东西👇
@Test
public void testTwoH() {
//定义两个sqlsession
SqlSession sqlSession1 = SqlSessionUtil.getSqlSession();
SqlSession sqlSession2 = SqlSessionUtil.getSqlSession();
//分别进行相同的查询操作
//sqlsession1操作
BlogMapper mapper = sqlSession1.getMapper(BlogMapper.class);
System.out.println(mapper.findAll());
//sqlsession2操作
BlogMapper mapper1 = sqlSession2.getMapper(BlogMapper.class);
System.out.println(mapper1.findAll());
//在这之前,没有东西存入二级缓存
sqlSession1.close();
sqlSession2.close();
}
日志👇,可以看到有两次数据库sql查询操作,即使是相同的sql语句
[2020/04/28 15:15:31,205]DEBUG:Opening JDBC Connection
[2020/04/28 15:15:32,280]DEBUG:Created connection 212683148.
[2020/04/28 15:15:32,284]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 15:15:32,310]DEBUG:==> Parameters:
[2020/04/28 15:15:32,334]DEBUG:<== Total: 16
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 14:24:39 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
[2020/04/28 15:15:32,335]DEBUG:Cache Hit Ratio [ssm.suyuesheng.mapper.BlogMapper]: 0.0
[2020/04/28 15:15:32,335]DEBUG:Opening JDBC Connection
[2020/04/28 15:15:32,370]DEBUG:Created connection 1912960603.
[2020/04/28 15:15:32,370]DEBUG:==> Preparing: select * from mybatisstudy.blog
[2020/04/28 15:15:32,370]DEBUG:==> Parameters:
[2020/04/28 15:15:32,374]DEBUG:<== Total: 16
[Blog(id=01, title=hello, author=更新验证缓存, creatTime=Tue Apr 28 14:24:39 CST 2020, views=15), Blog(id=02, title=idea how to use, author=liusann, creatTime=Sat May 01 10:05:00 CST 2004, views=12), Blog(id=03, title=你好世界, author=sogei, creatTime=Sun Jan 01 00:00:00 CST 2006, views=123), Blog(id=04, title=MATLAB使用方法, author=liusann, creatTime=Mon Jan 01 00:00:00 CST 2007, views=122), Blog(id=05, title=WHAT IS JDBC, author=sogei, creatTime=Mon Oct 01 00:00:00 CST 2007, views=122), Blog(id=06, title=INTO JDK1.8, author=liusann, creatTime=Tue Jun 01 10:05:00 CST 2004, views=123), Blog(id=07, title=CREATE A WEBSITE BY USING BOOTSTRAP , author=sogei, creatTime=Wed Jan 01 00:00:00 CST 2020, views=122), Blog(id=08, title=跟新, author=so update, creatTime=Sun Apr 26 21:46:03 CST 2020, views=34), Blog(id=a0e433ab127b488b9fe81d368907b72e, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:53:43 CST 2020, views=12), Blog(id=7124224de5104fffbb9119a75219cb00, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:55:23 CST 2020, views=12), Blog(id=85d4af0c11424a428d7db43e54ec55eb, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:15 CST 2020, views=12), Blog(id=7334ed8c220e454f861909b32eb01da6, title=hehe, author=为了更新, creatTime=Sun Apr 26 20:56:43 CST 2020, views=12), Blog(id=872eac5dd83e40efb503244e99f26993, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:03:08 CST 2020, views=12), Blog(id=1ba71b26cae64a3ea32b330bff8ff007, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:08:31 CST 2020, views=12), Blog(id=2595ec87a88f44c19fa15155c3511a94, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:09:49 CST 2020, views=12), Blog(id=e051ba5328c44d58904f1fea3816850e, title=hehe, author=为了更新, creatTime=Sun Apr 26 21:46:04 CST 2020, views=12)]
[2020/04/28 15:15:32,374]DEBUG:Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@cad498c]
[2020/04/28 15:15:32,374]DEBUG:Returned connection 212683148 to pool.
[2020/04/28 15:15:32,374]DEBUG:Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7205765b]
[2020/04/28 15:15:32,374]DEBUG:Returned connection 1912960603 to pool.
读取缓存的顺序
- 查看二级缓存有没有
- 如果二级缓存没有就查看一级缓存
- 如果一级缓存也没有就执行数据库的读写
某一条语句不想缓存?某一条select语句想执行后就更新缓存?(指的是二级缓存)
请注意,缓存的配置和缓存实例会被绑定到 SQL 映射文件的命名空间中。 因此,同一命名空间中的所有语句和缓存将通过命名空间绑定在一起。 每条语句可以自定义与缓存交互的方式,或将它们完全排除于缓存之外,这可以通过在每条语句上使用两个简单属性来达成。 默认情况下,语句会这样来配置:
<select ... flushCache="false" useCache="true"/> <insert ... flushCache="true"/> <update ... flushCache="true"/> <delete ... flushCache="true"/>
鉴于这是默认行为,显然你永远不应该以这样的方式显式配置一条语句。但如果你想改变默认的行为,只需要设置 flushCache 和 useCache 属性。比如,某些情况下你可能希望特定 select 语句的结果排除于缓存之外,或希望一条 select 语句清空缓存。类似地,你可能希望某些 update 语句执行时不要刷新缓存。