基于mybatis-plus实现多租户级别的数据隔离

一个平台系统通常需要提供给很多的用户进行使用，为了保证数据的安全性和完整性，用户之间是不能直接访问数据的，也就是说用户只能访问属于自己的数据。典型的场景就是电商平台，平台上有很多的商户，每个商户只能访问自己的商品，订单，账单等数据。

为每个商户建立一个数据库，往往会存在巨大的成本压力和管理复杂度，此时可以采用通过数据库表中增加特定的标识字段来进行租户数据隔离。mybatis-plus提供的租户数据隔离方案，可以使得开发过程中，不用过多的关注租户隔离的处理，主要由mybatis-plus提供处理。

说起来总是很抽象，这里以一个简单的示例来说明实现过程，项目采用mysql + mybatis-plus + druid。

1. 创建工程，添加相关依赖

<properties>         <maven.compiler.source>8</maven.compiler.source>         <maven.compiler.target>8</maven.compiler.target>         <mybatisplus.version>3.4.2</mybatisplus.version>         <druid.version>1.2.6</druid.version>     </properties>      <dependencies>          <dependency>             <groupId>org.springframework.boot</groupId>             <artifactId>spring-boot-starter</artifactId>             <version>2.3.12.RELEASE</version>         </dependency>          <dependency>             <groupId>org.springframework.boot</groupId>             <artifactId>spring-boot-starter-web</artifactId>             <version>2.3.12.RELEASE</version>         </dependency>          <dependency>             <groupId>com.baomidou</groupId>             <artifactId>mybatis-plus-boot-starter</artifactId>             <version>${mybatisplus.version}</version>         </dependency>          <dependency>             <groupId>com.baomidou</groupId>             <artifactId>mybatis-plus</artifactId>             <version>${mybatisplus.version}</version>         </dependency>          <dependency>             <groupId>mysql</groupId>             <artifactId>mysql-connector-java</artifactId>             <version>8.0.25</version>         </dependency>          <!-- 阿里数据库连接池 -->         <dependency>             <groupId>com.alibaba</groupId>             <artifactId>druid-spring-boot-starter</artifactId>             <version>${druid.version}</version>         </dependency>      </dependencies>

2. 添加配置类

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 org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;   @Configuration @MapperScan("com.platform.multitenant.mapper") public class MybatisPlusConfig {      /**      * 新多租户插件配置,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存万一出现问题      */     @Bean     public MybatisPlusInterceptor mybatisPlusInterceptor() {         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();         interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {              /**              * 这里返回的租户id作为sql执行时自动添加的租户id              * 这里的租户id可以从线程本地变量中获取（当租户id提前设置到了线程本地变量中），              * 也可以从请求环境中获取（当租户id在请求环境中存在时）              * 为了简化演示，这里设置了一个固定值              * @return              */             @Override             public Expression getTenantId() {                 return new LongValue(1);             }              // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件             @Override             public boolean ignoreTable(String tableName) {                 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); //    } }

3. mapper.xml

<mapper namespace="com.platform.multitenant.mapper.UserMapper">      <select id="myCount" resultType="java.lang.Integer">         select count(1) from user     </select>      <select id="getUserAndAddr" resultType="com.platform.multitenant.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.platform.multitenant.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>

可以看到，xml文件中并没有出现租户字段信息。

4. 参数配置（application.yml）

spring:   application:     # 应用名称     name: multitenant-app   datasource:     type: com.alibaba.druid.pool.DruidDataSource     driverClassName: com.mysql.cj.jdbc.Driver     url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8     username: username     password: password   logging:   level:     com.platform.multitenant: debug

5. 执行测试

@RequestMapping("/test")     void testUser()     {         User user = new User();         user.setName("test1");          // add for test         userMapper.insert(user);          // select for test         user = userMapper.selectById(user.getId());          // delete for test         userMapper.deleteById(3L);          // update for test         User newUser = new User();         newUser.setId(1L);         user.setName("mp");         userMapper.updateById(user);          // count for test         System.out.println(userMapper.myCount());          userMapper.getAddrAndUser(null).forEach(System.out::println);         userMapper.getAddrAndUser("add").forEach(System.out::println);         userMapper.getUserAndAddr(null).forEach(System.out::println);         userMapper.getUserAndAddr("test1").forEach(System.out::println);     }

6. 调试输出

2022-04-07 15:56:54.651 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.insert           : ==>  Preparing: INSERT INTO user (id, name, tenant_id) VALUES (?, ?, 1) 2022-04-07 15:56:54.652 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.insert           : ==> Parameters: 1511976295397867521(Long), test1(String) 2022-04-07 15:56:54.661 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.insert           : <==    Updates: 1 2022-04-07 15:56:54.663 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.selectById       : ==>  Preparing: SELECT id, tenant_id, name FROM user WHERE id = ? AND tenant_id = 1 2022-04-07 15:56:54.663 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.selectById       : ==> Parameters: 1511976295397867521(Long) 2022-04-07 15:56:54.664 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.selectById       : <==      Total: 1 2022-04-07 15:56:54.665 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.deleteById       : ==>  Preparing: DELETE FROM user WHERE tenant_id = 1 AND id = ? 2022-04-07 15:56:54.665 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.deleteById       : ==> Parameters: 3(Long) 2022-04-07 15:56:54.666 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.deleteById       : <==    Updates: 0 2022-04-07 15:56:54.667 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.updateById       : ==>  Preparing: UPDATE user SET tenant_id = ?, name = ? WHERE tenant_id = 1 AND id = ? 2022-04-07 15:56:54.667 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.updateById       : ==> Parameters: 1(Long), mp(String), 1511976295397867521(Long) 2022-04-07 15:56:54.671 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.updateById       : <==    Updates: 1 2022-04-07 15:56:54.672 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.myCount          : ==>  Preparing: SELECT count(1) FROM user WHERE tenant_id = 1 2022-04-07 15:56:54.672 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.myCount          : ==> Parameters:  2022-04-07 15:56:54.674 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.myCount          : <==      Total: 1 19 2022-04-07 15:56:54.676 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : ==>  Preparing: SELECT a.name AS addr_name, u.id, u.name FROM user_addr a LEFT JOIN user u ON u.id = a.user_id AND u.tenant_id = 1 2022-04-07 15:56:54.676 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : ==> Parameters:  2022-04-07 15:56:54.677 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : <==      Total: 0 2022-04-07 15:56:54.679 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : ==>  Preparing: SELECT a.name AS addr_name, u.id, u.name FROM user_addr a LEFT JOIN user u ON u.id = a.user_id AND u.tenant_id = 1 WHERE a.name LIKE concat(concat('%', ?), '%') 2022-04-07 15:56:54.680 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : ==> Parameters: add(String) 2022-04-07 15:56:54.680 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getAddrAndUser   : <==      Total: 0 2022-04-07 15:56:54.682 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getUserAndAddr   : ==>  Preparing: 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 u.tenant_id = 1 2022-04-07 15:56:54.682 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getUserAndAddr   : ==> Parameters:  2022-04-07 15:56:54.684 DEBUG 26448 --- [nio-8080-exec-6] c.p.m.mapper.UserMapper.getUserAndAddr   : <==      Total: 19

从日志输出中，可以看到自动添加了租户字段信息。

7. 数据库表脚本

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) );  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) );