SpringBoot+Mybatis实现多租户代码

其实就是往表里加个字段

mybatis拦截器配置

package com.example.tenantmybatisdemo.config;

import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author hrui
 * @date 2024/7/24 6:32
 */
@Configuration
public class MyBatisConfig {


    //添加自定义拦截器
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                //MultiTenantInterceptor和MultiTenantInterceptor1,MultiTenantInterceptor2虽然可用,但修改强度过大
                //使用MultiTenantInterceptor3 通过StatementHandler修改最好
                //那么执行流程是这样的  1.构建 SqlSessionFactory(一次生成)  2.获取 SqlSession(每次链接)
                //3.获取 MappedStatement  4.生成 BoundSql   5.创建 StatementHandler  最后一步是StatementHandler 准备执行sql
                configuration.addInterceptor(new MultiTenantInterceptor3());
            }
        };
    }
}

package com.example.tenantmybatisdemo.config;

import com.example.tenantmybatisdemo.pojo.LoginUser;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;

import java.sql.Connection;
import java.util.Properties;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author hrui
 * @date 2024/7/24 6:33
 */
//prepare方法是在执行SQL语句之前调用的，用于准备和优化SQL语句。因此，这个拦截器将适用于所有类型的SQL操作，包括增、删、改、查等。
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class MultiTenantInterceptor3 implements Interceptor {
    //排除的表名
    private static final Set<String> IGNORE_TABLES = Stream.of("t_user").collect(Collectors.toSet());

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //获取当前登录用户的租户ID  这里只是演示  这个值随意  只要代表用户 redis随便放一个
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        //如果用户已经认证
        if (authentication != null && authentication.isAuthenticated()) {
            LoginUser user = (LoginUser) authentication.getPrincipal();
            Integer tenantId = user.getUserId();

            //获取StatementHandler实例
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = statementHandler.getBoundSql();
            String originalSql = boundSql.getSql();

            //在 SQL 中添加多租户条件
            String newSql = addTenantCondition(originalSql, tenantId);

            //使用反射设置新的 SQL
            MetaObject metaObject = SystemMetaObject.forObject(boundSql);
            metaObject.setValue("sql", newSql);
        }

        //执行目标方法
        return invocation.proceed();
    }

    /**
     * 添加多租户条件到SQL语句
     * @param originalSql 原始SQL语句
     * @param tenantId 租户ID
     * @return 修改后的SQL语句
     */
    private String addTenantCondition(String originalSql, Integer tenantId) {
        //检查是否为 INSERT 语句
        if (originalSql.trim().toLowerCase().startsWith("insert")) {
            // 添加 tenant_id 列和值
            return addTenantIdToInsert(originalSql, tenantId);
        }

        //对于其他 SQL 语句，添加 tenant_id 条件
        String modifiedSql;
        if (originalSql.toLowerCase().contains("where")) {
            modifiedSql = originalSql + " AND tenant_id = " + tenantId;
        } else {
            modifiedSql = originalSql + " WHERE tenant_id = " + tenantId;
        }
        return modifiedSql;
    }

    /**
     * 为 INSERT 语句添加 tenant_id 列和值
     * @param originalSql 原始SQL语句
     * @param tenantId 租户ID
     * @return 修改后的SQL语句
     */
    private String addTenantIdToInsert(String originalSql, Integer tenantId) {
        //找到 VALUES 关键字的位置
        int valuesIndex = originalSql.toLowerCase().indexOf("values");
        if (valuesIndex == -1) {
            throw new IllegalArgumentException("无效的 INSERT SQL 语句: " + originalSql);
        }

        //在列部分添加 tenant_id
        String columnsPart = originalSql.substring(0, valuesIndex).trim();
        int lastParenthesisIndex = columnsPart.lastIndexOf(")");
        if (lastParenthesisIndex == -1) {
            throw new IllegalArgumentException("无效的 INSERT SQL 语句: " + originalSql);
        }
        columnsPart = columnsPart.substring(0, lastParenthesisIndex) + ", tenant_id" + columnsPart.substring(lastParenthesisIndex);

        //在值部分添加 tenant_id
        String valuesPart = originalSql.substring(valuesIndex).trim();
        int lastValuesParenthesisIndex = valuesPart.lastIndexOf(")");
        if (lastValuesParenthesisIndex == -1) {
            throw new IllegalArgumentException("无效的 INSERT SQL 语句: " + originalSql);
        }
        valuesPart = valuesPart.substring(0, lastValuesParenthesisIndex) + ", " + tenantId + valuesPart.substring(lastValuesParenthesisIndex);

        return columnsPart + " " + valuesPart;
    }


}

spring.application.name=tenant-mybatis-demo

server.port=8888

spring.datasource.url=jdbc:mysql://localhost:3306/tenantdemo?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root


#mybatis.mapper-locations=classpath:mappers/*.xml   这样配置会报错 可能是用了@Select注解
mybatis.type-aliases-package=com.example.tenantmybatisdemo

#自动转驼峰
mybatis.configuration.map-underscore-to-camel-case=true

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl