mybatis-plus-3(MP)-进阶
mybatis-plus-3(MP)-进阶
一、准备
1、数据库脚本
-- 创建用户表
CREATE TABLE user (
id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',
name VARCHAR(30) DEFAULT NULL COMMENT '姓名',
age INT(11) DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',
create_time DATETIME DEFAULT NULL COMMENT '创建时间',
update_time DATETIME DEFAULT NULL COMMENT '修改时间',
version INT(11) DEFAULT '1' COMMENT '版本',
deleted INT(1) DEFAULT '0' COMMENT '逻辑删除标识(0.未删除,1.已删除)',
CONSTRAINT manager_fk FOREIGN KEY (manager_id)
REFERENCES user (id)
) ENGINE=INNODB CHARSET=UTF8;
-- 初始化数据：
INSERT INTO user (id, name, age, email, manager_id
, create_time)
VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL
, '2019-01-11 14:20:20'),
(1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553
, '2019-02-05 11:12:22'),
(1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385
, '2019-02-14 08:31:16'),
(1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385
, '2019-01-14 09:15:15'),
(1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385
, '2019-01-14 09:48:16');
2、实体类
@Data
public class User {
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
private LocalDateTime updateTime; // 创建时间
private Integer version; // 版本
private Integer deleted; // 逻辑删除。0：未删除，1：已删除
}
3、Mapper
public interface UserMapper extends BaseMapper<User> {
}
4、启动类
@MapperScan("com.demo.dao")
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
5、application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/mp?useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
二、高级功能
1、逻辑删除
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/mp?useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
mybatis-plus:
global-config:
db-config:
logic-not-delete-value: 0 # 全局配置。如需局部配置可在实体类@TableLogic注解后配置
logic-delete-value: 1
@Configuration
public class MybatisPlusConfiguration{
// V3.1.1及一下版本需要配置这个Bean
// v3.1.2不需要配置
@Bean
public ISqlInjector sqlInjector(){
// 已经过时
return new LogicSqlInjector();
}
}
@Data
public class User {
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
private LocalDateTime updateTime; // 创建时间
private Integer version; // 版本
@TableLogic // 全局逻辑删除
@TableField(select=false) // 查询时，不查询该字段
private Integer deleted; // 逻辑删除。0：未删除，1：已删除
}
public interface UserMapper extends BaseMapper<User> {
@Select("select * from user ${ew.customSqlSegment}")
List<User> mySelectList(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}
测试
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void deleteById(){
// 返回影响行数
int rows = userMapper.deleteById(id);
System.out.println("影响行数：" + rows);
}
// 查询时加入了 where deleted = 0
// 查询时会将deleted查询出来。解决办法是在实体类注解@TableField(select=false)
@Test
public void select(){
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
// 更新时加入了 where deleted = 0
@Test
public void updateById(){
User user = new User();
user.setId(id);
user.setAge(26);
int rows = userMapper.updateById(user);
System.out.println("影响记录数：" + rows);
}
// 在Mapper自定义的查询中，查询时不会出现where deleted = 0
// 解决：
// 方法一：如下面方法。
// 方法二：在Mapper的select中加入条件
@Test
public void mySelect(){
List<User> users = userMapper.mySelectList(
// age > 25 and deleted = 0
Wrappers.<User>lambdaQuery().gt(User::getAge,25).eq(User::getDeleted,0)
);
}
}
2、自动填充
@Data
public class User {
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
@TableField(fill=FieldFill.INSERT)
private LocalDateTime createTime; // 创建时间
@TableField(fill=FieldFill.UPDATE)
private LocalDateTime updateTime; // 创建时间
private Integer version; // 版本
private Integer deleted; // 逻辑删除。0：未删除，1：已删除
}
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject){
// 优化1
// 有些实体没有createTime。每次都自动填充，浪费了性能
// 判断一下是否有createTime再自动填充
boolean hasSetter = metaObject.hasSetter("createTime");
if(hasSetter){
// 优化2
// 达到效果。已设置值，要已设置的值。未设置值，自动填充
Object val = getFieldValByName("createTime",metaObject);
if(val == null){
System.out.println("insertFill~~");
// createTime 为实体参数的名称
setInsertFieldValByName("createTime",LocalDateTime.now(),metaObject);
}
}
}
@Override
public void updateFill(MetaObject metaObject){
System.out.println("updateFill~~");
// createTime 为实体参数的名称
setUpdateFieldValByName("updateTime",LocalDateTime.now(),metaObject);
}
}
测试
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void insert(){
User user = new User();
user.setName("小明");
user.setAge("23");
user.setManagerId("1");
int rows = userMapper.insert(user);
System.out.println("影响行数：" + rows);
}
@Test
public void updateById(){
User user = new User();
user.setId(id);
user.setAge(26);
int rows = userMapper.updateById(user);
System.out.println("影响记录数：" + rows);
}
}
3、乐观锁插件
@Configuration
public class MybatisPlusConfig {
/**
* 乐观锁
*/
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
}
@Data
public class User {
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
private LocalDateTime updateTime; // 创建时间
@Version // 支持的数据类型：int Integer long Long Date Timestamp LocalDateTime
private Integer version; // 版本
private Integer deleted; // 逻辑删除。0：未删除，1：已删除
}
测试
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void updateById(){
int version = 1;
User user = new User();
user.setId(id);
user.setAge(26);
user.setVersion(version);
// 查询后版本号+1
int rows = userMapper.updateById(user);
System.out.println("影响记录数：" + rows);
}
}
4、性能分析插件
@Configuration
public class MybatisPlusConfig {
@Bean
@Profile({"dev","test"})// 设置 dev test 环境开启
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setFormat(true);// 格式化语句
//performanceInterceptor.setMaxTime(5);// 执行时间超过多少秒会抛出异常
return performanceInterceptor;
}
4.1、执行sql分析打印
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.8.2</version>
</dependency>
spring:
datasource:
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
url: jdbc:p6spy:h2:mem:test
spy.properties:
#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
5、多租户SQL解析器
@Configuration
public class MybatisPlusConfig {
/**
* mybatis-plus分页插件<br>
* 文档：http://mp.baomidou.com<br>
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
/*
* 【测试多租户】 SQL 解析处理拦截器<br>
* 这里固定写成住户 1 实际情况你可以从cookie读取，因此数据看不到 【 麻花藤 】 这条记录（ 注意观察 SQL ）<br>
*/
List<ISqlParser> sqlParserList = new ArrayList<>();
TenantSqlParser tenantSqlParser = new TenantSqlParser();
tenantSqlParser.setTenantHandler(new TenantHandler() {
@Override
public Expression getTenantId() {
return new LongValue(1L);
}
@Override
public String getTenantIdColumn() {
return "tenant_id";
}
@Override
public boolean doTableFilter(String tableName) {
// 这里可以判断是否过滤表
/*if ("user".equals(tableName)) {
return true;
}*/
return false;
}
});
sqlParserList.add(tenantSqlParser);
paginationInterceptor.setSqlParserList(sqlParserList);
// paginationInterceptor.setSqlParserFilter(new ISqlParserFilter() {
// @Override
// public boolean doFilter(MetaObject metaObject) {
// MappedStatement ms = PluginUtils.getMappedStatement(metaObject);
// // 过滤自定义查询此时无租户信息约束【 麻花藤 】出现
// if ("com.baomidou.springboot.mapper.UserMapper.selectListBySQL".equals(ms.getId())) {
// return true;
// }
// return false;
// }
// });
return paginationInterceptor;
}
}
6、动态表名 SQL 解析器
@Configuration
@MapperScan("com.baomidou.mybatisplus.samples.dytablename.mapper")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser();
dynamicTableNameParser.setTableNameHandlerMap(new HashMap<String, ITableNameHandler>(2) {{
put("user", (metaObject, sql, tableName) -> {
// metaObject 可以获取传入参数，这里实现你自己的动态规则
String year = "_2018";
int random = new Random().nextInt(10);
if (random % 2 == 1) {
year = "_2019";
}
return tableName + year;
});
}});
paginationInterceptor.setSqlParserList(Collections.singletonList(dynamicTableNameParser));
return paginationInterceptor;
}
}
7、Sql 注入器
/**
* 删除全部
*/
public class DeleteAll extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass,
Class<?> modelClass, TableInfo tableInfo) {
/* 执行 SQL ，动态 SQL 参考类 SqlMethod */
String sql = "delete from " + tableInfo.getTableName();
/* mapper 接口方法名一致 */
String method = "deleteAll";
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addDeleteMappedStatement(mapperClass, method, sqlSource);
}
}
@Component
public class MySqlInjector extends DefaultSqlInjector{
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass){
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new DeleteAll());
return methodList;
}
}
public interface UserMapper extends BaseMapper<User> {
int deleteAll(); // 返回影响行数
}
以上学习自 慕课网 -- MyBatis-Plus进阶
