Mybatis-Plus 多数据源动态切换

多数据源解决方案

目前在SpringBoot框架基础上多数据源的解决方案大多手动创建多个DataSource，后续方案有三：

  1. 继承org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource，使用AOP切面注入相应的数据源 ，但是这种做法仅仅适用单Service方法使用一个数据源可行，如果单Service方法有多个数据源执行会造成误读。
  2. 通过DataSource配置 JdbcTemplateBean，直接使用 JdbcTemplate操控数据源。
  3. 分别通过DataSource创建SqlSessionFactory并扫描相应的Mapper文件和Mapper接口。

MybatisPlus的多数据源解决方案正是AOP，继承了org.springframework.jdbc.datasource.AbstractDataSource，有自己对ThreadLocal的处理。通过注解切换数据源。也就是说，MybatisPlus只支持在单Service方法内操作一个数据源，毕竟官网都指明——“强烈建议只注解在service实现上”。

而后，注意看com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder，也就是MybatisPlus是如何切换数据源的。

pom引用

<mybatisplus.version>3.5.0</mybatisplus.version>

            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
                <version>${mybatisplus.version}</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-core</artifactId>
                <version>${mybatisplus.version}</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-extension</artifactId>
                <version>${mybatisplus.version}</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-annotation</artifactId>
                <version>${mybatisplus.version}</version>
            </dependency>
            <!-- https://mvnrepository.com/artifact/com.alibaba/transmittable-thread-local -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>transmittable-thread-local</artifactId>
                <version>2.12.2</version>
            </dependency>

数据库配置

spring:
  datasource:
    dynamic:
      primary: generali
      strict: false  
      datasource:
        generali:
          url: jdbc:mysql://awschina-nx-xman-gis-rds-01.crag5sximeqt.rds.cn-northwest-1.amazonaws.com.cn:3306/leads_lms?allowMultiQueries=true&multiStatementAllow=true&serverTimezone=GMT%2B8&autoReconnect=true&failOverReadOnly=false&useSSL=false&rewriteBatchedStatements=true
          username: admin
          password: VP4tR5BmBVvaYGyc
        gen:
          url: jdbc:mysql://awschina-nx-xman-gis-rds-01.crag5sximeqt.rds.cn-northwest-1.amazonaws.com.cn:3306/leads_gen?allowMultiQueries=true&multiStatementAllow=true&serverTimezone=GMT%2B8&autoReconnect=true&failOverReadOnly=false&useSSL=false&rewriteBatchedStatements=true
          username: admin
          password: VP4tR5BmBVvaYGyc

配置租户与租户id

tenantcode:
  ignoreUrl: /common/**,/nano/**,/test/**,/i18n/**,/**/v2/api-docs,/**/login,/**/token,/health
  mapping:
    generali: 1
    gen: 2

代码：

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * @author qhong
 * @date 2022/2/16 11:53
 **/
@Configuration
@Data
@ConfigurationProperties(prefix = "tenantcode")
public class TenantCodeConfig {

    /**
     * 不进行身份校验的url配置
     */
    private String ignoreUrl;

    /**
     * 租户code与db中tenantId匹配关系 ，例如 generali：1
     */
    private Map<String, Long> mapping;

    /**
     * 根据tenantCode获取tenantId
     * @param tenantCode
     * @return
     */
    public Long getTenantIdByTenantCode(String tenantCode) {
        if (StringUtils.isEmpty(tenantCode)) {
            return null;
        }
        return mapping.get(tenantCode);
    }

    /**
     * 根据tenantId获取tenantCode
     * @param tenantId
     * @return
     */
    public String getTenantCodeByTenantId(Long tenantId) {
        if (tenantId == null) {
            return null;
        }
        return mapping.entrySet().stream().filter(x -> x.getValue().equals(tenantId)).map(x -> x.getKey()).findFirst().orElse(null);
    }
}

租户code全局获取

import com.alibaba.ttl.TransmittableThreadLocal;
import org.springframework.util.StringUtils;

import java.util.ArrayDeque;
import java.util.Deque;

/**
 * @author qhong
 * @date 2022/1/26 16:30
 **/
public class ThreadContextHolder {

    private static final ThreadLocal<Deque<String>> CONTENT_HOLDER = new TransmittableThreadLocal<Deque<String>>() {
        @Override
        protected Deque<String> initialValue() {
            return new ArrayDeque<>();
        }
    };

    private ThreadContextHolder() {
    }

    /**
     * 获得当前线程数据源
     * @return 数据源名称
     */
//    public static String peek() {
//        return CONTENT_HOLDER.get().peek();
//    }

    /**
     * 获得当前线程数据源
     *
     * @return 数据源名称
     */
    public static String element() {
        Deque<String> strings = CONTENT_HOLDER.get();
        if (strings == null || strings.size() == 0) {
            return null;
        }
        return strings.element();
    }

    /**
     * 设置数据源信息
     */
    public static void push(String ds) {
        CONTENT_HOLDER.get().push(StringUtils.isEmpty(ds) ? "" : ds);
    }

    /**
     * 清空当前线程数据源
     */
    public static void poll() {
        Deque<String> deque = CONTENT_HOLDER.get();
        deque.poll();
        if (deque.isEmpty()) {
            CONTENT_HOLDER.remove();
        }
    }

    /**
     * 强制清空本地线程
     */
    public static void clear() {
        CONTENT_HOLDER.remove();
    }
}

web全局请求拦截：

import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.zhongan.leads.config.AuthManager;
import com.zhongan.leads.config.TenantCodeConfig;
import com.zhongan.leads.dto.UserInfo;
import com.zhongan.leads.utils.ThreadContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.AntPathMatcher;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.List;

/**
 * @author qhong
 * @date 2022/2/16 11:38
 **/
@Component
@Slf4j
public class TenantInterceptor extends HandlerInterceptorAdapter {

    @Autowired
    private AuthManager authManager;
    @Autowired
    private TenantCodeConfig tenantCodeConfig;

    private static final AntPathMatcher antPathMatcher = new AntPathMatcher();

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        if (StringUtils.hasText(tenantCodeConfig.getIgnoreUrl()) && checkIgnoreUrl(tenantCodeConfig.getIgnoreUrl())) {
            //不进行身份校验的，直接通过
            return true;
        }
        try {
            UserInfo userInfo = authManager.getUserInfo();
            String tenantCode = userInfo.getTenantId().toString();
            DynamicDataSourceContextHolder.push(tenantCode);
            ThreadContextHolder.push(tenantCode);
            return true;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
            throws Exception {
        DynamicDataSourceContextHolder.clear();
        ThreadContextHolder.clear();
    }

    /**
     * 校验当前访问的url是否忽略校验的路径
     *
     * @param url
     * @return
     */
    private boolean checkIgnoreUrl(String url) {
        if (StringUtils.isEmpty(url)) {
            return false;
        }
        String currentUrl = authManager.getRequestURI();
        List<String> urlList = Arrays.asList(url.split(","));
        for (int i = 0; i < urlList.size(); i++) {
            if (antPathMatcher.match(urlList.get(i), currentUrl)) {
                return true;
            }
        }
        return false;
    }
}

定时任务：

定时任务需要遍历所有的租户数据库

import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.zhongan.leads.config.TenantCodeConfig;
import com.zhongan.leads.utils.ThreadContextHolder;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


/**
 * @author qhong
 * @date 2022/2/17 14:16
 * 用于标志多租户定时任务，对各个租户db进行轮询处理,return 为null
 **/
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Aspect
@Component
@Slf4j
public class ScheduledMultiTenantAspect {

    private final TenantCodeConfig tenantCodeConfig;

    @Pointcut("@annotation(org.springframework.scheduling.annotation.Scheduled)")
    public void scheduledMultiTenantAspect() {
    }

    @Around(value = "scheduledMultiTenantAspect()")
    public Object aroundAdvice(ProceedingJoinPoint pjp) throws Throwable {
        Method method = ((MethodSignature) pjp.getSignature()).getMethod();
        int tenantSize = tenantCodeConfig.getMapping().size();
        if (tenantSize == 1) {
            pjp.proceed();
            return null;
        }
        ExecutorService fixedThreadPool = Executors.newFixedThreadPool(tenantSize);

        tenantCodeConfig.getMapping().entrySet().forEach(x -> {
            fixedThreadPool.execute(() -> {
                ThreadContextHolder.push(x.getKey());
                DynamicDataSourceContextHolder.push(x.getKey());
                log.info("ScheduledMultiTenantAspect,method:{},tenantcode:{}", method.getName(), x.getKey());
                try {
                    pjp.proceed();
                } catch (Throwable throwable) {
                    throwable.printStackTrace();
                } finally {
                    ThreadContextHolder.clear();
                    DynamicDataSourceContextHolder.clear();
                }
            });
        });
        fixedThreadPool.shutdown();
        while (true) {
            if (fixedThreadPool.isTerminated()) {
                //log.info("ScheduledMultiTenantAspect,method:{} terminated", method.getName());
                break;
            }
        }
        //log.info("ScheduledMultiTenantAspect,method:{} end", method.getName());
        return null;
    }
}

Async异步任务：

DynamicDataSourceContextHolder不支持异步，线程池，所以异步任务需要自己处理

public class MultiTenantCodeHelper {

    /**
     * DynamicDataSourceContextHolder不支持异步，线程池，使用该方法赋值
     */
    public static void setDynamicDataSourceByThreadHolderForAsync(){
        String tenantCode = ThreadContextHolder.element();
        DynamicDataSourceContextHolder.push(tenantCode);
    }
}


import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.zhongan.leads.utils.MultiTenantCodeHelper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * @author qhong
 * @date 2022/2/17 19:18
 **/
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Aspect
@Component
@Slf4j
public class AsyncMultiTenantAspect {

    @Pointcut("@annotation(org.springframework.scheduling.annotation.Async)")
    public void asyncMultiTenantAspect() {
    }

    @Around(value = "asyncMultiTenantAspect()")
    public Object aroundAdvice(ProceedingJoinPoint pjp) throws Throwable {
        try {
            MultiTenantCodeHelper.setDynamicDataSourceByThreadHolderForAsync();
            return pjp.proceed();
        } catch (Exception e) {
            throw e;
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }
}

Redis缓存：

需要区分各个租户的缓存信息，防止重复

日志：

需要区分各个租户的日志信息，方便后续查看

总结

mybatis-plus的动态数据库切换，必须在controller方法之前，因为controller初始化的时候就设置默认db

参考：

dynamic-datasource

MybatisPlus多数据源及事务解决思路

mybatis-plus多数据源解析

mybatis-plus多数据源切换失败

posted @ 2022-10-25 21:16  hongdada  阅读(7141)  评论(0)  编辑  收藏  举报