MybatisPlus：SQL语句打印、SQL分析、自定义主键值策略填充（IdType.INPUT）、动态表名、多租户、枚举、类型处理器、连表自定义SQL（使用wrapper）
基于SpringBoot学习1 - MybatisPlus使用、SQL语句日志打印、SQL分析、代码生成器、自定义主键值策略填充（IdType.INPUT）、动态表名、多租户、枚举、类型处理器、连表自定义SQL（使用wrapper）
·
文章目录
- 1. 简单使用以及配置 - 带分页配置
- 2. 用法
- 2.0 Wrapper属性
- 2.1 @TableId - 自定义主键生成策略
- 2.2 @TableField - 自定义字段值填充
- 2.3 锁 - 乐观锁、悲观锁
- 2.4 DML、DQL操作
- 2.5 性能分析、SQL打印插件（p6spy） - 支持SQL格式化
- 2.6 代码生成器
- 2.7 自定义主键值策略填充（IdType.INPUT）
- 2.8 动态表名-根据入参决定执行哪个表-会影响手写的SQL语句且手写SQL记得写表别名 - 手写SQL中所有的表名都会经过这层
- 2.9 多租户 - 手写SQL中所有的表名都会经过这层 - 主表的租户字段加在where上，连表的租户字段加在on上
- 2.10 枚举@EnumValue
- 2.11 类型处理器BaseTypeHandler - 必须开启TableName.autoResultMap=true
- 2.12 连表自定义SQL - 配合wrapper使用
1. 简单使用以及配置 - 带分页配置
pom.xml - 依赖
<dependencies>
<!--web功能的springboot -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--JavaBean的属性get.set生成器 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--SpringBoot自动配置mybatis-plus的默认配置 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<!--SpringBoot热加载 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<!--SpringBoot单元测试 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
application.yml
spring:
application:
name: mybatisplusStudy
datasource:
data-password: root
password: root
username: 123456
url: jdbc:mysql://localhost:3306/interview?serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
server:
servlet:
context-path: /mybatisplus
port: 80
mybatis-plus:
# mapper文件的位置
mapper-locations: classpath:/mappers/*Mapper.xml
# 哪个包下的类名可以在mapper文件中不用写全限定名，写类名即可
type-aliases-package: top.linruchang.mybatisplusstudy.dto
configuration:
# 默认true - 数据库字段名 与 JavaBean属性名的对应关系
# 查询数据库时
# 1. true 数据库字段名中的下划线去掉并toUpperCase = JavaBean属性名.toUpperCase
# 2. false 数据库字段名.toUpperCase = JavaBean属性名.toUpperCase
# update、set数据库时
# 1. true 只要是驼峰的JavaBean字段，会将驼峰的大写字母 变成 _小写字母
# 2. false 数据库字段 = JavaBean属性字段
map-underscore-to-camel-case: false
#打印SQL日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
# @FieldId 默认类型为自增长
id-type: auto
# @TableName如果要使用需要设置@TableName的属性是keepGlobalPrefix为true
table-prefix: test_
MyConfig.java - 使MyBatis-plus分页起效
@Configuration
public class MyConfig {
@Bean
public PaginationInterceptor getPaginationInterceptor() {
return new PaginationInterceptor();
}
}
BaseDTO.java、ClassType.java
@Data
public class BaseDTO {
@TableId(type=IdType.AUTO)
Integer id;
}
@TableName("class_type")
@Data
@Builder
public class ClassType extends BaseDTO {
@TableField("classNo")
Integer classNo;
@TableField("description")
String description;
}
ClassTypeDao.java
@Mapper
public interface ClassTypeDao extends BaseMapper<ClassType>{
}
测试
@SpringBootTest
class SpringbootMybatisplusApplicationTests {
@Autowired
ClassTypeDao classTypeDao;
@Test
public void test2() {
IPage<ClassType> classTypeIPage = classTypeDao.selectPage(new Page<>(1, 1), new QueryWrapper<ClassType>().gt("id", 0));
List<ClassType> records = classTypeIPage.getRecords();
records.forEach(System.out::println);
}
}
2. 用法
2.0 Wrapper属性
# wrapper.getSqlSegment() === 无where关键字
(sys_user.id LIKE #{ew.paramNameValuePairs.MPGENVAL1} AND friendly_link.name = #{ew.paramNameValuePairs.MPGENVAL2} OR nick_name LIKE #{ew.paramNameValuePairs.MPGENVAL3})
# wrapper.wrapper.getCustomSqlSegment() === 有where关键字
WHERE (sys_user.id LIKE #{ew.paramNameValuePairs.MPGENVAL1} AND friendly_link.name = #{ew.paramNameValuePairs.MPGENVAL2} OR nick_name LIKE #{ew.paramNameValuePairs.MPGENVAL3})
# wrapper.getTargetSql() == 带问号参数sql非mybatis的手写sql == 即JDBC形式的SQL
(sys_user.id LIKE ? AND friendly_link.name = ? OR nick_name LIKE ?)
2.1 @TableId - 自定义主键生成策略
@TableName("student")
public class Student {
//如果数据库该表的主键不是自增长，且不能为null，则用实体类插入数据到数据库时主键必须有值，否则报错
@TableId(type=IdType.INPUT)
Integer id;
}
2.2 @TableField - 自定义字段值填充
实现填充处理器MetaObjectHandler
@Component
@Slf4j
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
log.info("插入操作。。。。。。。。。。。。");
this.setInsertFieldValByName("updateTime", new Date(), metaObject);
this.setInsertFieldValByName("createTime", new Date(), metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
log.info("更新操作。。。。。。。。。。。。");
this.setInsertFieldValByName("updateTime", new Date(), metaObject);
}
}
添加填充注解属性
@TableName("student")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {
@TableField("name")
String name;
@TableField("sport")
String sport;
//FieldFill.DEFAULT默认填充不进行处理
@TableField(value="create_time", fill = FieldFill.INSERT)
Date createTime;
@TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
Date updateTime;
}
测试
@Test
public void test7() {
Student student = new Student();
student.setId("fbc629c6703f0d8188476feb33c1f41f");
student.setName("ewoi");
student.setSport("8764431");
int flag = studentDao.updateById(student);
System.out.println(flag);
}
2.3 锁 - 乐观锁、悲观锁
2.3.1 @Version - 乐观锁
开启乐观锁SQL拦截器
@Configuration
@EnableTransactionManagement
public class MyConfig {
@Bean
public OptimisticLockerInterceptor getOptimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
}
添加字段version字段
@Data
public class BaseDTO {
@TableId(type=IdType.UUID)
String id;
//乐观锁注解开启
@Version
Integer version;
}
@TableName("student")
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {
@TableField("name")
String name;
@TableField("sport")
String sport;
@TableField(value="create_time", fill = FieldFill.INSERT)
Date createTime;
@TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
Date updateTime;
}
测试
@Test
public void test8() {
Student student = studentDao.selectById("fsfsd");
System.out.println(student);
student.setSport("做爱做的侍寝111");
studentDao.updateById(student);
student = studentDao.selectById("fsfsd");
System.out.println(student);
}
2.4 DML、DQL操作
实体类Student以及与该表交互的StudentDao
@Data
public class BaseDTO {
@TableId(type=IdType.UUID)
String id;
}
@TableName("student")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {
@TableField("name")
String name;
@TableField("sport")
String sport;
}
@Mapper
public interface StudentDao extends BaseMapper<Student>{
}
2.4.1 DML - 增删改 - 物理
插入数据
@Autowired
StudentDao studentDao
@Test
public void test3() {
Student student = new Student();
student.setName("分公司的");
student.setSport("泡妞");
int flag = studentDao.insert(student);
System.out.println(flag);
}
更新数据
@Test
public void test4() {
Student student = new Student();
student.setId("4c6b03074bbbab1ae361cb21f994d23b");
student.setName("werwe");
student.setSport("泡妞111111111");
int flag = studentDao.updateById(student);
System.out.println(flag);
}
删除数据
@Test
public void test5() {
int flag = studentDao.deleteById("4c6b03074bbbab1ae361cb21f994d23b");
System.out.println(flag);
}
批量删除数据
@Test
public void test11() {
int flag = studentDao.deleteBatchIds(Arrays.asList("1", "2"));
System.out.println(flag);
}
2.4.2 DML - 逻辑删除 - @TableLogic - 仅对注入MyBatisPlus注入的SQL生效，自定义SQL不生效
添加字段
@Data
public class BaseDTO {
@TableId(type=IdType.UUID)
String id;
//乐观锁注解开启
@Version
Integer version;
//逻辑删除标识符
@TableLogic
Integer deleted;
}
开启配置
@Configuration
@EnableTransactionManagement
public class MyConfig {
//开启SQL字段自动填充拦截器
@Bean
public PaginationInterceptor getPaginationInterceptor() {
return new PaginationInterceptor();
}
//开启乐观锁
@Bean
public OptimisticLockerInterceptor getOptimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
//开启逻辑删除
@Bean
public LogicDeleteByIdWithFill getLogicDeleteByIdWithFill() {
return new LogicDeleteByIdWithFill();
}
}
设置逻辑删除的值定义
server:
port: 80
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
devtools:
restart:
enabled: true
mybatis-plus:
mapper-locations: classpath:/mapper/*Mapper.xml
type-aliases-package: top.linruchang.springbootmybatisplus.dto
global-config:
db-config:
table-prefix: test_
id-type: auto
# 设置@TableLogic修饰的注解字段，逻辑已删除=1 逻辑未删除=0
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
map-underscore-to-camel-case: true
测试
@Test
public void test12() {
int flag = studentDao.deleteBatchIds(Arrays.asList("5", "4", "3"));
System.out.println(flag);
List<Student> students = studentDao.selectBatchIds(Arrays.asList("5", "4", "3"));
System.out.println(students.size());
}
2.4.3 DQL - 查
@Data
public class BaseDTO {
@TableId(type=IdType.UUID)
String id;
//乐观锁注解开启
@Version
Integer version;
}
@TableName("student")
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {
@TableField("name")
String name;
@TableField("sport")
String sport;
@TableField(value="create_time", fill = FieldFill.INSERT)
Date createTime;
@TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
Date updateTime;
}
通过多个ID查询多条记录
@Test
public void test9() {
List<Student> students = studentDao.selectBatchIds(Arrays.asList("1", "2"));
System.out.println(students);
}
通过多个字段查询记录
@Test
public void test10() {
HashMap map = new HashMap();
map.put("sport", "泡妞");
List<Student> students = studentDao.selectByMap(map);
students.forEach(System.out::println);
}
2.4.4 条件构造器
@Test
public void test13() {
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
List<Object> students = studentDao.selectObjs(queryWrapper.eq("name", "ewoi").isNull("version"));
students.forEach(System.out::println);
}
@Test
public void test14() {
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper = queryWrapper.like("sport", "侍寝").likeLeft("name", "公司的");
List<Object> students = studentDao.selectObjs(queryWrapper);
students.forEach(System.out::println);
}
@Test
public void test15() {
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper = queryWrapper.notInSql("id", "select id from student where name = 'rty'");
List<Student> students = studentDao.selectList(queryWrapper);
students.forEach(System.out::println);
}
2.5 性能分析、SQL打印插件（p6spy） - 支持SQL格式化
Mybatis-plus官方规则
注意！
driver-class-name 为 p6spy 提供的驱动类
url 前缀为 jdbc:p6spy 跟着冒号为对应数据库连接地址
打印出sql为null,在excludecategories增加commit
批量操作不打印sql,去除excludecategories中的batch
批量操作打印重复的问题请使用MybatisPlusLogFactory (3.2.1新增）
该插件有性能损耗，不建议生产环境使用。
引进依赖
<!-- SQL性能分析插件 -->
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.0</version>
</dependency>
applicaiton.yml - 配置
server:
port: 80
spring:
datasource:
username: root
password: root
# 注意这里细节
url: jdbc:p6spy:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false
# driver-class-name: com.mysql.cj.jdbc.Driver
# 注意这里细节
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
devtools:
restart:
enabled: true
mybatis-plus:
mapper-locations: classpath:/mapper/*Mapper.xml
type-aliases-package: top.linruchang.springbootmybatisplus.dto
global-config:
db-config:
table-prefix: test_
id-type: auto
# 设置@TableLogic修饰的注解字段，逻辑已删除=1 逻辑未删除=0
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
map-underscore-to-camel-case: true
EnhaceP6SpyLogger.java
public class EnhaceP6SpyLogger extends P6SpyLogger {
/**
* 参考com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger#formatMessage(int, java.lang.String, long, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
*/
@Override
public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
//格式化SQL
String sqlContent = sql.replaceAll("[\\s]+", " ");
sqlContent = SqlUtil.formatSql(sqlContent);
return StringUtils.isNotBlank(sql) ? " Consume Time：" + elapsed + " ms " + now +
"\n Execute SQL：\n" + sqlContent + "\n" : "";
}
}
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
# 自定义日志打印
# 官网的SQL没有格式化
#logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
# SQL格式化输出
logMessageFormat=work.linruchang.mybatisplussamplestest.config.mybatisplus.EnhaceP6SpyLogger
#日志输出到控制台
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
测试
@Test
public void test10() {
HashMap map = new HashMap();
map.put("sport", "泡妞");
List<Student> students = studentDao.selectByMap(map);
students.forEach(System.out::println);
}
格式化前-配置官网的com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
格式化后：配置增强的EnhaceP6SpyLogger
2.6 代码生成器
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<!--代码生成器-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.2</version>
</dependency>
<!--自动填充JavaBean的 Getter/Setter方法
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--api文档接口生成 -->
<!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!--模板引擎 - myabtisplus代码生成器需要的模板引擎，不引进来会报错，可以生成代码删除这个就行了-->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-thymeleaf -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
@Test
public void test16() {
// 代码生成器
AutoGenerator mpg = new AutoGenerator();
// 1. 全局配置
GlobalConfig gc = new GlobalConfig();
//生成文件的输入目录
String projectPath = System.getProperty("user.dir");
gc.setOutputDir(projectPath + "/src/main/java");
gc.setAuthor("LinRuChang");
//生成文件后不打开文件管理器级【我的电脑】
gc.setOpen(false);
//添加swagger注解在生成文件中
gc.setSwagger2(true);
//设置文件生成日期注释所使用的日期类
gc.setDateType(DateType.ONLY_DATE);
//设置插入记录时，ID主键生成的值
gc.setIdType(IdType.ASSIGN_UUID);
//生成的文件不覆盖已有的文件
gc.setFileOverride(false);
//service类，mapper类即dao类，mapper的xml文件的名字 %s表示表名
gc.setServiceName("%sService");
gc.setMapperName("%sDao");
gc.setXmlName("%sMapper");
mpg.setGlobalConfig(gc);
// 2. 数据源配置
DataSourceConfig dsc = new DataSourceConfig();
dsc.setUrl("jdbc:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false&characterEncoding=utf8");
dsc.setDriverName("com.mysql.cj.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("root");
dsc.setDbType(DbType.MYSQL);
mpg.setDataSource(dsc);
// 3. 包配置
PackageConfig pc = new PackageConfig();
//将该模块的文件放在哪个包里
pc.setParent("top.linruchang.springbootmybatisautogenerator");
//模块名 - 可以不配 -
pc.setModuleName("");
//控制层、实体类层、service层，dao层的包名设置
pc.setController("controller");
pc.setEntity("dto");
pc.setService("service");
pc.setMapper("dao");
mpg.setPackageInfo(pc);
// 3. 策略配置 - 实体类、controller类生成规则
StrategyConfig strategy = new StrategyConfig();
//实体类名的设置 - 表名转驼峰命名
strategy.setNaming(NamingStrategy.underline_to_camel);
//实体类的字段名的设置 - 字段名名转驼峰命名
strategy.setColumnNaming(NamingStrategy.underline_to_camel);
//是否使用lombok注解在实体类中
strategy.setEntityLombokModel(true);
//控制器中的含有@RestController注解 - 常用于前后端分离
strategy.setRestControllerStyle(true);
// 写于父类中的公共字段 - 实体类中的公共字段
strategy.setSuperEntityColumns("id", "version", "deleted");
//注意这个类需要自己写哦 - 即写上面定义的字段
strategy.setSuperEntityClass(BaseDTO.class);
//TODO 表名 - 准备映射的表
strategy.setInclude("class_type", "student");
strategy.setControllerMappingHyphenStyle(true);
//逻辑删除字段
strategy.setLogicDeleteFieldName("deleted");
//乐观锁字段
strategy.setVersionFieldName("version");
//字段值填充
TableFill createTime = new TableFill("create_time", FieldFill.INSERT);
TableFill updateTime = new TableFill("update_time", FieldFill.INSERT_UPDATE);
strategy.setTableFillList(Arrays.asList(createTime, updateTime));
mpg.setStrategy(strategy);
//执行代码生成
mpg.execute();
}
2.7 自定义主键值策略填充（IdType.INPUT）
注意： 一旦设置了keyGenerator，如果没有全局指定ID的生成策略或在基类中的@TableId中指定主键的生成策略，则默认为IdType.INPUT，则全部的主键值都由keyGenerator进行生成
ArticleTag.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("article_tag")
//用于com.baomidou.mybatisplus.core.incrementer.IKeyGenerator#executeSql的入参
@KeySequence("ArticleTag")
public class ArticleTag extends BaseDB implements Serializable {
/** 主键 - 记录插入自动填充主键处理{@path application.yml} */
@TableId(type= IdType.INPUT)
private String id;
/**
* 版本号
*/
private static final long serialVersionUID = -8898734581249698571L;
/**标签名 - 限制15个字*/
private String tagName;
/**标签所属的用户 - sys_user表的ID*/
private String userId;
/**标签是否违规：0未违规 1违规 - 违规不可显示*/
private Integer isViolation;
}
MyBatisPlusConfig.java
@Configuration
public class MyBatisPlusConfig {
/**
* 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
* @return
*/
@Bean
public IKeyGenerator keyGenerator() {
return new IKeyGenerator(){
@Override
public String executeSql(String incrementerName) {
incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))",incrementerName,incrementerName );
}
@Override
public DbType dbType() {
return DbType.MYSQL;
}
};
}
}
ArticleTagDao.java
@Mapper
public interface ArticleTagDao extends BaseMapper<ArticleTag> {
}
ArticleTagService.java
@Service
public class ArticleTagService extends ServiceImpl<ArticleTagDao, ArticleTag> {
}
ArticleTagServiceTest.java
@SpringBootTest
class ArticleTagServiceTest {
@Autowired
ArticleTagService articleTagService;
@Test
public void keySequenceTest1() {
ArticleTag articleTag = new ArticleTag();
articleTag.setTagName("你好");
boolean insert = articleTag.insert();
Console.log("插入情况：{}", insert);
Console.log("实体情况：{}", articleTag);
}
}
2.8 动态表名-根据入参决定执行哪个表-会影响手写的SQL语句且手写SQL记得写表别名 - 手写SQL中所有的表名都会经过这层
内置SQL
MyBatisPlusConfig.java
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//防全表更新与删除插件
interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
//乐观锁，字段添加version注解
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
//动态表单
interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
Console.log("动态表单执行成功");
}, (sql, tableName) -> {
Console.log("当前执行的SQL：{}\n 表名：{}", sql,tableName);
Map<String, Object> requestData = RequestDataHelper.getRequestData();
String tableNum = null;
if(CollUtil.isNotEmpty(requestData)) {
String mapValueContent = requestData.values()
.stream()
.map(Object::toString)
.reduce(StrUtil.EMPTY, (t1, t2) -> {
return StrUtil.join(StrUtil.DOT, t1, t2);
});
int mapHashValue = HashUtil.fnvHash(mapValueContent);
int modulus = mapHashValue % 3;
tableNum = String.valueOf(modulus);
Console.log("当前参数的Hash值：{}: 取模后：{}",mapHashValue,modulus);
}
String resultTableName = tableName;
if(StrUtil.isNotBlank(tableNum)) {
resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
}
RequestDataHelper.setRequestData(null);
return resultTableName;
}));
return interceptor;
}
}
SysUser.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
private static final long serialVersionUID = 2095940921263481761L;
/**
* 用户昵称 - 如果没有设置昵称直接使用账户名显示
*/
private String nickName;
/**
* 头像图片地址
*/
private String headUrl;
/**
* 账户名
*/
private String loginName;
/**
* 手机号
*/
private String phoneNumber;
/**
* 邮箱
*/
private String email;
/**
* 性别
*/
private Integer gender;
@TableField("gender")
private GenderEnum genderEnum;
/**
* 个性签名
*/
private String personalMotto;
//@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
//private String myArticleCategory;
/**
* 最近登录时间 - 格式 - yyyyMMddHHmmss
*/
private String lastLoginTime;
/**
* 登录状态：0未登录 1单设备登陆 2多设备登陆
*/
private Integer loginStatus;
/** 账户禁用状态：0账户可使用 1账户不可使用（封号） */
private Integer disabledStatus;
/**
* 密码
*/
private String password;
}
SysUserDao.java
@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}
SysUserService.java
@Service
public class SysUserService extends ServiceImpl<SysUserDao, SysUser> {
}
SysUserServiceTest.java
@SpringBootTest
class SysUserServiceTest {
@Test
public void test12() {
List<String> nicknames = null;
Console.log("\n========sys_user=========\n");
RequestDataHelper.setRequestData(null);
List<SysUser> sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
nicknames = sysUsers.stream()
.map(SysUser::getNickName)
.collect(Collectors.toList());
Console.log(sysUsers);
Console.log(nicknames);
Console.log("\n========sys_user_0=========\n");
RequestDataHelper.setRequestData(new Dict()
.set("id","4"));
sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
nicknames = sysUsers.stream()
.map(SysUser::getNickName)
.collect(Collectors.toList());
Console.log(sysUsers);
Console.log(nicknames);
Console.log("\n========sys_user_1=========\n");
RequestDataHelper.setRequestData(new Dict()
.set("id","8"));
sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
nicknames = sysUsers.stream()
.map(SysUser::getNickName)
.collect(Collectors.toList());
Console.log(sysUsers);
Console.log(nicknames);
Console.log("\n========sys_user_2=========\n");
RequestDataHelper.setRequestData(new Dict()
.set("id","5"));
sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
nicknames = sysUsers.stream()
.map(SysUser::getNickName)
.collect(Collectors.toList());
Console.log(sysUsers);
Console.log(nicknames);
}
}
执行结果
手写SQL - 必须起表别名因为动态表单插件仅改from表名，where属性的表名不会改
MyBatisPlusConfig.java
@Configuration
public class MyBatisPlusConfig {
/**
* 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
*
* @return
*/
@Bean
public IKeyGenerator keyGenerator() {
return new IKeyGenerator() {
@Override
public String executeSql(String incrementerName) {
incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
}
@Override
public DbType dbType() {
return DbType.MYSQL;
}
};
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//动态表单
interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
Console.log("动态表单执行成功");
}, (sql, tableName) -> {
Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);
Map<String, Object> requestData = RequestDataHelper.getRequestData();
String tableNum = null;
if (CollUtil.isNotEmpty(requestData)) {
String mapValueContent = requestData.values()
.stream()
.map(Object::toString)
.reduce(StrUtil.EMPTY, (t1, t2) -> {
return StrUtil.join(StrUtil.DOT, t1, t2);
});
int mapHashValue = HashUtil.fnvHash(mapValueContent);
int modulus = mapHashValue % 3;
tableNum = String.valueOf(modulus);
Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
}
String resultTableName = tableName;
if (StrUtil.isNotBlank(tableNum)) {
resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
}
return resultTableName;
}));
//乐观锁，字段添加version注解
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
//防全表更新与删除插件
interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
//租户模式
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
/**
* 租户字段的值 每执行一次SQL一次都会判断一次
* @return
*/
@Override
public Expression getTenantId() {
Object tenant = RequestDataHelper.getRequestData("tenant");
return tenant == null ? null : new StringValue(tenant.toString());
}
/**
* 租户的字段名
* @return
*/
@Override
public String getTenantIdColumn() {
return "tenant";
}
/**
* 某表的SQL是否忽略带上租户字段 每执行一次SQL一次都会判断一次
* @param tableName 表名 这里的表名可能是动态表单的表名注意区别
* @return
*/
@Override
public boolean ignoreTable(String tableName) {
//无租户值则直接忽略拼接租户字段
Object tenant = RequestDataHelper.getRequestData("tenant");
boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);
//根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
boolean tanantEntityFlag = tableInfo.getFieldList().stream()
.anyMatch(tableFieldInfo -> {
return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
});
//需要租户的表
return !tanantEntityFlag && (!tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
.map(TableInfoHelper::getTableInfo)
.map(TableInfo::getTableName)
.anyMatch(t -> StrUtil.contains(tableName,t)));
}
}));
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
/**
* SQL注入器
*
* @return
*/
@Bean
public ISqlInjector enhanceISqlInjector() {
return new DefaultSqlInjector() {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
methodList.add(new DeleteByIdPhysical());
methodList.add(new DeleteByIdLogic());
return methodList;
}
};
}
}
SysUser.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
private static final long serialVersionUID = 2095940921263481761L;
/**
* 用户昵称 - 如果没有设置昵称直接使用账户名显示
*/
private String nickName;
/**
* 头像图片地址
*/
private String headUrl;
/**
* 账户名
*/
private String loginName;
/**
* 手机号
*/
private String phoneNumber;
/**
* 邮箱
*/
private String email;
/**
* 性别
*/
private Integer gender;
@TableField("gender")
private GenderEnum genderEnum;
/**
* 个性签名
*/
private String personalMotto;
//@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
//private String myArticleCategory;
/**
* 最近登录时间 - 格式 - yyyyMMddHHmmss
*/
private String lastLoginTime;
/**
* 登录状态：0未登录 1单设备登陆 2多设备登陆
*/
private Integer loginStatus;
/** 账户禁用状态：0账户可使用 1账户不可使用（封号） */
private Integer disabledStatus;
/**
* 密码
*/
private String password;
/**
* 租户
*/
private String tenant;
}
FriendlyLink.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("friendly_link")
public class FriendlyLink extends BaseDB{
private static final long serialVersionUID = -4714261187453073302L;
/** 所属用户 - 空或空字符则说明是系统链接，有则说明用户链接 */
private String userId;
/** 链接名 */
private String name;
/** 链接地址 */
private String url;
/** 排序 - 值越低越靠前显示 */
private Integer sort;
/** 链接类型：系统1 个人2 => 参考{@link LinkTypeEnums#code}*/
private String type;
/** 该友情链式是否跳转：0不跳转 1跳转 默认为1=> 参考{@link FlagEnums#code} */
private String jumpFlag;
/**其他参数：JSON格式字符串*/
private String params;
/**
* 租户
*/
private String tenant;
}
SysUserDao.java
public interface SysUserDao extends MyBaseMapper<SysUser> {
SysUser findOneById(String id);
Map findOneByIdAndFriendLink(String id);
Map findOneByIdAndFriendLink2(String id);
}
SysUserMapper.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="work.linruchang.mybatisplussamplestest.dao.SysUserDao">
<select id="findOneById" parameterType="string" resultType="work.linruchang.mybatisplussamplestest.bean.SysUser">
select * from sys_user where id = #{id}
</select>
<select id="findOneByIdAndFriendLink" parameterType="string" resultType="java.util.Map">
select *
from sys_user
left join friendly_link on friendly_link.user_id = sys_user.id
where sys_user.id = #{id}
</select>
<select id="findOneByIdAndFriendLink2" parameterType="string" resultType="java.util.Map">
select *
from sys_user as sys_user
left join friendly_link as friendly_link on friendly_link.user_id = sys_user.id
where sys_user.id = #{id}
</select>
</mapper>
SysUserServiceTest.java
@SpringBootTest
class SysUserServiceTest {
@Test
public void test15() {
Map sysUser = sysUserDao.findOneByIdAndFriendLink("d32478155b6530951cf6b3da56848d5c");
Console.log(sysUser);
RequestDataHelper.setRequestData(new Dict().set("tenant", 1));
sysUser = sysUserDao.findOneByIdAndFriendLink("d32478155b6530951cf6b3da56848d5c");
Console.log(sysUser);
}
@Test
public void test16() {
Map sysUser = sysUserDao.findOneByIdAndFriendLink2("d32478155b6530951cf6b3da56848d5c");
Console.log(sysUser);
RequestDataHelper.setRequestData(new Dict().set("tenant", 1));
sysUser = sysUserDao.findOneByIdAndFriendLink2("d32478155b6530951cf6b3da56848d5c");
Console.log(sysUser);
}
}
上面是：findOneByIdAndFriendLink，下面是：findOneByIdAndFriendLink2。避免Bug请手写SQL的时候起表别名
2.9 多租户 - 手写SQL中所有的表名都会经过这层 - 主表的租户字段加在where上，连表的租户字段加在on上
查看动态表名-手写SQL的代码
MyBatisPlusConfig.java
/**
* 作用：
*
* @author LinRuChang
* @version 1.0
* @date 2022/06/12
* @since 1.8
**/
@Configuration
public class MyBatisPlusConfig {
/**
* 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
*
* @return
*/
@Bean
public IKeyGenerator keyGenerator() {
return new IKeyGenerator() {
@Override
public String executeSql(String incrementerName) {
incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
}
@Override
public DbType dbType() {
return DbType.MYSQL;
}
};
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//动态表单
interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
Console.log("动态表单执行成功");
}, (sql, tableName) -> {
Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);
boolean needDynamicFlag = Arrays.asList(SysUser.class).stream()
.map(TableInfoHelper::getTableInfo)
.map(TableInfo::getTableName)
.anyMatch(t -> StrUtil.equals(t, tableName));
if(!needDynamicFlag) {return tableName;}
Map<String, Object> requestData = RequestDataHelper.getRequestData();
String tableNum = null;
if (CollUtil.isNotEmpty(requestData)) {
String mapValueContent = requestData.values()
.stream()
.map(Object::toString)
.reduce(StrUtil.EMPTY, (t1, t2) -> {
return StrUtil.join(StrUtil.DOT, t1, t2);
});
int mapHashValue = HashUtil.fnvHash(mapValueContent);
int modulus = mapHashValue % 3;
tableNum = String.valueOf(modulus);
Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
}
String resultTableName = tableName;
if (StrUtil.isNotBlank(tableNum)) {
resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
}
return resultTableName;
}));
//乐观锁，字段添加version注解
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
//防全表更新与删除插件
interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
//租户模式
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
/**
* 租户字段的值 每执行一次SQL一次都会判断一次
* @return
*/
@Override
public Expression getTenantId() {
Object tenant = RequestDataHelper.getRequestData("tenant");
return tenant == null ? null : new StringValue(tenant.toString());
}
/**
* 租户的字段名
* @return
*/
@Override
public String getTenantIdColumn() {
return "tenant";
}
/**
* 某表的SQL是否忽略带上租户字段 每执行一次SQL一次都会判断一次
* @param tableName 表名 这里的表名可能是动态表单的表名注意区别
* @return
*/
@Override
public boolean ignoreTable(String tableName) {
//无租户值则直接忽略拼接租户字段
Object tenant = RequestDataHelper.getRequestData("tenant");
boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);
//根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
boolean tanantEntityFlag = tableInfo.getFieldList().stream()
.anyMatch(tableFieldInfo -> {
return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
});
//需要租户的表
return !tanantEntityFlag || !tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
.map(TableInfoHelper::getTableInfo)
.map(TableInfo::getTableName)
.anyMatch(t -> StrUtil.contains(tableName,t));
}
}));
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
/**
* SQL注入器
*
* @return
*/
@Bean
public ISqlInjector enhanceISqlInjector() {
return new DefaultSqlInjector() {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
methodList.add(new DeleteByIdPhysical());
methodList.add(new DeleteByIdLogic());
return methodList;
}
};
}
}
2.10 枚举@EnumValue
GenderEnum.java
@Getter
@AllArgsConstructor
@ToString
public enum GenderEnum {
/**
* 注意：经测试，这个不会生效，即使数据库中确实是空的，也没办法赋值，是MP的Bug
*/
NO_DELETE(null, "未知"),
MALE(0, "男"),
FEMALE(1, "女");
/**
* 用于标记这是数据库中存储的值
*/
@EnumValue
Integer dbValue;
String desc;
}
IsDelEnum.java
@Getter
@AllArgsConstructor
@ToString
public enum IsDelEnum {
NO_DELETE(0, "未删除"),
DELETED(1, "已删除");
@EnumValue
Integer dbValue;
String desc;
}
SysUser.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
private static final long serialVersionUID = 2095940921263481761L;
/**
* 用户昵称 - 如果没有设置昵称直接使用账户名显示
*/
private String nickName;
/**
* 头像图片地址
*/
private String headUrl;
/**
* 账户名
*/
private String loginName;
/**
* 手机号
*/
private String phoneNumber;
/**
* 邮箱
*/
private String email;
/**
* 性别
*/
private Integer gender;
@TableField("gender")
private GenderEnum genderEnum;
/**
* 个性签名
*/
private String personalMotto;
//@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
//private String myArticleCategory;
/**
* 最近登录时间 - 格式 - yyyyMMddHHmmss
*/
private String lastLoginTime;
/**
* 登录状态：0未登录 1单设备登陆 2多设备登陆
*/
private Integer loginStatus;
/** 账户禁用状态：0账户可使用 1账户不可使用（封号） */
private Integer disabledStatus;
/**
* 密码
*/
private String password;
}
SysUserDao.java
@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}
SysUserServiceTest.java
@SpringBootTest
class SysUserServiceTest {
@Test
public void test11() {
List<SysUser> sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
Console.log(CollectionUtil.size(sysUsers));
Console.log(sysUsers);
}
}
2.11 类型处理器BaseTypeHandler - 必须开启TableName.autoResultMap=true
SysUser.java
/**
* 作用：系统用户信息表(sys_user)
*
* @author LinRuChang
* @version 1.0
* @date 2020/09/24
* @since 1.8
**/
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName(value = "sys_user", autoResultMap = true)
public class SysUser extends BaseDB<SysUser> implements Serializable {
private static final long serialVersionUID = 2095940921263481761L;
/**
* 用户昵称 - 如果没有设置昵称直接使用账户名显示
*/
private String nickName;
/**
* 头像图片地址
*/
private String headUrl;
/**
* 账户名
*/
private String loginName;
/**
* 手机号
*/
private String phoneNumber;
/**
* 邮箱
*/
private String email;
/**
* 性别
*/
private Integer gender;
@TableField("gender")
private GenderEnum genderEnum;
/**
* 个性签名
*/
private String personalMotto;
//@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
//private String myArticleCategory;
/**
* 最近登录时间 - 格式 - yyyyMMddHHmmss
*/
private String lastLoginTime;
/**
* 登录状态：0未登录 1单设备登陆 2多设备登陆
*/
private Integer loginStatus;
/** 账户禁用状态：0账户可使用 1账户不可使用（封号） */
private Integer disabledStatus;
/**
* 密码
*/
private String password;
/**
* 租户
*/
private String tenant;
/**
* 钱包
*
* 每个字段只会在mp中仅注册实例一次
*/
@TableField(typeHandler = WalletListTypeHandler.class)
private List<Wallet> wallets;
/**
* 其他信息
*
* 每个字段只会在mp中仅注册实例一次
*/
@TableField(typeHandler = HutooljsonTypeHandler.class)
private OtherInfo otherInfo;
}
Wallet.java
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Wallet {
/**
* 名称
*/
private String name;
/**
* 各种货币
*/
private List<Currency> currencyList;
}
OtherInfo.java
@Data
public class OtherInfo {
/**
* 性别
*/
private String sex;
/**
* 居住城市
*/
private String city;
}
Currency.java
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Currency {
/**
* 类型: 人民币 RMB , 美元 USD
*/
private String type;
/**
* 金额
*/
private Double amount;
}
HutooljsonTypeHandler.java
@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class HutooljsonTypeHandler extends AbstractJsonTypeHandler<Object> {
private final Class<?> type;
public HutooljsonTypeHandler(Class<?> type) {
if (log.isTraceEnabled()) {
log.trace("HutooljsonTypeHandler(" + type + ")");
}
Assert.notNull(type, "Type argument cannot be null");
this.type = type;
}
@Override
protected Object parse(String json) {
return JSONUtil.toBean(json,type);
}
@Override
protected String toJson(Object obj) {
return JSONUtil.toJsonStr(obj);
}
}
WalletListTypeHandler.java
@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class WalletListTypeHandler extends HutooljsonTypeHandler {
public WalletListTypeHandler(Class<?> type) {
super(type);
}
@Override
protected Object parse(String json) {
return JSONUtil.toBean(json, new TypeReference<List<Wallet>>() {
},true);
}
}
SysUserDao.java
@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}
SysUserServiceTest.java
@SpringBootTest
class SysUserServiceTest {
@Test
public void test17() {
SysUser sysUser = sysUserDao.selectById("6d72c93aa292cf2ca2e789919a5e7bdc");
Console.log(sysUser);
sysUser = sysUserDao.selectById("c08d391e02bc11eb9416b42e99ea3e69");
Console.log(sysUser);
}
}
2.12 连表自定义SQL - 配合wrapper使用
SysUser.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName(value = "sys_user", autoResultMap = true)
public class SysUser extends BaseDB<SysUser> implements Serializable {
private static final long serialVersionUID = 2095940921263481761L;
/**
* 用户昵称 - 如果没有设置昵称直接使用账户名显示
*/
private String nickName;
/**
* 头像图片地址
*/
private String headUrl;
/**
* 账户名
*/
private String loginName;
/**
* 手机号
*/
private String phoneNumber;
/**
* 邮箱
*/
private String email;
/**
* 性别
*/
private Integer gender;
@TableField("gender")
private GenderEnum genderEnum;
/**
* 个性签名
*/
private String personalMotto;
//@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
//private String myArticleCategory;
/**
* 最近登录时间 - 格式 - yyyyMMddHHmmss
*/
private String lastLoginTime;
/**
* 登录状态：0未登录 1单设备登陆 2多设备登陆
*/
private Integer loginStatus;
/** 账户禁用状态：0账户可使用 1账户不可使用（封号） */
private Integer disabledStatus;
/**
* 密码
*/
private String password;
/**
* 租户
*/
private String tenant;
/**
* 钱包
*
* 每个字段只会在mp中仅注册实例一次
*/
@TableField(typeHandler = WalletListTypeHandler.class)
private List<Wallet> wallets;
/**
* 其他信息
*
* 每个字段只会在mp中仅注册实例一次
*/
@TableField(typeHandler = HutooljsonTypeHandler.class)
private OtherInfo otherInfo;
}
FriendlyLink.java
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("friendly_link")
public class FriendlyLink extends BaseDB{
private static final long serialVersionUID = -4714261187453073302L;
/** 所属用户 - 空或空字符则说明是系统链接，有则说明用户链接 */
private String userId;
/** 链接名 */
private String name;
/** 链接地址 */
private String url;
/** 排序 - 值越低越靠前显示 */
private Integer sort;
/** 链接类型：系统1 个人2 => 参考{@link LinkTypeEnums#code}*/
private String type;
/** 该友情链式是否跳转：0不跳转 1跳转 默认为1=> 参考{@link FlagEnums#code} */
private String jumpFlag;
/**其他参数：JSON格式字符串*/
private String params;
/**
* 租户
*/
private String tenant;
}
MyBatisPlusConfig.java
@Configuration
public class MyBatisPlusConfig {
/**
* 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
*
* @return
*/
@Bean
public IKeyGenerator keyGenerator() {
return new IKeyGenerator() {
@Override
public String executeSql(String incrementerName) {
incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
}
@Override
public DbType dbType() {
return DbType.MYSQL;
}
};
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//动态表单
interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
Console.log("动态表单执行成功");
}, (sql, tableName) -> {
Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);
boolean needDynamicFlag = Arrays.asList(SysUser.class).stream()
.map(TableInfoHelper::getTableInfo)
.map(TableInfo::getTableName)
.anyMatch(t -> StrUtil.equals(t, tableName));
if(!needDynamicFlag) {return tableName;}
Map<String, Object> requestData = RequestDataHelper.getRequestData();
String tableNum = null;
if (CollUtil.isNotEmpty(requestData)) {
String mapValueContent = requestData.values()
.stream()
.map(Object::toString)
.reduce(StrUtil.EMPTY, (t1, t2) -> {
return StrUtil.join(StrUtil.DOT, t1, t2);
});
int mapHashValue = HashUtil.fnvHash(mapValueContent);
int modulus = mapHashValue % 3;
tableNum = String.valueOf(modulus);
Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
}
String resultTableName = tableName;
if (StrUtil.isNotBlank(tableNum)) {
resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
}
return resultTableName;
}));
//乐观锁，字段添加version注解
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
//防全表更新与删除插件
interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
//租户模式
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
/**
* 租户字段的值 每执行一次SQL一次都会判断一次
* @return
*/
@Override
public Expression getTenantId() {
Object tenant = RequestDataHelper.getRequestData("tenant");
return tenant == null ? null : new StringValue(tenant.toString());
}
/**
* 租户的字段名
* @return
*/
@Override
public String getTenantIdColumn() {
return "tenant";
}
/**
* 某表的SQL是否忽略带上租户字段 每执行一次SQL一次都会判断一次
* @param tableName 表名 这里的表名可能是动态表单的表名注意区别
* @return
*/
@Override
public boolean ignoreTable(String tableName) {
//无租户值则直接忽略拼接租户字段
Object tenant = RequestDataHelper.getRequestData("tenant");
boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);
//根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
boolean tanantEntityFlag = tableInfo.getFieldList().stream()
.anyMatch(tableFieldInfo -> {
return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
});
//需要租户的表
return !tanantEntityFlag || !tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
.map(TableInfoHelper::getTableInfo)
.map(TableInfo::getTableName)
.anyMatch(t -> StrUtil.contains(tableName,t));
}
}));
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
/**
* SQL注入器
*
* @return
*/
@Bean
public ISqlInjector enhanceISqlInjector() {
return new DefaultSqlInjector() {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
methodList.add(new DeleteByIdPhysical());
methodList.add(new DeleteByIdLogic());
return methodList;
}
};
}
}
SysUserDao.java
@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
IPage<Map> findOneByIdAndFriendLink2Page(IPage<Map> pageInfo, @Param("ew") Wrapper<SysUser> wrapper);
}
SysUserMapper.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="work.linruchang.mybatisplussamplestest.dao.SysUserDao">
<select id="findOneByIdAndFriendLink2Page" resultType="java.util.Map">
select *
from sys_user as sys_user
left join friendly_link as friendly_link on friendly_link.user_id = sys_user.id
<where>
${ew.sqlSegment}
</where>
</select>
</mapper>
SysUserServiceTest.java
@SpringBootTest
class SysUserServiceTest {
@Test
public void test18() {
RequestDataHelper.setRequestData(new Dict().set("tenant", 1));
Page<Map> pageInfo = new Page<>(2, 1);
LambdaQueryWrapper<SysUser> wrapper = Wrappers.<SysUser>query()
.like("sys_user.id", "1536530866313883649")
.eq("friendly_link.name", "Gitee")
.or().lambda()
.like(SysUser::getNickName, "嗯嗯**");
IPage<Map> dataPage = sysUserDao.findOneByIdAndFriendLink2Page(pageInfo, wrapper);
dataPage.getRecords().stream().forEach(Console::log);
}
}
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
1
0- 0
已为社区贡献8条内容
所有评论(0)