【微服务】后台管理项目多数据源管理方案实战

前言

1、使用Spring提供的AbstractRoutingDataSource

2、使用MyBatis注册多个SqlSessionFactory

3、使用dynamic-datasource框架

前言

Java后台使用MyBatis-plus 快速访问多个数据源，这里分享三种常用的多数据源管理方案

1、使用Spring提供的AbstractRoutingDataSource

这种方式的核心是使用 Spring 提供的 AbstractRoutingDataSource 抽象类，注入多个数据源。核心代码如下：

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Logger;

@Component
@Primary   // 将该Bean设置为主要注入Bean
public class DynamicDataSource extends AbstractRoutingDataSource {


    // 当前使用的数据源标识
    public static ThreadLocal<String> name=new ThreadLocal<>();

    // 写
    @Autowired
    DataSource dataSource1;
    // 读
    @Autowired
    DataSource dataSource2;


    // 返回当前数据源标识
    @Override
    protected Object determineCurrentLookupKey() {
        return name.get();

    }
    
    @Override
    public void afterPropertiesSet() {

        // 为targetDataSources初始化所有数据源
        Map<Object, Object> targetDataSources=new HashMap<>();
        targetDataSources.put("W",dataSource1);
        targetDataSources.put("R",dataSource2);

        super.setTargetDataSources(targetDataSources);

        // 为defaultTargetDataSource 设置默认的数据源
        super.setDefaultTargetDataSource(dataSource1);

        super.afterPropertiesSet();
    }
}

将自己实现的 DynamicDataSource 注册成为默认的 DataSource 实例后，只需要在每次使用 DataSource 时，提前

改变一下其中的name标识，就可以快速切换数据源。这里使用到AOP功能，关键代码如下：

注解类，用于标识读库或者写库

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/***
 * @Author 黎剑
 * @Slogan 无意与众不同，只因品位出众，致敬未来的你
 */
@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface WR {
    String value() default "W";
}

切面类

import com.lijian.dynamic.datasource.DynamicDataSource;
import com.lijian.dynamic.datasource.annotation.WR;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/***
 * @author 黎剑
 * @slogan 无意与众不同，只因品位出众，致敬未来的你
 */
@Component
@Aspect
public class DynamicDataSourceAspect implements Ordered {

    // 前置
    @Before("within(com.lijian.dynamic.datasource.service.impl.*) && @annotation(wr)")
    public void before(JoinPoint point, WR wr){
        String name = wr.value();
        DynamicDataSource.name.set(name);

        System.out.println(name);
    }

    @Override
    public int getOrder() {
        return 0;
    }

    //TODO 环绕通知
}

数据库配置类


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.lijian.dynamic.datasource.DynamicDataSource;
import com.lijian.dynamic.datasource.plugin.DynamicDataSourcePlugin;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/***
 * @author 黎剑
 * @slogan 无意与众不同，只因品位出众，致敬未来的你
 */
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource1")
    public DataSource dataSource1() {
        // 底层会自动拿到spring.datasource中的配置， 创建一个DruidDataSource
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource2")
    public DataSource dataSource2() {
        // 底层会自动拿到spring.datasource中的配置， 创建一个DruidDataSource
        return DruidDataSourceBuilder.create().build();
    }


    @Bean
    public DataSourceTransactionManager transactionManager1(DynamicDataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }

    @Bean
    public DataSourceTransactionManager transactionManager2(DynamicDataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}

application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    datasource1:
      url: jdbc:mysql://127.0.0.1:3306/datasource1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
      username: root
      password: 123456
      initial-size: 1
      min-idle: 1
      max-active: 20
      test-on-borrow: true
      driver-class-name: com.mysql.cj.jdbc.Driver
    datasource2:
      url: jdbc:mysql://127.0.0.1:3306/datasource2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
      username: root
      password: 123456
      initial-size: 1
      min-idle: 1
      max-active: 20
      test-on-borrow: true
      driver-class-name: com.mysql.cj.jdbc.Driver

服务层实现类

import com.lijian.dynamic.datasource.annotation.WR;
import com.lijian.dynamic.datasource.mapper.FrendMapper;
import com.lijian.dynamic.datasource.entity.Frend;
import com.lijian.dynamic.datasource.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


@Service
public class FrendImplService implements FrendService {

    @Autowired
    FrendMapper frendMapper;


    @Override
    @WR("R")        // 库2
    public List<Frend> list() {
        return frendMapper.list();
    }

    @Override
    @WR("W")        // 库1
    public void save(Frend frend) {
        frendMapper.save(frend);
    }
}

启动类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@MapperScan("com.lijian.dynamic.datasource.mapper")
@EnableAspectJAutoProxy(exposeProxy=true) // 启动AOP
public class DynamicDatasourceApplication {

    public static void main(String[] args) {
        SpringApplication.run(DynamicDatasourceApplication.class, args);
    }

}

2、使用MyBatis注册多个SqlSessionFactory

如果使用MyBatis框架，要注册多个数据源的话，就需要将MyBatis底层的DataSource、SqlSessionFactory、 DataSourceTransactionManager这些核心对象一并进行手动注册。例如：

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;

@Configuration
// 继承mybatis:
// 1. 指定扫描的mapper接口包（主库）
// 2. 指定使用sqlSessionFactory是哪个（主库）
@MapperScan(basePackages = "com.lijian.datasource.dynamic.mybatis.mapper.r",
        sqlSessionFactoryRef="rSqlSessionFactory")
public class RMyBatisConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource2")
    public DataSource dataSource2() {
        // 底层会自动拿到spring.datasource中的配置， 创建一个DruidDataSource
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory rSqlSessionFactory()
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        // 指定主库
        sessionFactory.setDataSource(dataSource2());
        // 指定主库对应的mapper.xml文件
        /*sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/r/*.xml"));*/
        return sessionFactory.getObject();
    }



    @Bean
    public DataSourceTransactionManager rTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource2());
        return dataSourceTransactionManager;
    }

    @Bean
    public TransactionTemplate rTransactionTemplate(){
        return new TransactionTemplate(rTransactionManager());
    }
}

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionOperations;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;

/***
 *
 * 写数据源配置
 */
@Configuration
// 继承mybatis:
// 1. 指定扫描的mapper接口包（主库）
// 2. 指定使用sqlSessionFactory是哪个（主库）
@MapperScan(basePackages = "com.lijian.datasource.dynamic.mybatis.mapper.w",
        sqlSessionFactoryRef="wSqlSessionFactory")
public class WMyBatisConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource1")
    public DataSource dataSource1() {
        // 底层会自动拿到spring.datasource中的配置， 创建一个DruidDataSource
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory wSqlSessionFactory()
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        // 指定主库
        sessionFactory.setDataSource(dataSource1());
        // 指定主库对应的mapper.xml文件
        /*sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/order/*.xml"));*/
        return sessionFactory.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager wTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource1());
        return dataSourceTransactionManager;
    }


    @Bean
    public TransactionTemplate wTransactionTemplate(){
        return new TransactionTemplate(wTransactionManager());
    }
}

注解类

import java.lang.annotation.*;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultiTransactional {

    String[] value() default {};
}

AOP

import com.lijian.datasource.dynamic.mybatis.service.transaction.ComboTransaction;
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;

@Aspect
@Component
public class MultiTransactionAop {

    private final ComboTransaction comboTransaction;

    @Autowired
    public MultiTransactionAop(ComboTransaction comboTransaction) {
        this.comboTransaction = comboTransaction;
    }

    @Pointcut("within(com.lijian.datasource.dynamic.mybatis.service.impl.*)")
    public void pointCut() {
    }

    @Around("pointCut() && @annotation(multiTransactional)")
    public Object inMultiTransactions(ProceedingJoinPoint pjp, MultiTransactional multiTransactional) {
        return comboTransaction.inCombinedTx(() -> {
            try {
                return pjp.proceed();       //执行目标方法
            } catch (Throwable throwable) {
                if (throwable instanceof RuntimeException) {
                    throw (RuntimeException) throwable;
                }
                throw new RuntimeException(throwable);
            }
        }, multiTransactional.value());
    }
}

import com.alibaba.druid.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.concurrent.Callable;
import java.util.stream.Stream;

@Component
public class ComboTransaction {

    @Autowired
    private Db1TxBroker db1TxBroker;

    @Autowired
    private Db2TxBroker db2TxBroker;

    public <V> V inCombinedTx(Callable<V> callable, String[] transactions) {
        if (callable == null) {
            return null;
        }
        // 相当于循环 [wTransactionManager,wTransactionManager]
        Callable<V> combined = Stream.of(transactions)
                .filter(ele -> !StringUtils.isEmpty(ele))
                .distinct()
                .reduce(callable, (r, tx) -> {
                    switch (tx) {
                        case DbTxConstants.DB1_TX:
                            return () -> db1TxBroker.inTransaction(r);
                        case DbTxConstants.DB2_TX:
                            return () -> db2TxBroker.inTransaction(r);
                        default:
                            return null;
                    }
                }, (r1, r2) -> r2);

        try {
            return combined.call();
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.concurrent.Callable;

@Component
public class Db1TxBroker {

    @Transactional(DbTxConstants.DB1_TX)
    public <V> V inTransaction(Callable<V> callable) {
        try {
            return callable.call();
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.concurrent.Callable;

@Component
public class Db2TxBroker {

    @Transactional(DbTxConstants.DB2_TX)
    public <V> V inTransaction(Callable<V> callable) {
        try {
            return callable.call();
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

public class DbTxConstants {

    public static final String DB1_TX = "wTransactionManager";

    public static final String DB2_TX = "rTransactionManager";
}

服务层类

import com.lijian.datasource.dynamic.mybatis.entity.Frend;
import com.lijian.datasource.dynamic.mybatis.mapper.r.RFrendMapper;
import com.lijian.datasource.dynamic.mybatis.mapper.w.WFrendMapper;
import com.lijian.datasource.dynamic.mybatis.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;

import java.util.List;


@Service
public class FrendImplService implements FrendService {

    @Autowired
    private RFrendMapper rFrendMapper;

    @Autowired
    private WFrendMapper wFrendMapper;

    @Autowired
    TransactionTemplate wTransactionTemplate;
    @Autowired
    TransactionTemplate rTransactionTemplate;


    // 读-- 读库
    @Override
    public List<Frend> list() {
        return rFrendMapper.list();
    }

    // 保存-- 写库
    @Override
    public void save(Frend frend) {
        wFrendMapper.save(frend);
    }


    // 保存-- 写库
    @Override
    public void saveW(Frend frend) {
        frend.setName("无羡W");
        wFrendMapper.save(frend);
    }

    // 保存-- 读库
    @Override
    public void saveR(Frend frend) {
        frend.setName("无羡R");
        rFrendMapper.save(frend);
    }

    @Transactional(transactionManager = "wTransactionManager")
    public void saveAll(Frend frend) throws Exception {
        FrendService frendService = (FrendService) AopContext.currentProxy();
        frendService.saveAllR(frend);
    }

    @Transactional(transactionManager = "rTransactionManager")
    public void saveAllR(Frend frend) {
        saveW(frend);
        saveR(frend);
        int a = 1 / 0;
    }
}

这样就完成了读库的注册。而读库与写库之间，就可以通过指定不同的 Mapper 和 XML 文件的地址来进行区分。

3、使用dynamic-datasource框架

dynamic-datasource是 MyBaits-plus 作者设计的一个多数据源开源方案。使用这个框架需要引入对应的 pom 依赖

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

这样就可以在 SpringBoot 的配置文件中直接配置多个数据源，application.yaml

spring:
  datasource:
    dynamic:
      #设置默认的数据源或者数据源组,默认值即为master
      primary: master
      #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      strict: false
      datasource:
        master:
          url: jdbc:mysql://127.0.0.1:3306/datasource1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
          username: root
          password: 123456
          initial-size: 1
          min-idle: 1
          max-active: 20
          test-on-borrow: true
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave_1:
          url: jdbc:mysql://127.0.0.1:3306/datasource2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
          username: root
          password: 123456
          initial-size: 1
          min-idle: 1
          max-active: 20
          test-on-borrow: true
          driver-class-name: com.mysql.cj.jdbc.Driver

这样就配置完成了 master 和 slave_1 两个数据库。

接下来在使用时，只要在对应的方法或者类上添加 @DS注解即可。服务层实现类，例如

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import com.lijian.dynamic.datasource.mapper.FrendMapper;
import com.lijian.dynamic.datasource.entity.Frend;
import com.lijian.dynamic.datasource.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


@Service
public class FrendImplService implements FrendService {

    @Autowired
    FrendMapper frendMapper;


    @Override
    @DS("slave")  // 从库， 如果按照下划线命名方式配置多个  ， 可以指定前缀即可（组名）
    public List<Frend> list() {
        return frendMapper.list();
    }

    @Override
    @DS("#session.userID")
    @DSTransactional #事务控制
    public void save(Frend frend) {
        frendMapper.save(frend);
    }


    @DS("master")
    @DSTransactional
    public void saveAll(){
        // 执行多数据源的操作
    }
}