目录
一. MybatisPlus 基础介绍
- mybatisPlus官方介绍:MyBatis (opens new window)的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
- 优点
- 集成mybatisPlus注意点: 注意依赖问题,不能同时引入mybatisPlus与mybatis,特别考虑在引入其它依赖时,依赖中是否包含了mybatis
二. SpringBoot+MybatisPlus+Druid 基础项目集成
- mysql表
CREATE TABLE `tbl_mock` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`mock_field` varchar(32) DEFAULT NULL,
`version` int(32) DEFAULT NULL COMMENT '乐观锁字段',
`del` int(1) DEFAULT '0' COMMENT '是否删除,0否,1是',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
- 项目整体结构
- pom依赖
<?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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.lumous.adapter</groupId>
<artifactId>pms-adapter-rezen</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<java.version>1.8</java.version>
<spring-cloud.version>Greenwich.SR3</spring-cloud.version>
<druid.version>1.1.20</druid.version>
<lombok.version>1.18.2</lombok.version>
<commons-lang.version>3.9</commons-lang.version>
<guava.version>28.0-jre</guava.version>
<fastjson.version>1.2.8</fastjson.version>
<pagehelper.version>1.2.12</pagehelper.version>
<springfox-swagger2.version>2.5.0</springfox-swagger2.version>
<dom4j.version>1.6.1</dom4j.version>
<commons.pool2.version>2.7.0</commons.pool2.version>
</properties>
<dependencies>
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- redis -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>${commons.pool2.version}</version>
</dependency>
<!-- xml -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>${dom4j.version}</version>
</dependency>
<!-- 监控-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<!--服务发现-->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-consul-discovery</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-hystrix</artifactId>
</dependency>
<!-- 工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>${guava.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>${springfox-swagger2.version}</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>${springfox-swagger2.version}</version>
</dependency>
<dependency>
<groupId>io.micrometer</groupId>
<artifactId>micrometer-core</artifactId>
</dependency>
<dependency>
<groupId>io.micrometer</groupId>
<artifactId>micrometer-registry-prometheus</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-openfeign</artifactId>
</dependency>
<!--mysql 数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mybatisPlus 依赖(注意点此处使用mybatis-plus-boot-starter不要同时导入mybatis与mybatisPlus
可能存在兼容问题,并且要考虑在导入其它依赖时,导入的依赖中是否继承了mybatis如果有需要排除例如pagHelp,mapper等-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<!--druid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.9</version>
</dependency>
<!--SpringBoot 热部署插件-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!--热部署相关-->
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
<addResources>true</addResources>
</configuration>
</plugin>
</plugins>
</build>
</project>
- application.yml 配置(中的关注DataSource与MybatisPlus)
server:
port: 8080
spring:
profiles:
active: dev #环境
application:
name: pms-adapter-rezen
group: com.roamblue.luma
cloud:
consul:
discovery:
enabled: true
health-check-critical-timeout: 30s
healthCheckInterval: 5s
heartbeat: /actuator/health
instance-group: pms
instanceId: ${spring.application.name}-${random.value}
port: ${server.port}
prefer-ip-address: true
service-name: test-${spring.application.name}
tags: pms,app-service,cluster-springcloud-microservice,contextpath-${spring.application.name}
host: 127.0.0.1
port: 8500
datasource:
url: jdbc:mysql://127.0.0.1:3306/pms-backend?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: test
password: b53JkJmRs1VZwQMp72D3
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
spring.datasource.filters: stat,wall,log4j
druid:
# 初始化大小,最小,最大
initial-size: 5
min-idle: 5
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
# 配置DruidStatFilter
web-stat-filter:
enabled: true
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
# 配置DruidStatViewServlet
stat-view-servlet:
url-pattern: "/druid/*"
# IP白名单(没有配置或者为空,则允许所有访问)
allow: 127.0.0.1,192.168.163.1
# IP黑名单 (存在共同时,deny优先于allow)
deny: 192.168.1.73
# 禁用HTML页面上的“Reset All”功能
reset-enable: false
# 登录名
login-username: admin
# 登录密码
login-password: 123456
#mybatis-plus配置
mybatis-plus:
#classpath:/mapper/*Mapper.xml
mapper-locations: com.lumous.pms.adapter.mapper.*Mapper.xml
#实体扫描,多个package用逗号或者分号分隔
typeAliasesPackage: com.lumous.pms.adapter.dto
#枚举映射
#typeEnumsPackage: com.枚举地址
configuration:
map-underscore-to-camel-case: true
cache-enabled: true #配置的缓存的全局开关
lazyLoadingEnabled: true #延时加载的开关
multipleResultSetsEnabled: true #开启的话,延时加载一个属性时会加载该对象全部属性,否则按需加载属性
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #打印sql语句,调试用
logging:
level: warn
global-config:
#主键类型 AUTO:"数据库ID自增", INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
id-type: 2
#字段策略 IGNORED:"忽略判断",NOT_NULL:"非 NULL 判断"),NOT_EMPTY:"非空判断"
#推荐使用下方的原因: 假设需是希望更新时运行某个字段设置为null此处用"IGNORED"修饰,
#此时会出现一种情况,查询时where 字段=null则会查到为null的,针对该情况提供了下方的,针对操作语句的验证方式
#field-strategy: not_empty
#field-insertStrategy:
#field-updateStrategy:
#field-selectStrategy:
#驼峰下划线转换
column-underline: true
#数据库大写下划线转换
#capital-mode: true
#逻辑删除配置
logic-delete-value: 0
logic-not-delete-value: 1
db-type: mysql
#刷新mapper 调试神器
refresh: true
#插入或更新数据成功后返回策略(默认返回操作数据条数)
#key-generator: com.baomidou.springboot.xxx
#3.0后添加配置,bean的注入方式
#sql-injector: com.baomidou.mybatisplus.mapper.LogicSqlInjector
#公共字段使用策略(是否生效待测试)
#meta-object-handler: com.baomidou.springboot.xxx
#熔断降级相关
hystrix:
command:
default:
execution:
isolation:
thread:
timeoutInMillisecond: 100000
ribbon:
ConnectTimeout: 3000
MaxAutoRetries: 1
MaxAutoRetriesNextServer: 1
OkToRetryOnAllOperations: true
ReadTimeout: 5000
#监控相关
management:
endpoint:
metrics:
enabled: true
prometheus:
enabled: true
endpoints:
web:
exposure:
include:
- prometheus
- health
metrics:
export:
prometheus:
enabled: true
tags:
application: ${spring.application.name}
- logback-spring.xml 日志配置
<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true">
<property name="LOG_PATH" value="/home/app/log/"/>
<property name="APP_NAME" value="pms-adapter-ly"/>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d{HH:mm:ss.SSS} %contextName [%thread] %-5level %logger{36} - msgid:%X{msgid} %msg%n</pattern>
<charset>UTF-8</charset>
</encoder>
</appender>
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>${LOG_PATH}/${APP_NAME}.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<fileNamePattern>${LOG_PATH}/%d{yyyy-MM}/${APP_NAME}-%d{yyyy-MM-dd}-%i.log.gz</fileNamePattern>
<maxFileSize>128MB</maxFileSize>
<maxHistory>7</maxHistory>
<totalSizeCap>10GB</totalSizeCap>
</rollingPolicy>
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - msgid:%X{msgid} %msg%n</pattern>
<charset>utf-8</charset>
</encoder>
</appender>
<root level="INFO">
<appender-ref ref="FILE"/>
<appender-ref ref="STDOUT"/>
</root>
</configuration>
- mapper 接口
package com.lumous.pms.adapter.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lumous.pms.adapter.dto.TblMockDto;
public interface TblMockMapper extends BaseMapper<TblMockDto> {
}
- mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lumous.pms.adapter.mapper.TblMockMapper">
</mapper>
- mybatisPlus配置类
package com.lumous.pms.adapter.config;
import com.baomidou.mybatisplus.core.injector.ISqlInjector;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
//如果需要事物,添加事物开启注解
@EnableTransactionManagement
public class MybatisPlusConfig {
//注册mybatisPlust乐观锁组件
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
//注册分页拦截器组件
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
//注册逻辑删除拦截器组件
@Bean
public ISqlInjector sqlInjector() {
return new LogicSqlInjector();
}
//注册性能分析拦截器插件,打印sql执行时间
//性能分析方式: 慢sql日志,第三方压测工具,druid等等,
//MP也提供了性能分析拦截器插件,用于输出每条sql的执行时间
@Bean
//保证生产环境效率指定 dev test 环境开启
//注意如果要指定环境配置文件中要指定运行的环境
//例如: spring.profiles.active=dev
@Profile({"dev","test"})
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor prePerformanceInterceptor = new PerformanceInterceptor();
//设置sql允许执行的最大时间,如果超过抛出异常,单位毫秒
prePerformanceInterceptor.setMaxTime(10000);
//打印日志开启格式化
prePerformanceInterceptor.setFormat(true);
return new PerformanceInterceptor();
}
}
- 对应表的实体类
package com.lumous.pms.adapter.dto;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import org.springframework.stereotype.Component;
import java.util.Date;
@Data
@Component
@TableName(value = "tbl_mock")
public class TblMockDto {
//@TableId修饰主键属性指定主键生成策略(除了使用该注解设置以外,也可以在yml中配置)
//ID_WORKER:雪花默认
//AUTO:自增(注意点如果使用该类型,建表时要设置自增sql)
//NONE:不设置
//INPUT:手动输入(该方式创建对象时需要设置id)
//UUID: uuid
//ID_WORKER_STR: ID_WORKER的字符串表示
@TableId(type = IdType.ID_WORKER)
private Integer id;
private String mockField;
//@Version: mybatisPlus提供的乐观锁,表中要有对应的
//字段,并且遵循乐观锁规则,使用该注解时需要编写注册
//MybatisPlus的乐观锁插件
@Version
private Integer version;
//@TableLogic 修饰代表逻辑删除字段属性
@TableLogic
private Integer del;
//@TableField 字段自动填充策略(DEFAULT默认,不填充)
//注意点:使用@TableField时需要编写处理器,也就是填充该字段的值的生成
@TableField(fill= FieldFill.INSERT)
private Date createTime;
@TableField(fill= FieldFill.INSERT_UPDATE)
private Date updateTime;
}
操作数据示例
1. 常见基础操作与配置解释
package com.lumous.pms.adapter.controller;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.lumous.pms.adapter.dto.TblMockDto;
import com.lumous.pms.adapter.mapper.TblMockMapper;
import lombok.val;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
public class MybatisPlusTestController {
@Autowired
private TblMockMapper tblMockMapper;
/**
* 基础查询
* @return
*/
@GetMapping(value = {"/myTest"}, produces = "application/json")
public Object myTest() {
//1.TblMockMapper 继承了 BaseMapper<T> 接口,使用继承而来的接口执行操作即可
//查询所有,观察selectList()方法,该方法需要一个Wrapper<T>条件构造器
//如果传null则是查询所有
List<TblMockDto> dtoList = tblMockMapper.selectList(null);
//tblMockMapper.selectObjs();
//tblMockMapper.selectOne();
//tblMockMapper.selectCount();
dtoList.forEach(System.out::println);
//1.根据id查询
TblMockDto dtoById = tblMockMapper.selectById(1);
//2.多个id条件
List<TblMockDto> dtoListByIds = tblMockMapper.selectBatchIds(Arrays.asList(1, 2, 3));
//3.Map条件查询
HashMap<String, Object> map = new HashMap<>();
//map中key为字段名,value为对应该字段的值等值查
map.put("mockField", "bbb");
List<TblMockDto> dtoListByMap = tblMockMapper.selectByMap(map);
return "success:" + dtoList;
}
/**
* 插入
* @return
*/
@GetMapping(value = {"/insertTest"}, produces = "application/json")
public Object insertTest() {
TblMockDto dto = new TblMockDto();
dto.setMockField("bbb");
//dto.setId(3);
int count = tblMockMapper.insert(dto);
return "success:" + count;
}
/**
* 更新
* @return
*/
@GetMapping(value = {"/upadateTest"}, produces = "application/json")
public Object upadateTest() {
TblMockDto dto = tblMockMapper.selectById(1);
dto.setMockField("更新");
//更新,注意点虽然方法是updateById但是方法入参是对应的实体类对象<T>
int count = tblMockMapper.updateById(dto);
return "success:" + count;
}
}
2. QueryWapper 与 UpdateWapper
/**
* QueryWapper 使用示例
* @return
*/
@GetMapping(value = {"/queryWapperTest"}, produces = "application/json")
public Object queryWapperTest() {
//1.创建QueryWapper对象
QueryWrapper<TblMockDto> wapper = new QueryWrapper<>();
//2.添加查询条件
wapper.isNotNull("mockField");//添加不为空条件,传入对应的字段名
//wapper.isNull("字段名");
wapper.eq("mockField", "bbb");//添加等值条件
wapper.ne("", "");//不等于
wapper.gt("", "");//大于
//wapper.ge("字段名",值)//大于等于条件
wapper.lt("", "");//小于
wapper.le("", "");//小于等于
//wapper.allEq();
wapper.in("字段名", "1", "2");//
//wapper.notIn();
//wapper.between("字段名", "区间值1", "区间值2");//范围查询
wapper.like("字段名", "字段值");//全模糊
//wapper.notLike("字段名", 字段值);//全模糊,不包含
//wapper.likeLeft("", "");
//wapper.likeRight("", "");
//上面的wapper条件添加完毕后,再继续追加这个sql,此处表示添加查询条件id in (字表中id小于3的数据)
wapper.inSql("id", "select 字表字段 from 关联的子表名 where id<3");
//wapper.notInSql();
wapper.groupBy("字段名1", "字段名2");//分组
//排序,参数二:是否ASC排序,参数三:排序字段名
wapper.orderBy(true, true, "排序字段名");
//wapper.orderByDesc();
//wapper.orderByAsc();
//wapper.and();//and嵌套
//wapper.or();//添加or条件
//wapper.having();
//wapper.nested();//正常嵌套不带AND或OR
//wapper.lambda();
//wapper.apply();//拼接sql
//wapper.last();//无视优化规则,直接拼接sql到最后
//wapper.exists();//拼接EXISTS
//wapper.notExists();
//3.xxxMapper调用方法执行语句
TblMockDto dto = tblMockMapper.selectOne(wapper);
// tblMockMapper.selectList(wapper);//查询多个
//tblMockMapper.selectMaps(wapper);
tblMockMapper.selectCount(wapper);//查询条数
//返回object类型
List<Object> oList = tblMockMapper.selectObjs(wapper);
//返回指定字段queryWapper查询
QueryWrapper<TblMockDto> queryWrapper=new QueryWrapper();
queryWrapper.select("id","name").gt("salary",3500).like("name","小");
List<TblMockDto> tbList = tblMockMapper.selectList(queryWrapper);
return "success";
}
/**
* UpadateWapper 使用示例
* @return
*/
@GetMapping(value = {"/updateWapperTest"}, produces = "application/json")
public Object updateWapperTest() {
UpdateWrapper<TblMockDto> updateWrapper = new UpdateWrapper<>();
TblMockDto dto = new TblMockDto();
dto.setMockField("cccc");
updateWrapper.eq("id", 6);
int affectRows = tblMockMapper.update(dto, updateWrapper);
if (affectRows > 0) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
//update2 删除示例
UpdateWrapper<TblMockDto> updateWrapper2=new UpdateWrapper<>();
updateWrapper.apply("DATE_FORMAT(birthday,'%Y-%m-%d')<={0}","1990-01-01").eq("departmentId",2);
int affectRows2=tblMockMapper.delete(updateWrapper2);
return "success";
}
3. LambdaQueryWrapper 与 QueryChainWrapper
- MybatisPlus 提供类 LambdaQueryWrappe查询条件构造器,提供了一种通过 lambda 表达式来对查询条件进行组合的方式,使得代码更加简洁易懂
- LambdaQueryWrapper 支持多种操作符和方法,包括比较操作、like 操作、in 操作、exists 操作等,同时也支持多个条件之间的逻辑操作,如 and、or 等
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Override
public List<User> selectUserList(String name, Integer age) {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>()
.like(StringUtils.isNotBlank(name), User::getName, name)
.eq(age != null, User::getAge, age);
return list(wrapper);
}
}
- QueryChainWrapper 是 MybatisPlus 提供的另一个实用工具类,可以提高查询效率和代码简洁度。它可以将多个查询条件进行链式调用,避免重复的查询语句和参数设置
IPage<User> page = new Page<>(1, 10);
QueryChainWrapper<User> queryWrapper = new QueryChainWrapper<>(userMapper).eq("name", "张三").ge("age", 18);
IPage<User> userPage = queryWrapper.selectPage(page);
4. 字段填充
- 解释: 例如上方,表中有create_time与update_time字段,我们希望在向数据库插入或更新数据时,可以自动填充着两个字段的数据,而不是手动的set进去
- 方式:
a.数据库级别: 建表时设置这两个字段默认值为: CURRENT_TIMESTAMP, 例如: ALTER TABLE
tbl_mock
MODIFY COLUMNcreate_time
datetime NULL DEFAULT COMMENT ‘创建时间’ AFTERmock_field
,
MODIFY COLUMNupdate_time
datetime NULL DEFAULT COMMENT ‘更新时间’ AFTERcreate_time
;
b. mybatisPlus 也提供了自动填充功能,对应字段上使用 @TableField注解修饰,并编写字段填充处理器
- 自定义字段填充处理器
package com.lumous.pms.adapter.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* 自定义mybatisPlus字段填充处理器
* (该处理器的作用:例如在数据库表中有create_time,与update_time 字段,以前使用数据库级别
* 插入或更新数据是通过数据库表设置可以提供默认值自动填充,在使用mybatisPlus后可以使用
* 代码级别的自动填充,在create_time与update_time字段对应的属性上使用@TableField注解
* 修饰,设置填充策略(插入还是更新或插入更新都会自动填充)该属性值)
*/
@Component
public class MybatisPluseMetaObjectHandler implements MetaObjectHandler {
//插入时自动填充策略
//此处当前方法表示,在向数据库插入数据时,会自动将当前时间new Data()填充
//到"create_time" 与 "update_time" 字段上
@Override
public void insertFill(MetaObject metaObject) {
//setFieldValByName()方法,
//参数一: 对应数据库中需要自动填充的属性(注意不是字段而是该字段对应的属性)
//参数二: new Date() 填充该字段的值
//参数三: 入参 MetaObject
this.setFieldValByName("createTime", new Date(), metaObject);
this.setFieldValByName("updateTime", new Date(), metaObject);
}
//更新时填充策略
//当前方法表示在更新数据库表示,会自动把 new Data()当前时间, 填充到"update_time" 字段上
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
- 表对应实体类的对应字段修饰示例
//@TableField 字段自动填充策略(DEFAULT默认,不填充)
//注意点:使用@TableField时需要编写处理器,也就是填充该字段的值的生成
@TableField(fill= FieldFill.INSERT)
private Date createTime;
@TableField(fill= FieldFill.INSERT_UPDATE)
private Date updateTime;
5. 删除与配置逻辑删除
- 解释
/**
* 删除与逻辑删除使用解释
* @return
*/
@GetMapping(value = {"/delTest"}, produces = "application/json")
public Object delTest() {
//1.删除
//传递Wapper条件删除
//tblMockMapper.delete();
//根据id删除
tblMockMapper.deleteById(1);
//多id删除
tblMockMapper.deleteBatchIds(Arrays.asList(1, 2, 3));
//map条件删除
Map<String, Object> map = new HashMap<>();
map.put("mockField", "bbb");
tblMockMapper.deleteByMap(map);
//2.逻辑删除,前提条件
//2.1注册逻辑删除拦截器组件
//2.2配置文件中: mybatisPlus下的global-config配置代表删除与存在的值
// logic-delete-value: 0 #表示存在
// logic-not-delete-value: 1 #表示删除
//配置完毕后,再去执行删除操作,实际执行的是更新操作,将数据标识更新为删除
//并且查询时会自动过滤掉被逻辑删除的数据
return "success";
}
6. 配置乐观锁
/**
* MP乐观锁使用解释
* @return
*/
@GetMapping(value = {"/versionLockTest"}, produces = "application/json")
public Object versionLockTest() {
TblMockDto dto = tblMockMapper.selectById(1);
dto.setMockField("更新");
//1.以前使用version乐观锁时,首先拿到version字段值,在更新时
//version作为条件执行,并对该version字段累计+1,假设在更新
//时使用version并没有更新成功,说明该数据被其它请求更新过了
//需要重新读取version字段
//2.在MybatisPlus后,表中提供version字段,字段对应的属性上使用@Version修饰
//注册乐观锁组件后,更新操作会自动携带version版本,别忘了开启事物
int count = tblMockMapper.updateById(dto);
return "success:" + count;
}
7. 分页查询示例
/**
* 分页示例
* @return
*/
@GetMapping(value = {"/selectPagHelperTest"}, produces = "application/json")
public Object selectPagHelperTest() {
//1.分页方式: 原生limit,PageHelper第三方插件,Mp也内置了分页插件
//2.使用MP分页需要配置分页插件
//3.MybatisPlus分页使用
//3.1创建Page注意是mybatisPlus的,参数一:查询第几页,参数二:每页几条
Page<TblMockDto> page = new Page<>(1, 5);
//3.2selectPage方法进行分页查询,参数一:分页条件Page对象,参数二:Wrapper条件如果没有传null
tblMockMapper.selectPage(page, null);
//3.3查询执行后结果集会设置到上面的Page中
List<TblMockDto> dtoList = page.getRecords();//获取查询到的数据
long ps = page.getPages();//总页数
long total = page.getTotal();//总条数
boolean down = page.hasNext();//是否有下一页
boolean up = page.hasPrevious();//是否有上一页
page.getCurrent();//当前页
return "success";
}
8. MybatisPlus 主键生成解释
//@TableId修饰主键属性指定主键生成策略(除了使用该注解设置以外,也可以在yml中配置)
//ID_WORKER:雪花默认
//AUTO:自增(注意点如果使用该类型,建表时要设置自增sql)
//NONE:不设置
//INPUT:手动输入(该方式创建对象时需要设置id)
//UUID: uuid
//ID_WORKER_STR: ID_WORKER的字符串表示
@TableId(type = IdType.ID_WORKER)
private Integer id;
9. 动态表名
- 在某些场景下,需要动态指定表名。MybatisPlus 提供了 @TableName 注解和 ISqlInjector 接口,可以动态地指定表名和 SQL 语句
- @TableName 示例: User 实体类添加 @TableName 注解,在 @TableName 注解中指定了表名为 user_{year},其中 {year} 表示一个占位符,可以在运行时动态设置其值
@Data
@TableName(value = "user_${year}")
public class User {
private Long id;
private String name;
private Integer age;
@TableField(exist = false)
private Integer year;
}
- ISqlInjector 接口实现示例: 可以重写其方法来实现动态 SQL 语句: 重写类ISqlInjector 接口的 getMethodList 方法,新增了一个 InsertWithTable 方法来实现动态插入数据。在该方法中使用 RawSqlSource 来生成 SQL 语句,通过动态绑定表名的方式来实现动态表名
@Component
public class CustomSqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = new ArrayList<>(super.getMethodList(mapperClass));
methodList.add(new InsertWithTable());
return methodList;
}
public static class InsertWithTable extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
SqlSource sqlSource = new RawSqlSource(configuration, "insert into ${tableName}(name,age) values(#{name},#{age})",
modelClass) {
@Override
public BoundSql getBoundSql(Object parameterObject) {
BoundSql boundSql = super.getBoundSql(parameterObject);
MetaObject metaObject = SystemMetaObject.forObject(parameterObject);
String tableName = (String) metaObject.getValue("tableName");
String sql = boundSql.getSql().replace("${tableName}", tableName);
return new BoundSql(configuration, sql, boundSql.getParameterMappings(),
boundSql.getParameterObject());
}
};
return this.addInsertMappedStatement(mapperClass, modelClass, "insertWithTable", sqlSource);
}
}
}