mybatis-plus实现多租户Saas

开心姐妹群 · 发布于 2023-6-21 16:35:16

mybatis-plus实现多租户Saas

实现方式

给对应需要停止数据隔离的表加上租户ID (tenant_id字段)
原理

使用mybatis-plus操作的表会自动过滤tenant_id字段的值
包括新增,修改,删除,查询,关联查询等
步骤

1.给对应表加上tenant_id字段

2.配置mybatis-plus

项目构造

初始数据

DROP TABLE IF EXISTS user;
CREATE TABLE user
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (id)
);
DROP TABLE IF EXISTS user_addr;
CREATE TABLE USER_ADDR
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
user_id BIGINT(20) NOT NULL COMMENT 'user.id',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '地址名称',
PRIMARY KEY (id)
);
DELETE FROM user;
INSERT INTO user (id, tenant_id, name) VALUES
(1, 1, 'Jone'),(2, 1, 'Jack'),(3, 1, 'Tom'),
(4, 0, 'Sandy'),(5, 0, 'Billie');
INSERT INTO user_addr (id, USER_ID, name) VALUES
(1, 1, 'addr1'),(2,1,'addr2');

复制代码

Maven依赖

<?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">
<parent>
<artifactId>mybatis-plus-samples</artifactId>
<groupId>com.baomidou</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>mybatis-plus-sample-tenant</artifactId>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<filtering>false</filtering>
<includes>
<include>**/mapper/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*</include>
</includes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>

复制代码

yml配置

# DataSource Config
spring:
datasource:
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/rest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&allowMultiQueries=true
# Logger Config
logging:
level:
com.baomidou.mybatisplus.samples: debug
# MyBatis-Plus 配置
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 日志打印
system:
saas:
ignoreTables: # 需要忽略的表,表中无租户ID字段
- user_addr

复制代码

代码

MybatisPlusConfig.java

package com.baomidou.mybatisplus.samples.tenant.config;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@Configuration
@MapperScan("com.baomidou.mybatisplus.samples.tenant.mapper")
public class MybatisPlusConfig {
@Autowired
private SaaSConfig saaSConfig;
/**
* 新多租户插件配置,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 防止缓存万一呈现问题
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加租户
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
// todo 获取当前用户租户ID
Long tenantId = 1L;
/**
* 设置租户ID的值
* @return
*/
@Override
public Expression getTenantId() {
return new LongValue(tenantId);
}
// 这是 default 方法,默认返回 tenant_id 表示租户ID字段名称,租户字段为tenant_id时,不用重写此方法
@Override
public String getTenantIdColumn() {
return "tenant_id";
}
// 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件
@Override
public boolean ignoreTable(String tableName) {
// 超级管理员端的表可以停止忽略(如租户管理表等)
List<String> ignoreTables = saaSConfig.getIgnoreTables();
long count = ignoreTables.stream().filter(e -> e.equalsIgnoreCase(tableName)).count();
return count > 0;
//return !"user".equalsIgnoreCase(tableName);
}
}));
// 假设用了分页插件注意先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
// 用了分页插件必需设置 MybatisConfiguration#useDeprecatedExecutor = false
// interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
// @Bean
// public ConfigurationCustomizer configurationCustomizer() {
// return configuration -> configuration.setUseDeprecatedExecutor(false);
// }
}

复制代码

SaaSConfig.java

package com.baomidou.mybatisplus.samples.tenant.config;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
/**
* 多租户的配置属性
*
* @company
*
*/
@Data
@Component
@ConfigurationProperties(prefix = "system.saas")
public class SaaSConfig {
/**
* 多租户字段名
*/
private String tenantId = "tenant_id";
/**
* 忽略多租户的表名
* <pre>
* 数据库中物理表表名
* </pre>
*/
private List<String> ignoreTables = new ArrayList<>();
}

复制代码

User.java

package com.baomidou.mybatisplus.samples.tenant.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* <p>
* 用户实体对应表 user
* </p>
*
*/
@Data
@Accessors(chain = true)
public class User {
@TableId(type = IdType.AUTO)
private Long id;
/**
* 租户 ID
*/
private Long tenantId;
private String name;
@TableField(exist = false)
private String addrName;
}

复制代码

UserMapper.java

package com.baomidou.mybatisplus.samples.tenant.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.samples.tenant.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* <p>
* MP 支持不需要 UserMapper.xml 这个模块演示内置 CRUD 咱们就不要 XML 部分了
* </p>
*
*/
public interface UserMapper extends BaseMapper<User> {
/**
* 自定义SQL：默认也会增加多租户条件
* 参考打印的SQL
* @return
*/
Integer myCount();
List<User> getUserAndAddr(@Param("username") String username);
List<User> getAddrAndUser(@Param("name") String name);
}

复制代码

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.baomidou.mybatisplus.samples.tenant.mapper.UserMapper">
<select id="myCount" resultType="java.lang.Integer">
select count(1) from user
</select>
<select id="getUserAndAddr" resultType="com.baomidou.mybatisplus.samples.tenant.entity.User">
select u.id, u.name, a.name as addr_name
from user u
left join user_addr a on a.user_id=u.id
<where>
<if test="username!=null">
u.name like concat(concat('%',#{username}),'%')
</if>
</where>
</select>
<select id="getAddrAndUser" resultType="com.baomidou.mybatisplus.samples.tenant.entity.User">
select a.name as addr_name, u.id, u.name
from user_addr a
left join user u on u.id=a.user_id
<where>
<if test="name!=null">
a.name like concat(concat('%',#{name}),'%')
</if>
</where>
</select>
</mapper>

复制代码

TenantTest.java

package com.baomidou.mybatisplus.samples.tenant;
import com.baomidou.mybatisplus.samples.tenant.entity.User;
import com.baomidou.mybatisplus.samples.tenant.mapper.UserMapper;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.util.List;
/**
* <p>
* 多租户 Tenant 演示
* </p>
*
* @author hubin
* @since 2018-08-11
*/
@SpringBootTest
public class TenantTest {
@Resource
private UserMapper mapper;
@Test
public void aInsert() {
User user = new User();
user.setName("逐个33");
Assertions.assertTrue(mapper.insert(user) > 0);
user = mapper.selectById(user.getId());
Assertions.assertTrue(1 == user.getTenantId());
}
@Test
public void bDelete() {
Assertions.assertTrue(mapper.deleteById(3L) > 0);
}
@Test
public void cUpdate() {
Assertions.assertTrue(mapper.updateById(new User().setId(1L).setName("mp")) > 0);
}
@Test
public void dSelect() {
List<User> userList = mapper.selectList(null);
userList.forEach(u -> Assertions.assertTrue(1 == u.getTenantId()));
}
/**
* 自定义SQL：默认也会增加多租户条件
* 参考打印的SQL
*/
@Test
public void manualSqlTenantFilterTest() {
System.out.println(mapper.myCount());
}
@Test
public void testTenantFilter(){
mapper.getAddrAndUser(null).forEach(System.out::println);
mapper.getAddrAndUser("add").forEach(System.out::println);
mapper.getUserAndAddr(null).forEach(System.out::println);
mapper.getUserAndAddr("J").forEach(System.out::println);
}
}

复制代码

参考:https://baomidou.com/pages/aef2f2/#tenantlineinnerinterceptor