mybatis plus实现多租户方案

注意：方案的数据库mapper层采用的是tk.mybatis

maven依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.5</version>
</dependency>
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
    <exclusions>
        <exclusion>
            <artifactId>mybatis</artifactId>
            <groupId>org.mybatis</groupId>
        </exclusion>
    </exclusions>
</dependency>

多租户插件类 MultiTenantPlugin

public class MultiTenantPlugin implements Interceptor{

    private static PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

    private static Set<String> multiTenantTable = new HashSet<>();

    @Value("${multi.tenant.table:}")
    private String multiTenantTables;

    @Value("${multi.tenant.enable:}")
    private boolean multiTenantEnable;

    @PostConstruct
    public void afterPropertiesSet() {
        if (multiTenantEnable) {
            initMultiTenantTable();
            initTenantSqlParser();
        }
    }

    /**
     * 初始化多租户Dao层sql相关转换器
     */
    private void initTenantSqlParser() {
        List<ISqlParser> sqlParserList = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new TenantSqlParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean select) {
                if (multiTenantEnable) {
                    return new StringValue(RequestHolder.getTenantId());
                } else {
                    String tenantId = RequestHolder.getTenantId();
                    return new StringValue(StringUtils.isEmpty(tenantId) ? "" : tenantId);
                }
            }

            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }

            @Override
            public boolean doTableFilter(String tableName) {
                return !multiTenantTable.contains(tableName);
            }
        });

        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);

        paginationInterceptor.setSqlParserFilter(metaObject -> {
            if (MultiTenantSqlFilterHelper.getSqlParserInfo(metaObject)) {
                return true;
            }
            return false;
        });
    }

    /**
     * 初始化多租户相关表
     */
    private void initMultiTenantTable() {
        multiTenantTable = Sets.newHashSet(multiTenantTables.split(","));
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        return paginationInterceptor.intercept(invocation);
    }

    @Override
    public Object plugin(Object target) {
        return paginationInterceptor.plugin(target);
    }

    @Override
    public void setProperties(Properties properties) {
        paginationInterceptor.setProperties(properties);
    }


}

配置文件

# 多租户表配置 多张表用英文逗号分隔
multi.tenant.table=table1,table2

# 多租户开关
multi.tenant.enable=true

mybatis-plugin.xml新增多租户插件配置

<plugins>
        <plugin  interceptor="com.fudai.interceptor.MultiTenantPlugin"/>
</plugins>

特殊Mapper方法sql不拼接租户id设计

• 如果Mapper接口使用的是com.baomidou.mybatisplus.core.mapper,可以直接用mybatisplus自带的注解@SqlParser。否则用以下方法自定义注解做过滤（用的是tk.mybatis.mapper.common.Mapper）。

（1）定义Mapper方法层的sql过滤注解

（Mapper层特定的方法不走多租户加上改注解）

import java.lang.annotation.*;

/**
 * @className: SqlFilter
 * @description:
 * @author: fudai
 * @date: 2022-12-14 18:04
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface SqlFilter {
    boolean filter() default false;
}

（2）新增多租户sql过滤工具类

import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wacai.loan.screw.base.annotation.SqlFilter;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.core.annotation.AnnotationUtils;

import java.lang.reflect.Method;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @className: MultiTenantSqlFilterHelper
 * @description: 多租户sql过滤工具类
 * @author: fudai
 * @date: 2022-12-14 18:03
 */
public class MultiTenantSqlFilterHelper {
    public static final String DELEGATE_MAPPED_STATEMENT = "delegate.mappedStatement";
    /**
     * SQL 解析缓存
     * key 可能是 mappedStatement 的 ID,也可能是 class 的 name
     */
    private static final Map<String, Boolean> SQL_FILTER_INFO_CACHE = new ConcurrentHashMap<>();

    /**
     * 初始化缓存 Mapper方法上 SqlFilter 注解信息
     *
     * @param mapperClassName Mapper Class Name
     * @param method          Method
     */
    public static void initSqlParserInfoCache(String mapperClassName, Method method) {
        SqlFilter sqlFilter = AnnotationUtils.findAnnotation(method,SqlFilter.class);
        if (sqlFilter != null) {
            if (SQL_FILTER_INFO_CACHE.containsKey(mapperClassName)) {
                // mapper 接口上有注解
                Boolean value = SQL_FILTER_INFO_CACHE.get(mapperClassName);
                if (!value.equals(sqlFilter.filter())) {
                    // 取反,不属于重复注解,放入缓存
                    String sid = mapperClassName + StringPool.DOT + method.getName();
                    SQL_FILTER_INFO_CACHE.putIfAbsent(sid, sqlFilter.filter());
                }
            } else {
                String sid = mapperClassName + StringPool.DOT + method.getName();
                SQL_FILTER_INFO_CACHE.putIfAbsent(sid, sqlFilter.filter());
            }
        }
    }

    /**
     * 获取 SqlFilter 注解信息
     *
     * @param metaObject 元数据对象
     */
    public static boolean getSqlParserInfo(MetaObject metaObject) {
        String id = getMappedStatement(metaObject).getId();
        Boolean value = SQL_FILTER_INFO_CACHE.get(id);
        if (value != null) {
            return value;
        }
        String mapperName = id.substring(0, id.lastIndexOf(StringPool.DOT));
        return SQL_FILTER_INFO_CACHE.getOrDefault(mapperName, false);
    }

    /**
     * 获取当前执行 MappedStatement
     *
     * @param metaObject 元对象
     */
    public static MappedStatement getMappedStatement(MetaObject metaObject) {
        return (MappedStatement) metaObject.getValue(DELEGATE_MAPPED_STATEMENT);
    }
}

（3）初始化多租户Mapper方法sql过滤注解缓存服务

import com.wacai.loan.screw.base.util.MultiTenantSqlFilterHelper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.common.Mapper;

import java.lang.reflect.Method;
import java.util.Map;

/**
 * @className: MultiTenantSqlFilterCacheService
 * @description: 多租户Mapper方法sql过滤注解缓存服务
 * @author: fudai
 * @date: 2022-12-14 16:13
 */
@Service
@Slf4j
public class MultiTenantSqlFilterCacheService implements ApplicationContextAware {

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        Map<String, Mapper> mapperMap = applicationContext.getBeansOfType(Mapper.class);
        mapperMap.forEach((s, mapper) -> {
            try {
                Method[] methods = mapper.getClass().getMethods();
                for (Method method : methods) {
                    if (!method.isBridge()) {
                        MultiTenantSqlFilterHelper.initSqlParserInfoCache(applicationContext.getType(s).getName(), method);
                    }
                }
            } catch (Exception e) {
                log.error("多租户Mapper方法sql过滤注解缓存异常，mapper：{},异常：", s, e);
            }
        });
    }

}