mybatis-plus
1. 属性
1.1. 主键自定义生成
1: 自己实现，com.baomidou.mybatisplus.autoconfigure.MybatisPlusPropertiesCustomizer
2: 重写 customize()时，config.setIdentifierGenerator(传自己的id生成方法);
3：自定义id生成器，需要实现com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator
点击查看代码(26位的自增uuid)
package cn.yottacloud.pc.core.mybatis;
import java.util.Random;
/**
* ULID string generator and parser class, using Crockford Base32 encoding. Only
* upper case letters are used for generation. Parsing allows upper and lower
* case letters, and i and l will be treated as 1 and o will be treated as 0.
* <br>
* <br>
* ULID generation examples:<br>
*
* <pre>
* String ulid1 = ULID.random();
* String ulid2 = ULID.random(ThreadLocalRandom.current());
* String ulid3 = ULID.random(SecureRandom.newInstance("SHA1PRNG"));
* byte[] entropy = new byte[] { 0x0, 0x1, 0x2, 0x3, 0x4, 0x5, 0x6, 0x7, 0x8, 0x9 };
* String ulid4 = ULID.generate(System.currentTimeMillis(), entropy);
* </pre>
* ULID parsing examples:<br>
*
* <pre>
* String ulid = "003JZ9J6G80123456789abcdef";
* assert ULID.isValid(ulid);
* long ts = ULID.getTimestamp(ulid);
* assert ts == 123456789000L;
* byte[] entropy = ULID.getEntropy(ulid);
* </pre>
*
* @author azam
* @see <a href="http://www.crockford.com/wrmg/base32.html">Base32 Encoding</a>
* @see <a href="https://github.com/alizain/ulid">ULID</a>
* @since 0.0.1
*/
public class Ulid {
/**
* ULID string length.
*/
public static final int ULID_LENGTH = 26;
/**
* Minimum allowed timestamp value.
*/
public static final long MIN_TIME = 0x0L;
/**
* Maximum allowed timestamp value.
*/
public static final long MAX_TIME = 0x0000ffffffffffffL;
/**
* Base32 characters mapping
*/
private static final char[] C = new char[] { //
0x30, 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, //
0x38, 0x39, 0x41, 0x42, 0x43, 0x44, 0x45, 0x46, //
0x47, 0x48, 0x4a, 0x4b, 0x4d, 0x4e, 0x50, 0x51, //
0x52, 0x53, 0x54, 0x56, 0x57, 0x58, 0x59, 0x5a};
/**
* {@code char} to {@code byte} O(1) mapping with alternative chars mapping
*/
private static final byte[] V = new byte[] { //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0x00, (byte) 0x01, (byte) 0x02, (byte) 0x03, //
(byte) 0x04, (byte) 0x05, (byte) 0x06, (byte) 0x07, //
(byte) 0x08, (byte) 0x09, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0x0a, (byte) 0x0b, (byte) 0x0c, //
(byte) 0x0d, (byte) 0x0e, (byte) 0x0f, (byte) 0x10, //
(byte) 0x11, (byte) 0xff, (byte) 0x12, (byte) 0x13, //
(byte) 0xff, (byte) 0x14, (byte) 0x15, (byte) 0xff, //
(byte) 0x16, (byte) 0x17, (byte) 0x18, (byte) 0x19, //
(byte) 0x1a, (byte) 0xff, (byte) 0x1b, (byte) 0x1c, //
(byte) 0x1d, (byte) 0x1e, (byte) 0x1f, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0x0a, (byte) 0x0b, (byte) 0x0c, //
(byte) 0x0d, (byte) 0x0e, (byte) 0x0f, (byte) 0x10, //
(byte) 0x11, (byte) 0xff, (byte) 0x12, (byte) 0x13, //
(byte) 0xff, (byte) 0x14, (byte) 0x15, (byte) 0xff, //
(byte) 0x16, (byte) 0x17, (byte) 0x18, (byte) 0x19, //
(byte) 0x1a, (byte) 0xff, (byte) 0x1b, (byte) 0x1c, //
(byte) 0x1d, (byte) 0x1e, (byte) 0x1f, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff, //
(byte) 0xff, (byte) 0xff, (byte) 0xff, (byte) 0xff //
};
/**
* Generate random ULID string using {@link java.util.Random} instance.
*
* @return ULID string
*/
public static String random() {
byte[] entropy = new byte[10];
Random random = new Random();
random.nextBytes(entropy);
return generate(System.currentTimeMillis(), entropy);
}
/**
* Generate random ULID string using provided {@link java.util.Random}
* instance.
*
* @param random {@link java.util.Random} instance
* @return ULID string
*/
public static String random(Random random) {
byte[] entropy = new byte[10];
random.nextBytes(entropy);
return generate(System.currentTimeMillis(), entropy);
}
/**
* Generate ULID from Unix epoch timestamp in millisecond and entropy bytes.
* Throws {@link java.lang.IllegalArgumentException} if timestamp is less
* than {@value #MIN_TIME}, is more than {@value #MAX_TIME}, or entropy
* bytes is null or less than 10 bytes.
*
* @param time Unix epoch timestamp in millisecond
* @param entropy Entropy bytes
* @return ULID string
*/
public static String generate(long time, byte[] entropy) {
if (time < MIN_TIME || time > MAX_TIME || entropy == null || entropy.length < 10) {
throw new IllegalArgumentException(
"Time is too long, or entropy is less than 10 bytes or null");
}
char[] chars = new char[26];
// time
chars[0] = C[((byte) (time >>> 45)) & 0x1f];
chars[1] = C[((byte) (time >>> 40)) & 0x1f];
chars[2] = C[((byte) (time >>> 35)) & 0x1f];
chars[3] = C[((byte) (time >>> 30)) & 0x1f];
chars[4] = C[((byte) (time >>> 25)) & 0x1f];
chars[5] = C[((byte) (time >>> 20)) & 0x1f];
chars[6] = C[((byte) (time >>> 15)) & 0x1f];
chars[7] = C[((byte) (time >>> 10)) & 0x1f];
chars[8] = C[((byte) (time >>> 5)) & 0x1f];
chars[9] = C[((byte) (time)) & 0x1f];
// entropy
chars[10] = C[(byte) ((entropy[0] & 0xff) >>> 3)];
chars[11] = C[(byte) (((entropy[0] << 2) | ((entropy[1] & 0xff) >>> 6)) & 0x1f)];
chars[12] = C[(byte) (((entropy[1] & 0xff) >>> 1) & 0x1f)];
chars[13] = C[(byte) (((entropy[1] << 4) | ((entropy[2] & 0xff) >>> 4)) & 0x1f)];
chars[14] = C[(byte) (((entropy[2] << 5) | ((entropy[3] & 0xff) >>> 7)) & 0x1f)];
chars[15] = C[(byte) (((entropy[3] & 0xff) >>> 2) & 0x1f)];
chars[16] = C[(byte) (((entropy[3] << 3) | ((entropy[4] & 0xff) >>> 5)) & 0x1f)];
chars[17] = C[(byte) (entropy[4] & 0x1f)];
chars[18] = C[(byte) ((entropy[5] & 0xff) >>> 3)];
chars[19] = C[(byte) (((entropy[5] << 2) | ((entropy[6] & 0xff) >>> 6)) & 0x1f)];
chars[20] = C[(byte) (((entropy[6] & 0xff) >>> 1) & 0x1f)];
chars[21] = C[(byte) (((entropy[6] << 4) | ((entropy[7] & 0xff) >>> 4)) & 0x1f)];
chars[22] = C[(byte) (((entropy[7] << 5) | ((entropy[8] & 0xff) >>> 7)) & 0x1f)];
chars[23] = C[(byte) (((entropy[8] & 0xff) >>> 2) & 0x1f)];
chars[24] = C[(byte) (((entropy[8] << 3) | ((entropy[9] & 0xff) >>> 5)) & 0x1f)];
chars[25] = C[(byte) (entropy[9] & 0x1f)];
return new String(chars);
}
/**
* Checks ULID string validity.
*
* @param ulid ULID string
* @return true if ULID string is valid
*/
public static boolean isValid(CharSequence ulid) {
if (ulid == null || ulid.length() != ULID_LENGTH) {
return false;
}
for (int i = 0; i < ULID_LENGTH; i++) {
// We only care for chars between 0x00 and 0xff.
char c = ulid.charAt(i);
if (c < 0 || c > V.length || V[c] == (byte) 0xff) {
return false;
}
}
return true;
}
/**
* Extract and return the timestamp part from ULID. Expects a valid ULID
* string. Call {@link io.azam.ulidj.ULID#isValid(CharSequence)} and check
* validity before calling this method if you do not trust the origin of the
* ULID string.
*
* @param ulid ULID string
* @return Unix epoch timestamp in millisecond
*/
public static long getTimestamp(CharSequence ulid) {
return (long) V[ulid.charAt(0)] << 45 //
| (long) V[ulid.charAt(1)] << 40 //
| (long) V[ulid.charAt(2)] << 35 //
| (long) V[ulid.charAt(3)] << 30 //
| (long) V[ulid.charAt(4)] << 25 //
| (long) V[ulid.charAt(5)] << 20 //
| (long) V[ulid.charAt(6)] << 15 //
| (long) V[ulid.charAt(7)] << 10 //
| (long) V[ulid.charAt(8)] << 5 //
| (long) V[ulid.charAt(9)];
}
/**
* Extract and return the entropy part from ULID. Expects a valid ULID
* string. Call {@link io.azam.ulidj.ULID#isValid(CharSequence)} and check
* validity before calling this method if you do not trust the origin of the
* ULID string.
*
* @param ulid ULID string
* @return Entropy bytes
*/
public static byte[] getEntropy(CharSequence ulid) {
byte[] bytes = new byte[10];
bytes[0] = (byte) ((V[ulid.charAt(10)] << 3) //
| (V[ulid.charAt(11)] & 0xff) >>> 2);
bytes[1] = (byte) ((V[ulid.charAt(11)] << 6) //
| V[ulid.charAt(12)] << 1 //
| (V[ulid.charAt(13)] & 0xff) >>> 4);
bytes[2] = (byte) ((V[ulid.charAt(13)] << 4) //
| (V[ulid.charAt(14)] & 0xff) >>> 1);
bytes[3] = (byte) ((V[ulid.charAt(14)] << 7) //
| V[ulid.charAt(15)] << 2 //
| (V[ulid.charAt(16)] & 0xff) >>> 3);
bytes[4] = (byte) ((V[ulid.charAt(16)] << 5) //
| V[ulid.charAt(17)]);
bytes[5] = (byte) ((V[ulid.charAt(18)] << 3) //
| (V[ulid.charAt(19)] & 0xff) >>> 2);
bytes[6] = (byte) ((V[ulid.charAt(19)] << 6) //
| V[ulid.charAt(20)] << 1 //
| (V[ulid.charAt(21)] & 0xff) >>> 4);
bytes[7] = (byte) ((V[ulid.charAt(21)] << 4) //
| (V[ulid.charAt(22)] & 0xff) >>> 1);
bytes[8] = (byte) ((V[ulid.charAt(22)] << 7) //
| V[ulid.charAt(23)] << 2 //
| (V[ulid.charAt(24)] & 0xff) >>> 3);
bytes[9] = (byte) ((V[ulid.charAt(24)] << 5) //
| V[ulid.charAt(25)]);
return bytes;
}
}
点击查看代码（id生成器）
package cn.yottacloud.pc.core.mybatis;
import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import java.util.concurrent.ThreadLocalRandom;
/**
* 基于ULID的全局id生成器
*/
public class UlidIdentifierGenerator implements IdentifierGenerator {
@Override
public Number nextId(Object entity) {
return -1;
}
@Override
public String nextUUID(Object entity) {
byte[] entropy = new byte[10];
ThreadLocalRandom.current().nextBytes(entropy);
# java.util.Random.Ulid
return Ulid.generate(System.currentTimeMillis(), entropy);
}
}
点击查看代码(自定义id方法)
package cn.yottacloud.pc.core.mybatis;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusPropertiesCustomizer;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.MySqlDialect;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;
/**
* mybatis-plus配置
*/
@Configuration
@MapperScan("cn.yottacloud.pc.**.mapper")
public class MybatisPlusConfiguration implements MybatisPlusPropertiesCustomizer {
/**
* 拦截器配置
*/
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//分页插件
PaginationInnerInterceptor paginationInnerInterceptor =
new PaginationInnerInterceptor(DbType.MYSQL);
paginationInnerInterceptor.setMaxLimit(100L);
paginationInnerInterceptor.setDialect(new MySqlDialect());
interceptor.addInnerInterceptor(paginationInnerInterceptor);
//乐观锁
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
/**
* 全局配置
*/
@Override
public void customize(MybatisPlusProperties properties) {
GlobalConfig config = properties.getGlobalConfig();
//设置逻辑删除字段和id类型
config.setDbConfig(new GlobalConfig.DbConfig()
.setIdType(IdType.ASSIGN_UUID)
);
//设置id生成器
config.setIdentifierGenerator(new UlidIdentifierGenerator());
//设置对象填充器
config.setMetaObjectHandler(new DefaultMetaObjectHandler());
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.addInterceptor(mybatisPlusInterceptor());
configuration.setDefaultEnumTypeHandler(MybatisEnumTypeHandler.class);
properties.setConfiguration(configuration);
}
}
1.2. json字段自动映射List属性
java属性为对象也行！
-
1：实体类的@TableName要加属性：,autoResultMap=true
@TableName(value = "lg_kol_case_fee",autoResultMap=true) -
2：属性上加：@TableField(typeHandler = JacksonTypeHandler.class)
@TableField(typeHandler = JacksonTypeHandler.class)
private List\<String\> feeDept; -
3：mysql字段为json，则java的泛型必须为Object，必须是JacksonTypeHandler.class。字段为指定类型，需要自己实现对应泛型转换类！！
点击查看代码
package com.lg.crm.framework.mybatis.core.type;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import com.fasterxml.jackson.core.type.TypeReference;
import com.lg.crm.framework.common.util.json.JsonUtils;
import java.util.Set;
/**
* 参考 {@link com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler} 实现
* 在我们将字符串反序列化为 Set 并且泛型为 Long 时，如果每个元素的数值太小，会被处理成 Integer 类型，导致可能存在隐性的 BUG。
*
* 例如说哦，SysUserDO 的 postIds 属性
*
* @author 芋道源码
*/
public class JsonStringSetTypeHandler extends AbstractJsonTypeHandler<Object> {
private static final TypeReference<Set<String>> TYPE_REFERENCE = new TypeReference<Set<String>>(){};
@Override
protected Object parse(String json) {
return JsonUtils.parseObject(json, TYPE_REFERENCE);
}
@Override
protected String toJson(Object obj) {
return JsonUtils.toJsonString(obj);
}
}
- 4: xml的用法
<result property="feeDept" column="fee_dept" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
如果注解@TableName和@TableField加了，xml可以不加，已亲测
2. 单表api
2.1. 查
- Service最简洁写法：
前置前提：
![image]()
点击查看代码
List<ScrmSupplierUsersDO> list = scrmSupplierUsersService.lambdaQuery()
.eq(ScrmSupplierUsersDO::getSupId, supId).list();
scrmSupplierUsersService.removeBatchByIds(list);
2.2. 改
- Service最简洁写法：
点击查看代码
scrmSupplierUsersService.lambdaUpdate().eq(ScrmSupplierUsersDO::getSupId, supId)
.set(ScrmSupplierUsersDO::getDeleteFlag, 1).update();
3. 手动生成雪花id
IdWorker.getIdStr()
4. update的字段为空
问题：表里有条数据，name字段原来有值，更新请求时，用户要把name改为null，直接调的方法aveOrUpdate(T e),没用UpdateWrapper去set。
原因：
plus的service方法，默认不会对属性为空的进行sql拼接！
解决办法：
1：用UpdateWrapper去set。
2：对指定字段修改策略，颗粒度（全局、某个属性【新增，编辑】，详情可看官方文档-->问答，"插入或更新的字段有 空字符串 或者 null"）
// updateStrategy = FieldStrategy.IGNORED：该字段在更新时，为null或""也要参与更新
@TableField(value = "channel_id", updateStrategy = FieldStrategy.IGNORED)
private String channelId;
5. 多表分页
5.1. controller
IPage
return ViewResult.success(yqfkDangerReportHealthyService.getPageControlRecord(page, params));
5.2. service
@Override
public IPage
return yqfkDangerReportHealthyMapper.getPageControlRecord(page, params);
}
5.3. mapper
IPage
5.4. xml
点击查看代码
<select id="getPageControlRecord" resultMap="controlRecordPageResult">
SELECT
DISTINCT( person_id ),
controlResult,
dangerType,
name,
sex,
birthday,
cardNo,
mobile,
address
from
(SELECT
DISTINCT(a.person_id),
c.control_result controlResult,
( SELECT dict_label FROM sys_dict_data WHERE dict_type = 'yqfk_risk_type' AND dict_value = c.danger_type LIMIT 1 ) dangerType,
c.name name,
c.sex sex,
c.birthday birthday,
c.card_no cardNo,
c.mobile mobile,
c.address_str address,
b.check_start_time
FROM
yqfk_check_flow_process a
LEFT JOIN yqfk_danger_person_check_task b ON a.person_id = b.person_id and a.task_id=b.id
LEFT JOIN yqfk_danger_person c ON a.person_id = c.id
WHERE
a.object_type = 0
AND a.accept_object_id = #{params.userId}
AND a.del_flag = 0
AND b.del_flag = 0
AND c.del_flag =0
and c.control_result = #{params.controlStatus}
<if test="params.keyword != null and params.keyword != ''">
and
(
c.name like CONCAT('%',#{params.keyword},'%')
or
c.card_no = #{params.keyword}
or
c.mobile=#{params.keyword}
)
</if>
ORDER BY b.check_start_time desc
) a
</select>
6. 日志输出
6.1. 第1种
不打印sql查询的结果集，可控制到打印哪些包下的sql, 可打印java方法和sql。
debug级别，才会打印sql
点击查看代码
# 日志文件配置
logging:
file:
name: ${user.home}/logs/${spring.application.name}.log # 日志文件名，全路径
level:
# 配置自己写的 MyBatis Mapper 打印日志
com.lg.scrm.module.infra.dal.mysql: info
com.lg.scrm.module.infra.dal.mysql.job.JobLogMapper: info # 配置 JobLogMapper 的日志级别为 info
com.lg.scrm.module.system.dal.mysql: info
com.lg.scrm.module.lg.dal.mysql: info
6.2. 第2种
纯sql打印，
会输出具体每一行数据，select * ，查出50条，就会打印这50条
点击查看代码
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
7. 自动拼接sql
7.1. 租户id核心代码
7.1.1. 配置Bean
@AutoConfiguration
Configuration类
@Bean
public TenantLineInnerInterceptor tenantLineInnerInterceptor(TenantProperties properties,
MybatisPlusInterceptor interceptor) {
TenantLineInnerInterceptor inner = new TenantLineInnerInterceptor(new TenantDatabaseInterceptor(properties));
// 添加到 interceptor 中
// 需要加在首个，主要是为了在分页插件前面。这个是 MyBatis Plus 的规定
MyBatisUtils.addInterceptor(interceptor, inner, 0);
return inner;
}
7.1.2. TenantLineHandler
package cn.iocoder.yudao.framework.tenant.core.db;
import cn.hutool.core.collection.CollUtil;
import cn.iocoder.yudao.framework.tenant.config.TenantProperties;
import cn.iocoder.yudao.framework.tenant.core.context.TenantContextHolder;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import java.util.HashSet;
import java.util.Set;
/**
* 基于 MyBatis Plus 多租户的功能，实现 DB 层面的多租户的功能
*
* @author 芋道源码
*/
public class TenantDatabaseInterceptor implements TenantLineHandler {
private final Set<String> ignoreTables = new HashSet<>();
public TenantDatabaseInterceptor(TenantProperties properties) {
// 不同 DB 下，大小写的习惯不同，所以需要都添加进去
properties.getIgnoreTables().forEach(table -> {
ignoreTables.add(table.toLowerCase());
ignoreTables.add(table.toUpperCase());
});
// 在 OracleKeyGenerator 中，生成主键时，会查询这个表，查询这个表后，会自动拼接 TENANT_ID 导致报错
ignoreTables.add("DUAL");
}
@Override
public Expression getTenantId() {
return new LongValue(TenantContextHolder.getRequiredTenantId());
}
@Override
public boolean ignoreTable(String tableName) {
return TenantContextHolder.isIgnore() // 情况一，全局忽略多租户
|| CollUtil.contains(ignoreTables, SqlParserUtils.removeWrapperSymbol(tableName)); // 情况二，忽略多租户的表
}
}
7.1.3. TenantLineInnerInterceptor拦截器
/*
* Copyright (c) 2011-2024, baomidou (jobob@qq.com).
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.baomidou.mybatisplus.extension.plugins.inner;
import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.toolkit.PropertyMapper;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.RowConstructor;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ParenthesedExpressionList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
/**
* @author hubin
* @since 3.4.0
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class TenantLineInnerInterceptor extends BaseMultiTableInnerInterceptor implements InnerInterceptor {
private TenantLineHandler tenantLineHandler;
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
return;
}
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(parserSingle(mpBs.sql(), null));
}
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
return;
}
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(parserMulti(mpBs.sql(), null));
}
}
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
final String whereSegment = (String) obj;
processSelectBody(select, whereSegment);
List<WithItem> withItemsList = select.getWithItemsList();
if (!CollectionUtils.isEmpty(withItemsList)) {
withItemsList.forEach(withItem -> processSelectBody(withItem, whereSegment));
}
}
@Override
protected void processInsert(Insert insert, int index, String sql, Object obj) {
if (tenantLineHandler.ignoreTable(insert.getTable().getName())) {
// 过滤退出执行
return;
}
List<Column> columns = insert.getColumns();
if (CollectionUtils.isEmpty(columns)) {
// 针对不给列名的insert 不处理
return;
}
String tenantIdColumn = tenantLineHandler.getTenantIdColumn();
if (tenantLineHandler.ignoreInsert(columns, tenantIdColumn)) {
// 针对已给出租户列的insert 不处理
return;
}
columns.add(new Column(tenantIdColumn));
Expression tenantId = tenantLineHandler.getTenantId();
// fixed gitee pulls/141 duplicate update
List<UpdateSet> duplicateUpdateColumns = insert.getDuplicateUpdateSets();
if (CollectionUtils.isNotEmpty(duplicateUpdateColumns)) {
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new StringValue(tenantIdColumn));
equalsTo.setRightExpression(tenantId);
duplicateUpdateColumns.add(new UpdateSet(new Column(tenantIdColumn), tenantId));
}
Select select = insert.getSelect();
if (select instanceof PlainSelect) { //fix github issue 4998 修复升级到4.5版本的问题
this.processInsertSelect(select, (String) obj);
} else if (insert.getValues() != null) {
// fixed github pull/295
Values values = insert.getValues();
ExpressionList<Expression> expressions = (ExpressionList<Expression>) values.getExpressions();
if (expressions instanceof ParenthesedExpressionList) {
expressions.addExpression(tenantId);
} else {
if (CollectionUtils.isNotEmpty(expressions)) {//fix github issue 4998 jsqlparse 4.5 批量insert ItemsList不是MultiExpressionList 了，需要特殊处理
int len = expressions.size();
for (int i = 0; i < len; i++) {
Expression expression = expressions.get(i);
if (expression instanceof Parenthesis) {
ExpressionList rowConstructor = new RowConstructor<>()
.withExpressions(new ExpressionList<>(((Parenthesis) expression).getExpression(), tenantId));
expressions.set(i, rowConstructor);
} else if (expression instanceof ParenthesedExpressionList) {
((ParenthesedExpressionList) expression).addExpression(tenantId);
} else {
expressions.add(tenantId);
}
}
} else {
expressions.add(tenantId);
}
}
} else {
throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId");
}
}
/**
* update 语句处理
*/
@Override
protected void processUpdate(Update update, int index, String sql, Object obj) {
final Table table = update.getTable();
if (tenantLineHandler.ignoreTable(table.getName())) {
// 过滤退出执行
return;
}
List<UpdateSet> sets = update.getUpdateSets();
if (!CollectionUtils.isEmpty(sets)) {
sets.forEach(us -> us.getValues().forEach(ex -> {
if (ex instanceof Select) {
processSelectBody(((Select) ex), (String) obj);
}
}));
}
update.setWhere(this.andExpression(table, update.getWhere(), (String) obj));
}
/**
* delete 语句处理
*/
@Override
protected void processDelete(Delete delete, int index, String sql, Object obj) {
if (tenantLineHandler.ignoreTable(delete.getTable().getName())) {
// 过滤退出执行
return;
}
delete.setWhere(this.andExpression(delete.getTable(), delete.getWhere(), (String) obj));
}
/**
* 处理 insert into select
* <p>
* 进入这里表示需要 insert 的表启用了多租户,则 select 的表都启动了
*
* @param selectBody SelectBody
*/
protected void processInsertSelect(Select selectBody, final String whereSegment) {
if(selectBody instanceof PlainSelect){
PlainSelect plainSelect = (PlainSelect) selectBody;
FromItem fromItem = plainSelect.getFromItem();
if (fromItem instanceof Table) {
// fixed gitee pulls/141 duplicate update
processPlainSelect(plainSelect, whereSegment);
appendSelectItem(plainSelect.getSelectItems());
} else if (fromItem instanceof Select) {
Select subSelect = (Select) fromItem;
appendSelectItem(plainSelect.getSelectItems());
processInsertSelect(subSelect, whereSegment);
}
} else if(selectBody instanceof ParenthesedSelect){
ParenthesedSelect parenthesedSelect = (ParenthesedSelect) selectBody;
processInsertSelect(parenthesedSelect.getSelect(), whereSegment);
}
}
/**
* 追加 SelectItem
*
* @param selectItems SelectItem
*/
protected void appendSelectItem(List<SelectItem<?>> selectItems) {
if (CollectionUtils.isEmpty(selectItems)) {
return;
}
if (selectItems.size() == 1) {
SelectItem item = selectItems.get(0);
Expression expression = item.getExpression();
if (expression instanceof AllColumns) {
return;
}
}
selectItems.add(new SelectItem<>(new Column(tenantLineHandler.getTenantIdColumn())));
}
/**
* 租户字段别名设置
* <p>tenantId 或 tableAlias.tenantId</p>
*
* @param table 表对象
* @return 字段
*/
protected Column getAliasColumn(Table table) {
StringBuilder column = new StringBuilder();
// todo 该起别名就要起别名,禁止修改此处逻辑
if (table.getAlias() != null) {
column.append(table.getAlias().getName()).append(StringPool.DOT);
}
column.append(tenantLineHandler.getTenantIdColumn());
return new Column(column.toString());
}
@Override
public void setProperties(Properties properties) {
PropertyMapper.newInstance(properties).whenNotBlank("tenantLineHandler",
ClassUtils::newInstance, this::setTenantLineHandler);
}
/**
* 构建租户条件表达式
*
* @param table 表对象
* @param where 当前where条件
* @param whereSegment 所属Mapper对象全路径（在原租户拦截器功能中，这个参数并不需要参与相关判断）
* @return 租户条件表达式
* @see BaseMultiTableInnerInterceptor#buildTableExpression(Table, Expression, String)
*/
@Override
public Expression buildTableExpression(final Table table, final Expression where, final String whereSegment) {
if (tenantLineHandler.ignoreTable(table.getName())) {
return null;
}
return new EqualsTo(getAliasColumn(table), tenantLineHandler.getTenantId());
}
}
7.2. 自定义一个类似租户的字段（供应商id）
- 也是全局拼接，无侵入的
7.2.1. 第一步- 实现TenantLineHandler
确定追加字段名，getTenantIdColumn()
TenantProperties 可以模仿后改成自己的
package cn.iocoder.yudao.framework.tenant.core.db;
import cn.hutool.core.collection.CollUtil;
import cn.iocoder.yudao.framework.tenant.config.TenantProperties;
import cn.iocoder.yudao.framework.tenant.core.context.TenantContextHolder;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import java.util.HashSet;
import java.util.Set;
/**
* 供应商id拦截器
* @author jf
*/
public class IdDatabaseInterceptor implements TenantLineHandler {
private final Set<String> ignoreTables = new HashSet<>();
public IdDatabaseInterceptor(TenantProperties properties) {
// 不同 DB 下，大小写的习惯不同，所以需要都添加进去
properties.getIgnoreTables().forEach(table -> {
ignoreTables.add(table.toLowerCase());
ignoreTables.add(table.toUpperCase());
});
// 在 OracleKeyGenerator 中，生成主键时，会查询这个表，查询这个表后，会自动拼接 TENANT_ID 导致报错
ignoreTables.add("DUAL");
}
/**
* 供应商id列名
* @return
*/
@Override
public String getTenantIdColumn() {
return "tenant_id";
}
@Override
public Expression getTenantId() {
return new LongValue(TenantContextHolder.getRequiredTenantId());
}
@Override
public boolean ignoreTable(String tableName) {
return TenantContextHolder.isIgnore() // 情况一，全局忽略多租户
|| CollUtil.contains(ignoreTables, SqlParserUtils.removeWrapperSymbol(tableName)); // 情况二，忽略多租户的表
}
}
7.2.2. 第2步- 注册该拦截器
@Bean
public TenantLineInnerInterceptor tenantLineInnerInterceptorId(TenantProperties properties,
MybatisPlusInterceptor interceptor) {
TenantLineInnerInterceptor inner2 = new TenantLineInnerInterceptor(new IdDatabaseInterceptor(properties));
// 添加到 interceptor 中
// 需要加在首个，主要是为了在分页插件前面。这个是 MyBatis Plus 的规定
MyBatisUtils.addInterceptor(interceptor, inner2, 0);
return inner2;
}
浙公网安备 33010602011771号