数据库
数据库表定义
mysql> desc t_user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show create table t_user \G
*************************** 1. row ***************************
Table: t_user
Create Table: CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`phone` varchar(20) DEFAULT NULL COMMENT '手机号码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
表中的数据:
mysql> select * from t_user;
+----+----------+--------------------------------------------------------------+-------------+
| id | username | password | phone |
+----+----------+--------------------------------------------------------------+-------------+
| 1 | admin | $2a$10$iv5HFxuwh4.Vj9jrxILtRe/SKI1H4BWnGlIBXhAvfLjLI1pz3kFFS | 12345678910 |
| 2 | druid | 123456 | 12345678910 |
| 3 | root | 123456 | 12345678910 |
| 4 | druid | 123456 | 12345678910 |
+----+----------+--------------------------------------------------------------+-------------+
4 rows in set (0.00 sec)
项目基本结构
新建 Maven 项目,引入依赖:
<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>com.mk</groupId>
<artifactId>mybatis-logback</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<mysql-connector-java.version>5.1.40</mysql-connector-java.version>
<logback-classic.version>1.2.3</logback-classic.version>
</properties>
<dependencies>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-connector-java.version}</version>
<scope>runtime</scope>
</dependency>
<!-- MyBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<!-- logback 日志 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback-classic.version}</version>
</dependency>
</dependencies>
</project>
数据库连接配置,src/main/resources/db.properties 文件:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/security?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=true
username=root
password=root
src/main/resources/logback.xml 日志配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">
<!-- 定义日志文件的输出路径 -->
<property name="USER_HOME" value="G:/log" />
<!-- 输出到控制台 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d{HH:mm:ss.SSS} %-5level [%-10thread] %logger - %msg%n</pattern>
</encoder>
</appender>
<!-- 基于大小以及时间的轮转策略 -->
<!-- 参考:http://www.logback.cn/04%E7%AC%AC%E5%9B%9B%E7%AB%A0Appenders.html -->
<appender name="ROLLING" class="ch.qos.logback.core.rolling.RollingFileAppender">
<!-- 要写入文件的名称。如果文件不存在,则新建。 -->
<file>${USER_HOME}/logback.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<fileNamePattern>%d{yyyyMMdd}/logback-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
<maxFileSize>100KB</maxFileSize>
<!-- 最多保留多少数量的归档文件,将会异步删除旧的文件。 -->
<maxHistory>30</maxHistory>
<!-- 所有归档文件总的大小。当达到这个大小后,旧的归档文件将会被异步的删除。 -->
<totalSizeCap>1000MB</totalSizeCap>
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} %-5level [%-10thread] %logger - %msg%n</pattern>
</encoder>
</appender>
<logger name="com.mk.dao" level="DEBUG" ></logger>
<!-- 日志输出级别 -->
<root level="info">
<appender-ref ref="ROLLING" />
<appender-ref ref="STDOUT" />
</root>
</configuration>
src/main/resources/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>
<properties resource="db.properties"></properties>
<settings>
<!-- 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 -->
<setting name="logImpl" value="SLF4J"/>
</settings>
<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>
</environments>
<mappers>
<mapper resource="sqlmap/UserMapper.xml"/>
</mappers>
</configuration>
实体类:
import java.io.Serializable;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
@Getter
@Setter
@ToString
public class User implements Serializable {
private static final long serialVersionUID = -2614180613130427763L;
private Integer id;
private String username;
private String password;
private String phone;
}
DAO 层(mapper 接口):
import com.mk.domain.User;
public interface UserMapper {
int deleteByPrimaryKey(Integer id);
int insert(User record);
int insertSelective(User record);
User selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(User record);
int updateByPrimaryKey(User record);
}
映射文件 src/main/resources/sqlmap/UserMapper.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="com.mk.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.mk.domain.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="username" jdbcType="VARCHAR" property="username" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="phone" jdbcType="VARCHAR" property="phone" />
</resultMap>
<sql id="Base_Column_List">
id, username, password, phone
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_user
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.mk.domain.User">
insert into t_user (id, username, password,
phone)
values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.mk.domain.User">
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="username != null">
username,
</if>
<if test="password != null">
password,
</if>
<if test="phone != null">
phone,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="username != null">
#{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.mk.domain.User">
update t_user
<set>
<if test="username != null">
username = #{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.mk.domain.User">
update t_user
set username = #{username,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
phone = #{phone,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
MyBatis 本地(一级)缓存测试
第 1 种情况:在同一个 SQL 会话中执行两次相同的查询
import java.io.InputStream;
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 com.mk.dao.UserMapper;
public class App {
public static void main(String[] args) throws Exception {
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// 创建 SQL 会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 创建 SQL 会话
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得 mapper 接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 直接调用接口的方法
System.out.println(userMapper.selectByPrimaryKey(2));
System.out.println(userMapper.selectByPrimaryKey(2));
// 关闭 SQL 会话
sqlSession.close();
}
}
从控制台输出的日志可知,MyBatis 只执行 1 次数据库查询:
16:07:46.269 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:07:46.299 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:07:46.319 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=druid, password=123456, phone=12345678910)
User(id=2, username=druid, password=123456, phone=12345678910)
第 2 种情况:在同一个 SQL 会话中执行两次不同的查询
public class App {
public static void main(String[] args) throws Exception {
...
// 获得 mapper 接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 直接调用接口的方法
System.out.println(userMapper.selectByPrimaryKey(2));
System.out.println(userMapper.selectByPrimaryKey(3));
// 关闭 SQL 会话
sqlSession.close();
}
}
从控制台输出的日志可知,MyBatis 执行了 2 次数据库查询:
16:08:13.127 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:08:13.157 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:08:13.167 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=druid, password=123456, phone=12345678910)
16:08:13.167 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:08:13.167 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 3(Integer)
16:08:13.167 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=3, username=root, password=123456, phone=12345678910)
第 3 种情况:在不同 SQL 会话中执行两次相同的查询
public class App {
public static void main(String[] args) throws Exception {
...
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper.selectByPrimaryKey(2));
SqlSession sqlSession2 = sqlSessionFactory.openSession();
UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
System.out.println(userMapper2.selectByPrimaryKey(2));
sqlSession.close();
sqlSession2.close();
}
}
从控制台输出的日志可知,MyBatis 执行了 2 次数据库查询:
16:09:23.660 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:09:23.700 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:09:23.730 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=druid, password=123456, phone=12345678910)
16:09:23.740 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:09:23.740 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:09:23.740 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=druid, password=123456, phone=12345678910)
第 4 种情况:在同一个 SQL 会话中执行查询之后,更新数据,再执行相同的查询
public class App {
public static void main(String[] args) throws Exception {
...
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 第一次查询
User user = userMapper.selectByPrimaryKey(2);
System.out.println(user);
// 更新数据
user.setUsername("mybatis");
userMapper.updateByPrimaryKeySelective(user);
// 第二次查询
System.out.println(userMapper.selectByPrimaryKey(2));
sqlSession.commit();
sqlSession.close();
}
}
从控制台输出的日志可知,MyBatis 执行了 2 次数据库查询:
16:12:22.808 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:12:22.838 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:12:22.848 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=druid, password=123456, phone=12345678910)
16:12:22.868 DEBUG [main ] com.mk.dao.UserMapper.updateByPrimaryKeySelective - ==> Preparing: update t_user SET username = ?, password = ?, phone = ? where id = ?
16:12:22.868 DEBUG [main ] com.mk.dao.UserMapper.updateByPrimaryKeySelective - ==> Parameters: mybatis(String), 123456(String), 12345678910(String), 2(Integer)
16:12:22.958 DEBUG [main ] com.mk.dao.UserMapper.updateByPrimaryKeySelective - <== Updates: 1
16:12:22.958 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
16:12:22.958 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
16:12:22.968 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=mybatis, password=123456, phone=12345678910)
关闭本地缓存
可以通过修改 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>
...
<!-- 参考:https://mybatis.org/mybatis-3/zh/configuration.html#environments -->
<settings>
...
<!-- 默认值为 SESSION,这种情况下会缓存一个会话中执行的所有查询。 -->
<setting name="localCacheScope" value="STATEMENT"/>
</settings>
...
</configuration>
关闭本地缓存之后,在同一个 SQL 会话中执行两次相同的查询,MyBatis 将执行 2 次数据库查询。
参考
MyBatis 二级缓存测试
二级缓存
默认情况下,只启用了本地的会话缓存,它仅仅对一个会话中的数据进行缓存。 要启用全局的二级缓存,只需要在你的 SQL 映射文件中添加一行:
<cache/>
基本上就是这样。这个简单语句的效果如下:
- 映射语句文件中的所有
select
语句的结果将会被缓存。 - 映射语句文件中的所有
insert
、update
和delete
语句会刷新缓存。 - 缓存会使用最近最少使用算法(LRU, Least Recently Used)算法来清除不需要的缓存。
- 缓存不会定时进行刷新(也就是说,没有刷新间隔)。
- 缓存会保存列表或对象(无论查询方法返回哪种)的 1024 个引用。
- 缓存会被视为读/写缓存,这意味着获取到的对象并不是共享的,可以安全地被调用者修改,而不干扰其他调用者或线程所做的潜在修改。
提示 缓存只作用于 cache
标签所在的映射文件中的语句。如果你混合使用 Java API 和 XML 映射文件,在共用接口中的语句将不会被默认缓存。你需要使用 @CacheNamespaceRef
注解指定缓存作用域。
这些属性可以通过 cache 元素的属性来修改。比如:
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
这个更高级的配置创建了一个 FIFO 缓存,每隔 60 秒刷新,最多可以存储结果对象或列表的 512 个引用,而且返回的对象被认为是只读的,因此对它们进行修改可能会在不同线程中的调用者产生冲突。
可用的清除策略有:
LRU
– 最近最少使用:移除最长时间不被使用的对象。FIFO
– 先进先出:按对象进入缓存的顺序来移除它们。SOFT
– 软引用:基于垃圾回收器状态和软引用规则移除对象。WEAK
– 弱引用:更积极地基于垃圾收集器状态和弱引用规则移除对象。
默认的清除策略是 LRU
。
flushInterval
(刷新间隔)属性可以被设置为任意的正整数,设置的值应该是一个以毫秒为单位的合理时间量。 默认情况是不设置,也就是没有刷新间隔,缓存仅仅会在调用语句时刷新。
size
(引用数目)属性可以被设置为任意正整数,要注意欲缓存对象的大小和运行环境中可用的内存资源。默认值是 1024。
readOnly
(只读)属性可以被设置为 true 或 false。只读的缓存会给所有调用者返回缓存对象的相同实例。 因此这些对象不能被修改。这就提供了可观的性能提升。而可读写的缓存会(通过序列化)返回缓存对象的拷贝。 速度上会慢一些,但是更安全,因此默认值是 false。
提示 二级缓存是事务性的。这意味着,当 SqlSession 完成并提交时,或是完成并回滚,但没有执行 flushCache=true
的 insert/delete/update 语句时,缓存会获得更新。
二级缓存测试
修改映射器文件,添加 <cache/>
:
<?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="com.mk.dao.UserMapper">
<cache/>
...
</mapper>
修改 App.java
,在两个不同的会话中执行相同的查询:
public class App {
public static void main(String[] args) throws Exception {
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// 创建 SQL 会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper.selectByPrimaryKey(2));
sqlSession.close();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
System.out.println(userMapper2.selectByPrimaryKey(2));
sqlSession2.close();
}
}
从控制台输出的日志可知,MyBatis 只执行 1 次数据库查询:
17:03:21.273 DEBUG [main ] com.mk.dao.UserMapper - Cache Hit Ratio [com.mk.dao.UserMapper]: 0.0
17:03:21.484 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Preparing: select id, username, password, phone from t_user where id = ?
17:03:21.507 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
17:03:21.520 DEBUG [main ] com.mk.dao.UserMapper.selectByPrimaryKey - <== Total: 1
User(id=2, username=mybatis, password=123456, phone=12345678910)
17:03:21.536 DEBUG [main ] com.mk.dao.UserMapper - Cache Hit Ratio [com.mk.dao.UserMapper]: 0.5
User(id=2, username=mybatis, password=123456, phone=12345678910)
Cache Hit Ratio
表示查询缓存的命中率。
关闭二级缓存
如果要关闭二级缓存,可以修改 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>
...
<!-- 参考:https://mybatis.org/mybatis-3/zh/configuration.html#environments -->
<settings>
...
<!-- 全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存。 -->
<setting name="cacheEnabled" value="false"/>
...
</settings>
...
</configuration>