多数据源切换实现多租户系统-CSDN博客

本文链接：https://blog.csdn.net/weixin_48922638/article/details/149960812

1、依赖引入

首先，在项目的pom.xml文件中引入所需的依赖：

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

2、修改yml数据库配置

# 环境配置
spring:
  servlet:
    multipart:
      max-file-size: 1024MB
      max-request-size: 1024MB
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource #数据源为druid
    dynamic:
      primary: master #配置主数据库
      strict: false
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver #数据库驱动
          url: jdbc:mysql://127.0.0.1:3306/your_db?useSSL=false&useUnicode=true&characterEncoding=utf-8&servetTimeZone=Asia/Shanghai
          username: root
          password: root

3、加载数据源

通过继承AbstractJdbcDataSourceProvider类来自定义加载数据源

package com.kaifa.base.config;

import com.baomidou.dynamic.datasource.provider.AbstractJdbcDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.kaifa.base.exception.BusinessException;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class CustomDynamicDataSourceProvider extends AbstractJdbcDataSourceProvider {
        public CustomDynamicDataSourceProvider(String driverClassName, String url, String username, String password) {
            super(driverClassName, url, username, password);
        }
    
        @Override
        protected Map<String, DataSourceProperty> executeStmt(Statement statement)  {
          try {
             Map<String, DataSourceProperty> map = new HashMap<>();
              //通过租户表查询所有租户的数据库连接信息
             ResultSet rs = statement.executeQuery("select code,address,data_name,data_pwd from tenant where status=1 and is_deleted=0");
             /**
              * 获取信息
              */
             while (rs.next()) {
                String code = rs.getString("code");
                String address = rs.getString("address");
                String dbUser = rs.getString("data_name");
                String dbPwd = rs.getString("data_pwd");
                boolean b = checkDatabaseExists("tenant_" + code + "_db", "jdbc:mysql://" + address + "/information_schema",
                      dbUser, dbPwd);
                if(b){
                DataSourceProperty dataSourceProperty = new DataSourceProperty();
                dataSourceProperty
                      .setDriverClassName("com.mysql.cj.jdbc.Driver")
                      .setUrl("jdbc:mysql://"+address+"/tenant_"+code+"_db?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimeZone=Asia/Shanghai")
                      .setUsername(dbUser)
                      .setPassword(dbPwd)
                      .setPoolName("tenant"+code+"DataSource");
                map.put("tenant"+code, dataSourceProperty);
                }
             }
             return map;
          } catch (SQLException e) {
             throw new BusinessException("无法连接到数据库，请检查数据库配置或网络连接！");
          }
       }

    // 检查数据库是否存在
    private boolean checkDatabaseExists(String dbName,String url,String username,String password) {
       try (Connection conn = DriverManager.getConnection(url, username, password)) {
          String query = "SHOW DATABASES LIKE '" + dbName + "'";
          try (PreparedStatement stmt = conn.prepareStatement(query)) {
             try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
             }
          }
       } catch (SQLException e) {
           e.getMessage();
          return false;
       }
    }
}

4、创建动态数据源配置类

import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import java.util.Map;

@Primary
@Configuration
public class DataSourceConfiguration {

    @Autowired
    private DynamicDataSourceProperties properties;

    @Bean
    public DynamicDataSourceProvider customDynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasource = properties.getDatasource();
        DataSourceProperty property = datasource.get("master"); //指定yml配置的主数据库名称
        return new CustomDynamicDataSourceProvider(property.getDriverClassName(), property.getUrl(), property.getUsername(), property.getPassword());
    }
}

5、创建数据源操作类

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.DruidDataSourceCreator;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Set;

@Service
public class DataSourceService {

    @Resource
    private DynamicRoutingDataSource dataSource;

    @Autowired
    private DruidDataSourceCreator dataSourceCreator;

    //根据数据源名称查看数据源信息
    public DataSource get(String key){
        return dataSource.getDataSource(key);
    }

    //获取数据源列表
    public Set<String> getList(){
        return dataSource.getDataSources().keySet();
    }

    //添加一个数据源
    public Set<String> add(DataSourceProperty dsp, String key) {
        dsp.setDriverClassName("com.mysql.cj.jdbc.Driver");
        DataSource creatorDataSource = dataSourceCreator.createDataSource(dsp);
        dataSource.addDataSource(key, creatorDataSource);
        return dataSource.getDataSources().keySet();
    }

    //移除一个数据源
    public Boolean remove(String name) {
        dataSource.removeDataSource(name);
        return Boolean.TRUE;
    }
}

6、创建线程上下文操作类

public class ThreadLocalContext {

    private static ThreadLocal<String> threadLocalTenantId = new ThreadLocal<String>();
    
    public static String getTenantId() {
       return threadLocalTenantId.get();
    }

    public static void setTenantId(String tenantId) {
       threadLocalTenantId.set(tenantId);
    }

    public static void remove() {
       threadLocalTenantId.remove();
    }
}

7、在请求拦截处设置上下文

@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain)
       throws IOException, ServletException {
    HttpServletRequest httpServletRequest = (HttpServletRequest) servletRequest;
    log.info("请求路径：{}",httpServletRequest.getRequestURI());

    // 获取请求的 tenant_id，这里假设从请求头获取
    String tenantId = ((HttpServletRequest) servletRequest).getHeader("Tenant-ID");
    try {
       ThreadLocalContext.setTenantId(tenantId);
       filterChain.doFilter(servletRequest, servletResponse);
    } finally {
       ThreadLocalContext.remove();
    }

}

8、实现AOP对需要操作数据库的方法进行拦截切换数据源

import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.kaifa.base.config.DataSourceService;
import com.kaifa.base.config.ThreadLocalContext;
import com.kaifa.base.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.Set;

@Slf4j
@Aspect
@Component
public class DataSourceAspect {

    @Autowired
    private DataSourceService sourceService;

//拦截所有的mybatis-plus的，这里可以拦截自定义注解也行
    @Pointcut("within(com.baomidou.mybatisplus.extension.service.IService+)") 
    public void dataSourcePointcut() {}

    @Before("dataSourcePointcut()")
    public void doBefore(JoinPoint joinPoint) {
        String tenantId = ThreadLocalContext.getTenantId();
        String master = "master";
        if (StringUtils.isBlank(tenantId)||  master.equals(tenantId)) {
            String peek = DynamicDataSourceContextHolder.peek();
            if (master.equals(peek)) {
                return;
            }
                DynamicDataSourceContextHolder.push(master);
        } else {
            Set<String> set = sourceService.getList();
            if (!set.contains(tenantId)) {
                throw new BusinessException("当前租户未配置数据源，请联系管理员！");
            }
            try {
                DynamicDataSourceContextHolder.push(tenantId);
            } catch (Exception e) {
                throw new BusinessException("当前租户未配置数据源，请联系管理员！");
            }
        }
        Class<?> clazz = joinPoint.getTarget().getClass();
        String methodName = joinPoint.getSignature().getName();
        log.info(clazz + "类-" + methodName + "方法-" + tenantId + "数据源");
    }

    @AfterReturning("dataSourcePointcut()")
    public void doAfter(JoinPoint joinPoint) {
        DynamicDataSourceContextHolder.poll();
    }
}

数据源切换失败的问题：异步消息、异步线程、远程调用、会造成数据源切换失败，需要手动进行数据源的切换。

事务管理的注解@Transactional会导致切换数据源失败，需要修改成@DSTransactional。注意拦截路径的使用，比如拦截的是IService+那么在service里调用另一个service方法就会被切换两次数据源，导致数据源错误。