mybatis-plus实现多租户Saas

àì夳堔傛蜴生んèń 2024-04-07 15:03 46阅读 0赞

## mybatis-plus实现多租户Saas ##

### 实现方式 ###

给对应需要进行数据隔离的表加上租户ID (tenant\_id字段)

### 原理 ###

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

### 步骤 ###

#### 1.给对应表加上tenant\_id字段 ####

#### 2.配置mybatis-plus ####

### 项目结构 ###

![在这里插入图片描述][51ddd875e09147b0984cd11c5c5689bf.png]

### 初始数据 ###

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][https_baomidou.com_pages_aef2f2_tenantlineinnerinterceptor]

[51ddd875e09147b0984cd11c5c5689bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/33f9c40a253941618ba16f8f2b2f721c.png
[https_baomidou.com_pages_aef2f2_tenantlineinnerinterceptor]: https://baomidou.com/pages/aef2f2/#tenantlineinnerinterceptor