MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
特性
- 无侵入:只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑
- 损耗小:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象操作
- 强大的 CRUD 操作:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD 操作,更有强大的条件构造器,满足各类使用需求
- 支持 Lambda 形式调用:通过 Lambda 表达式,方便的编写各类查询条件,无需再担心字段写错
- 支持主键自动生成:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题
- 支持 ActiveRecord 模式:支持 ActiveRecord 形式调用,实体类只需继承 Model 类即可进行强大的 CRUD 操作
- 支持自定义全局通用操作:支持全局通用方法注入( Write once, use anywhere )
- 内置代码生成器:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用
- 内置分页插件:基于 MyBatis 物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通 List 查询
- 分页插件支持多种数据库:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
- 内置性能分析插件:可输出 Sql 语句以及其执行时间,建议开发测试时启用该功能,能快速揪出慢查询
- 内置全局拦截插件:提供全表 delete 、 update 操作智能分析阻断,也可自定义拦截规则,预防误操作
支持数据库
- mysql 、 mariadb 、 oracle 、 db2 、 h2 、 hsql 、 sqlite 、 postgresql 、 sqlserver 、 presto
- 达梦数据库 、 虚谷数据库 、 人大金仓数据库。
官网:https://mp.baomidou.com/guide/
默认数据库字段与实体驼峰相匹配,
@TableField("manager_id")//数据库字段
private Long manager_id;//不能查出结果,即便是借助 @TableField(),不符合规范,即便不是驼峰
@TableField("manager_id")//数据库字段
private Long managerid;//能查出结果,借助 @TableField()
private Long managerId;//能查出结果 推荐驼峰
数据库表与实体类名不一致时:
@TableName()
默认主键id会根据雪花算法,自主生成id值 ,只有在主键为 id 时,否则需要借助 @TableId临时字段:数据库不存 private transient String remark; 序列化不行 private static String remark;
@TableField(exist = false)
private String remark;
select max(age),age from user GROUP BY manager_id HAVING SUM(age)<500;报错 打印显示数据要跟在GROUP BY的
select max(age),manager_id from user GROUP BY manager_id HAVING SUM(age)<500; OK
package com.lvym.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.lvym.mybatisplus.dao.UserMapper;
import com.lvym.mybatisplus.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.time.LocalDateTime;
import java.util.*;
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisPlusApplicationTests {
@Autowired
private UserMapper userMapper;
/**
* 查询所有
*/
@Test
public void contextLoads() {
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
/**
* 添加 主键id 默认有雪花算法
*/
@Test
public void insert() {
User user = new User();
user.setEmail("dsdsds@32156");
user.setAge(25);
user.setCreateTime(LocalDateTime.now());
user.setName("jj");
user.setManagerId(1088248166370832385L);
userMapper.insert(user);
}
------------------------------------------------------------------------------------------
/**
* 根据 id 查询
*/
@Test
public void querybyId() {
User user = userMapper.selectById(1088248166370832385L);
System.out.println(user);
}
/**
* 批量查询
*/
@Test
public void querybybatch() {
List<Long> list = Arrays.asList(1088248166370832385L, 1087982257332887553L, 1094590409767661570L);
List<User> users = userMapper.selectBatchIds(list);
users.forEach(System.out::println);
}
/**
* 根据 map 查询 有精确查询的意思
*/
@Test
public void querybymap() {
Map<String, Object> map = new HashMap<>();
map.put("age", 25); //key=数据库字段
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
/**
* 查询 name 带雨字 age 小于33的
*/
@Test
public void querybyWrapper() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name", "雨").lt("age", 33);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询 name 带雨字 age在 20-32 之间 包括20,30, email不为空
*/
@Test
public void querybyWrapper02() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name", "雨").between("age", 20, 32).isNotNull("email");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询 name 姓王或者年龄age大于等于25 按照年龄降序,年龄相同按照id升序
*/
@Test
public void querybyWrapper03() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", "王").or().ge("age", 25).orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询 创建日期等于 2019年2月14日 并且直属上级的 名字为王姓 的
*/
@Test
public void querybyWrapper04() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14").inSql("manager_id", "select id from user where name like '王%'");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* name 为王 并且 (年龄小于40或邮箱不为空)
*/
@Test
public void querybyWrapper05() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", "王").and(wq -> wq.lt("age", 40).or().isNotNull("email"));
// wrapper.likeRight("name","王").lt("age",40).or().isNotNull("email"); 不符合
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* name 为王, 或者 (年龄小于40并且大于20并且邮箱不为空)
*/
@Test
public void querybyWrapper06() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", "王").or(lv -> lv.lt("age", 40).ge("age", 20).isNotNull("email"));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* (年龄小于40或邮箱不为空) 并且名字为王姓
*/
@Test
public void querybyWrapper07() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", "王").nested(ee -> ee.lt("age", 40).isNotNull("email"));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 年龄在 30 31 34 35
*/
@Test
public void querybyWrapper08() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.in("age", Arrays.asList(30, 31, 34, 35));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 只返回一条数据
*/
@Test
public void querybyWrapper09() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1"); //有SQL 注入风险
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 只查询所需列 "name","id"
*/
@Test
public void querybyWrapperSupper() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.("age", Arrays.30, 31, 34, 35)).("name", "id");inselect
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.;out::println)
}
/**
* 排除不需要的列
*/
@Test
public void querybyWrapperSupper02() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.in("age", Arrays.30, 31, 34, 35)).(User.class, lv -> getColumn().equals("age")getColumn().equals("email"));select!lv. && !lv.
List<User> users = userMapper.(wrapper);selectList
users.forEach(System.;out::println)
}
/**
* 调用 condition
*/
@Test
public void querybyWrapperCondition() { String name =
"";
String email = "s";
condition(name, email);
}
private void condition(String name, String email) { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.like(StringUtils., "name", name).like(StringUtils., "email", email);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.;out::println)
}
/**
* 双匹配
*/
@Test
public void querybyWrapperCondition02() { User user =
new User();
user.setName(""); //刘红雨要写完整名
QueryWrapper<User> wrapper = new QueryWrapper<>(user);
// wrapper.like("name", "").lt("age",40).select("name","id");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println)
}
/**
* 精确查询
*/
@Test
public void querybyWrappeAlleq() { QueryWrapper<User> =
wrappernew QueryWrapper<>();
HashMap<String, Object> map = new HashMap<>();
map.put("name", "");
map.put("age", null);
wrapper.(map, false); //null
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 不会向上面那样打印不需要的列,尽管不需要的列=null值
*/
@Test
public void querybyWrapperMap() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.like("name", "").lt("age", 40).("name", "id");
List<Map<String, Object>> maps = userMapper.(wrapper);
maps.forEach(System.out::println)
}
/**
* 按照直属上级分组,查询每组的平均年龄,最大年龄,最小年龄,并只取年龄总和小于500的组 groupBy: select 这里需要groupBy后面的字段,否则报错
*/
@Test
public void querybyWrapperMap02() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").("manager_id").("sum(age)<{0}", 500);
List<Map<String, Object>> maps = userMapper.(wrapper);
maps.forEach(System.out::println)
}
/**
* 只返回第一列或索要打印列的第一个
*/
@Test
public void querybyWrapperObjs() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.("name", "age");
List<Object> objects = userMapper.(wrapper);
objects.forEach(System.;out::println)
}
/**
* 返回总记录数 不能设置select(...); 默认count(1)
*/
@Test
public void querybyWrapperCount() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.like("name", "");
Integer integer = userMapper.(wrapper);
System.out.println("总记录数:"+ integer);
}
/**
* 保证结果只有一条,否则报错
*/
@Test
public void querybyWrapperselectone() { QueryWrapper<User> wrapper =
new QueryWrapper<>();
wrapper.like("name", "");
User user = userMapper.(wrapper);
System.;out.println(user)
}}
/**
* Lambda 条件构造器 防止写错 写错会报错
*/
@Test
public void selectLambda(){
// LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
// LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
lambdaQuery.like(User::getName,"雨");
List<User> users = userMapper.selectList(lambdaQuery);
users.forEach(System.out::println);
}
/**
* 自定义SQL 配置 mybatis-plus:
mapper-locations: 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="com.lvym.mybatisplus.dao.UserMapper">
<select id="selectL" resultType="com.lvym.mybatisplus.entity.User">
select name,age from user ${ew.customSqlSegment}
</select>
</mapper>
或
@Select("select name,age from user ${ew.customSqlSegment}")
List<User> selectL(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
*/
@Test
public void selectMytest(){
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
lambdaQuery.like(User::getName,"雨").lt(User::getAge,33);
List<User> users = userMapper.selectL(lambdaQuery);
users.forEach(System.out::println);
}
/**
* 自定义SQL
mybatis写法
@Select("select * from user")
List<User> getall();
*/
@Test
public void selectMytest02(){
List<User> getall = userMapper.getall();
getall.forEach(System.out::println);
}
/**
* 分页
配置config
*/
package com.lvym.mybatisplus.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisPlusPage {
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
}
@Test
public void selectPage(){
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
Page<User> userPage = new Page<>(1,3,false); //加 false 不会发出查询总数这条SQL语句
// IPage<User> userIPage = userMapper.selectPage(userPage, lambdaQuery);
IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(userPage, lambdaQuery);
System.out.println("当前页:"+mapIPage.getCurrent());
System.out.println("total页:"+mapIPage.getTotal());
List<Map<String, Object>> records = mapIPage.getRecords();
records.forEach(System.out::println);
}
/**
* 分页
dao层*/
IPage<User> selectPages(Page<User> page,@Param(Constants.WRAPPER) Wrapper<User> wrapper);
*.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.lvym.mybatisplus.dao.UserMapper">
<select id="selectPages" resultType="com.lvym.mybatisplus.entity.User">
select name,age from user ${ew.customSqlSegment}
</select>
</mapper>
@Test
public void selectPage02(){
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
Page<User> userPage = new Page<>(1,3);
IPage<User> mapIPage= userMapper.selectPages(userPage, lambdaQuery);
System.out.println("当前页:"+mapIPage.getCurrent());
System.out.println("total页:"+mapIPage.getTotal());
List<User> records = mapIPage.getRecords();
records.forEach(System.out::println);
}
--------------------------------------------------------------------------------------------
/**
* id更新
*/
@Test
public void updateByid() {
User user = new User();
user.setId(1094590409767661570L);
user.setAge(22);
user.setCreateTime(LocalDateTime.now());
int update = userMapper.updateById(user);
System.out.println(update);
}
/**
* 更新 可批量update
*/
@Test
public void updateByWrapper() {
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.eq("age", 25);
User user = new User();
user.setAge(18);
user.setCreateTime(LocalDateTime.now());
int update = userMapper.update(user, wrapper);
System.out.println(update);
}
/**
* set更新 , 适合更新少量数据
*/
@Test
public void updateByWrapper02() {
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.eq("age", 25).set("age", 26);
int update = userMapper.update(null, wrapper);
System.out.println(update);
}
/**
* lambda
*/
@Test
public void updateByLambda() {
// LambdaUpdateWrapper<User> lambda = new UpdateWrapper<User>().lambda();
// LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
LambdaUpdateWrapper<User> lambda = Wrappers.lambdaUpdate();
lambda.eq(User::getAge, 26).set(User::getAge, 18);
int update = userMapper.update(null, lambda);
System.out.println(update);
}
/**
* 链式
*/
@Test
public void updateByLambdaChain() {
boolean chainWrapper = new LambdaUpdateChainWrapper<User>(userMapper).eq(User::getAge, 32).set(User::getCreateTime, LocalDateTime.now()).set(User::getAge, 19).update();
System.out.println(chainWrapper);
}
----------------------------------------------------------------------------------------------
/**
* id删除
*/
@Test
public void deleteByid() {
int deleteById = userMapper.deleteById(1278937132651528193L);
System.out.println(deleteById);
}
/**
* 删除一条,不可多条
*/
@Test
public void deleteByMap() {
Map<String, Object> map = new HashMap<>();
map.put("age",21);
int deleteByMap = userMapper.deleteByMap(map);
System.out.println(deleteByMap);
}
/**
* 批量删除
*/
@Test
public void deleteByBatchIds() {
int i = userMapper.deleteBatchIds(Arrays.asList(1279248600529944577L, 1279248577855594498L));
System.out.println(i);
}
/**
* 也可批量删除
*/
@Test
public void deleteByWrapper() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getAge,15).or().eq(User::getAge,16);
int delete = userMapper.delete(wrapper);
System.out.println(delete);
}
-----------------------------------------------------------------------------------------------------------
先改变实体: 继承 extends Model<User>
public class User extends Model<User> {}
dao层:
public interface UserMapper extends BaseMapper<User> {}
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisPlusARTests {
//也有其他 Wrapper 之类的
/**
* 添加
*/
@Test
public void insertU() {
User user = new User();
user.setCreateTime(LocalDateTime.now());
user.setAge(17);
user.setName("AR");
user.setManagerId(1087982257332887553L);
user.setEmail("jdghgds@dsgfsg");
boolean insert = user.insert();
System.out.println(insert);
}
/**
* 查询
*/
@Test
public void selectById() {
User user = new User();
User user1 = user.selectById(1087982257332887553L);
System.out.println(user1==user); //false
System.out.println(user1);
}
@Test
public void selectById02() {
User user = new User();
user.setId(1087982257332887553L);
User user1 = user.selectById();
System.out.println(user1);
}
/**
* 更新
*/
@Test
public void updateById() {
User user = new User();
user.setId(1087982257332887553L);
user.setAge(33);
boolean updateById = user.updateById();
System.out.println(updateById);
}
/**
* 删除 返回结果有点不同 不等于null或者大于等于0
*/
@Test
public void deleteById() {
User user = new User();
user.setId(1087982257332887553L);
boolean deleteById = user.deleteById();
System.out.println(deleteById);
}
/**
* 有 id 就是update 他会先根据id查询,有就跟新 没有就添加, 没有 id 就是insert
*/
@Test
public void insertOrUpdate() {
User user = new User();
user.setCreateTime(LocalDateTime.now());
user.setAge(17);
user.setName("AR");
user.setManagerId(1087982257332887553L);
user.setEmail("jdghgds@dsgfsg");
boolean insertOrUpdate = user.insertOrUpdate();
System.out.println(insertOrUpdate);
}
}
--------------------------------------------------------------------------
局部主键生成策略:
要在数据库设置主键自增的前提下: 新增的 id 紧跟前面数据的id值
@TableId(type = IdType.AUTO)
private Long id;
没有设置id值,就跟随默认的主键生成策略 雪花算法,设置了就不跟随默认
@TableId(type = IdType.NONE)
private Long id;
uuid,可以自填充
@TableId(type = IdType.UUID)
private String id;
字符串的雪花算法,可以自填充
@TableId(type = IdType.ID_WORKER_STR)
private String id;
雪花算法,可以自填充
@TableId(type = IdType.ID_WORKER)
private Long id;
全局主键生成策略: application.yml配置
mybatis-plus:
global-config:
db-config:
id-type: id_worker_str/uuid/auto/id_worker/input/none
局部策略优先于全局策略
基本设置:https://mp.baomidou.com/config/
----------------------------------------------------------------------------------------------
逻辑删除: 删除的数据,虽然数据库还存在,但是通用Mapper把他当作不存在,
标识字段
@TableLogic 没有这个注解执行真删除
private Integer deleted;
虽然默认0代表逻辑删除,1代表真删除,但是最好还是配一下
mybatis-plus:
global-config:
db-config:
logic-not-delete-value: 0
logic-delete-value: 1
@Bean
public ISqlInjector iSqlInjector(){
return new LogicSqlInjector(); //3.1.1以下的需要配置,否则逻辑删除不成功
}
添加:@TableField(select=false),查询时候不会查出这个字段
@TableLogic
@TableField(select=false)
private Integer deleted;
自定义的SQL语句不会加入逻辑删除,需要添加条件:只要告诉SQL语句 deleted=0,就行了
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
lambdaQuery.eq(User::getDeleted,0);
select * from user where deleted=0.......
-----------------------------------------------------------------------------------------------------
自动填充:
package com.lvym.mybatisplus.commonts;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.time.LocalDateTime;
/**
* 填充处理器
*/
@Component
public class MybatisPlusHandle implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
//判断实体类有没有setCreateTime方法,有才进入
boolean createTime = metaObject.hasSetter("createTime");
if (createTime){
setInsertFieldValByName("createTime", LocalDateTime.now(), metaObject);
}
}
@Override
public void updateFill(MetaObject metaObject) {
//判断参加update语句中有没有设置 user.setUpdateTime(LocalDateTime.now());, 没有就进入
Object updateTime = getFieldValByName("updateTime", metaObject);
if (updateTime==null){
setUpdateFieldValByName("updateTime", LocalDateTime.now(), metaObject);
}
}
}
实体类:
@TableField(fill = FieldFill.INSERT) //insert 时 填充
private LocalDateTime createTime;
@TableField(fill = FieldFill.UPDATE) // update 时 填充
private LocalDateTime updateTime;
测试:
User user = new User();
user.setEmail("dsdsds@32156");
user.setAge(25);
//user.setCreateTime(LocalDateTime.now()); 没必要,user中存在setCreateTime,所以都会进入填充
user.setName("5454");
user.setManagerId(1094592041087729666L);
user.insert();
User user = new User();
user.setId(1279337093717409794L);
user.setAge(22);
user.setUpdateTime(LocalDateTime.now()); //设置了就不进入自动填充
int updateById = userMapper.updateById(user);
System.out.println(updateById);
---------------------------------------------------------------------
乐观锁:
配置插件:
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
return new OptimisticLockerInterceptor();
}
实体类:
@Version
private Integer version;
测试:
int version=1;//应该从数据库中查出
User user = new User();
user.setAge(25);
user.setVersion(version);//会自动 +1
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("id",1279337093717409794L);
int update = userMapper.update(user, wrapper);
- 支持的数据类型只有:int,Integer,long,Long,Date,Timestamp,LocalDateTime
- 整数类型下
newVersion = oldVersion + 1
newVersion
会回写到entity
中- 仅支持
updateById(id)
与update(entity, wrapper)
方法 - 在
update(entity, wrapper)
方法下,wrapper
不能复用!!!
复用之后第一个数据能更新成功,第二条会出现混乱
------------------------------------------------------------------------------------------
性能分析:
配置插件:
@Bean
@Profile({"dev","test"})//在dev和test环境开启
public PerformanceInterceptor performanceInterceptor(){
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setFormat(true); //格式化SQL语句
performanceInterceptor.setMaxTime(5L); //设置最大执行时间 超过5毫秒就报错
return performanceInterceptor;
}
配置执行环境:
spring:
profiles:
active: dev
加入依赖|;
dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.0</version>
</dependency>
修改yml配置
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
url: jdbc:p6spy:mysql:...?useSSL=false&serverTimeZone=GMT%2B8&characterEncoding=UTF-8
添加spy.properties 配置:
#3.2.1以上使用
#modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory 会报错
module.log=com.p6spy.engine.logging.P6LogFactory,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
#输出到指定路径
logfile=log.log
# 使用日志系统记录 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
-------------------------------多租户-----------------------------------------------
多租户 != 权限过滤,不要乱用,租户之间是完全隔离的!!!
租户方案:
1.独立数据库,一个租户一个数据库,隔离级别最高
2.共享数据库,独立schema
3.共享数据库。共享schema
与分页插件配合实现:
@Bean
public PaginationInterceptor paginationInterceptor(){
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
ArrayList<ISqlParser> iSqlParsers = new ArrayList<>();
TenantSqlParser tenantSqlParser = new TenantSqlParser();
tenantSqlParser.setTenantHandler(new TenantHandler() {
@Override
public Expression getTenantId() {
return new LongValue(1088248166370832385L);
}
@Override
public String getTenantIdColumn() {
return "manager_id";
}
@Override
public boolean doTableFilter(String tableName) {
if ("role".equals(tableName)){
return true; //不加入租户信息 表级
}
return false;
}
});
iSqlParsers.add(tenantSqlParser);
paginationInterceptor.setSqlParserList(iSqlParsers);
paginationInterceptor.setSqlParserFilter(new ISqlParserFilter() {
@Override
public boolean doFilter(MetaObject metaObject) {
MappedStatement mappedStatement = SqlParserHelper.getMappedStatement(metaObject);
if ("com.lvym.mybatisplus.dao.UserMapper.queryby".equals(mappedStatement.getId())){
return true; //方法级
}
return false;
}
});
return paginationInterceptor;
}
表级注解:
@SqlParser(filter = true)
@Select("select * from user")
List<User> getall();
mybatis-plus:
global-config:
sql-parser-cache: true #3.1.1之前版本需要配置
------------------------------------动态表名SQL解析器---------------------------------------
与分页插件配合使用: 与多租户方法级过滤有冲突
public static ThreadLocal<String> myTableName=new ThreadLocal<>();
@Bean
public PaginationInterceptor paginationInterceptor(){
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
ArrayList<ISqlParser> iSqlParsers = new ArrayList<>();
DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser();
Map<String, ITableNameHandler> handlerHashMap = new HashMap<>();
表名
handlerHashMap.put("user", new ITableNameHandler() {
@Override
public String dynamicTableName(MetaObject metaObject, String sql, String tableName) {
return myTableName.get(); //从ThreadLocal中获取
}
});
dynamicTableNameParser.setTableNameHandlerMap(handlerHashMap);
iSqlParsers.add(dynamicTableNameParser);
paginationInterceptor.setSqlParserList(iSqlParsers);
return paginationInterceptor;
}
测试:
MybatisPlusPage.myTableName.set("user_...."); //运行的时候就会把表名改成 user_.. 实现动态表名
User user = new User();
user.setId(1279337093717409794L);
User user1 = user.selectById();
System.out.println(user1);
---------------------------------SQL注入器-----------------------------------------
新建:delete方法
package com.lvym.mybatisplus.method;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
public class DeleteMethod extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
String sql="delete from "+tableInfo.getTableName();
//Mapper接口中的方法名
String method="deleteAll";
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return addDeleteMappedStatement(mapperClass,method,sqlSource);
}
}
新建:
package com.lvym.mybatisplus.injector;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.lvym.mybatisplus.method.DeleteMethod;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class MysqlJector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);//必须
methodList.add(new DeleteMethod());
return methodList;
}
}
mapper:
int deleteAll(); //
----------mapper层选装件----------------------
批量新增数据,自选字段 insert
* <p> 不同的数据库支持度不一样!!! 只在 mysql 下测试过!!! 只在 mysql 下测试过!!! 只在 mysql 下测试过!!! </p>
* <p> 除了主键是 <strong> 数据库自增的未测试 </strong> 外理论上都可以使用!!! </p>
* <p> 如果你使用自增有报错或主键值无法回写到entity,就不要跑来问为什么了,因为我也不知道!!! </p>
* <p>
* 自己的通用 mapper 如下使用:
* <pre>
* int insertBatchSomeColumn(List<T> entityList);
* </pre>
* </p>
*
* <li> 注意: 这是自选字段 insert !!,如果个别字段在 entity 里为 null 但是数据库中有配置默认值, insert 后数据库字段是为 null 而不是默认值 </li>
*
* <p>
* 常用的 {@link Predicate}:
* </p>
*
* <li> 例1: t -> !t.isLogicDelete() , 表示不要逻辑删除字段 </li>
* <li> 例2: t -> !t.getProperty().equals("version") , 表示不要字段名为 version 的字段 </li>
* <li> 例3: t -> t.getFieldFill() != FieldFill.UPDATE) , 表示不要填充策略为 UPDATE 的字段 </li>
*
选装件:InsertBatchSomeColumn
需要与SQL注入器配合使用:
package com.lvym.mybatisplus.injector;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.additional.InsertBatchSomeColumn;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class MysqlJector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
// methodList.add(new DeleteMethod());
methodList.add(new InsertBatchSomeColumn(t->!t.isLogicDelete()&!t.getColumn().equals("age")&!t.getColumn().equals("version")));
//排除 逻辑删除,age,version字段的都insert,没有在这里排除的字段,之前设置其默认值会失效
return methodList;
}
}
mapper层:
int insertBatchSomeColumn(List<User> list);
像这种有共用性的最好把他抽取:
package com.lvym.mybatisplus.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
public interface MyMapper<T> extends BaseMapper<T> {
int deleteAll();
int insertBatchSomeColumn(List<T> list);
}
package com.lvym.mybatisplus.dao;
import com.baomidou.mybatisplus.annotation.SqlParser;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.lvym.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserMapper extends MyMapper<User> {
// @Select("select name,age from user ${ew.customSqlSegment}")
List<User> selectL(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
@SqlParser(filter = true)
@Select("select * from user")
List<User> getall();
IPage<User> selectPages(Page<User> page,@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}
测试: 除了排除的字段,没有排除了字段,如果没有添加就会赋予null
User user = new User();
user.setEmail("fdffff@32156");
user.setAge(24);
user.setName("5454");
user.setManagerId(1094592041087729666L);
User user2 = new User();
user2.setEmail("fdffff@32156");
user2.setAge(27);
user2.setName("666666666666");
user2.setManagerId(1094592041087729666L);
int i = userMapper.insertBatchSomeColumn(Arrays.asList(user, user2));
System.out.println(i);
选装件:LogicDeleteByIdWithFill
根据 id 逻辑删除数据,并带字段填充功能
* <p>注意入参是 entity !!! ,如果字段没有自动填充,就只是单纯的逻辑删除</p>
* <p>
* 自己的通用 mapper 如下使用:
* <pre>
* int deleteByIdWithFill(T entity);
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
// methodList.add(new DeleteMethod());
//methodList.add(new InsertBatchSomeColumn(t->!t.isLogicDelete()&!t.getColumn().equals("age")&!t.getColumn().equals("version")));//不是逻辑删除,age的都insert
methodList.add(new LogicDeleteByIdWithFill());
return methodList;
}
抽取mapper:
int deleteByIdWithFill(T entity);
实体层:
private String name;
@TableField(fill = FieldFill.UPDATE) // update 时 填充
private Integer age;
测试:
User user = new User();
user.setId(1279606104346595329L); //默认根据id
user.setAge(25);
user.setName("0000000");// 没有加 @TableField(fill = FieldFill.UPDATE) // update 时 填充无效
userMapper.deleteByIdWithFill(user);
选装件:AlwaysUpdateSomeColumnById
根据 ID 更新固定的那几个字段(但是不包含逻辑删除)
*
* <p>
* 自己的通用 mapper 如下使用:
* <pre>
* int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
// methodList.add(new DeleteMethod());
//methodList.add(new InsertBatchSomeColumn(t->!t.isLogicDelete()&!t.getColumn().equals("age")&!t.getColumn().equals("version")));//不是逻辑删除,age的都insert
// methodList.add(new LogicDeleteByIdWithFill());
methodList.add(new AlwaysUpdateSomeColumnById(suiyi->!suiyi.getColumn().equals("name")));//排除name字段
return methodList;
}
mapper层:
int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);
测试:
User user = new User();
user.setId(1094590409767661570L);
user.setAge(23);
user.setName("0000000");// 排除了,不会修改成功,,没有赋予值的字段自动填充null值,所以最好都赋予值
userMapper.alwaysUpdateSomeColumnById(user);