动态数据源切换方案（二）

方案二：（在切面设置routing-key）         此方案数据源切换部分还是由Spring提供的  AbstractRoutingDataSource来实现，但是指定routing-key 放在AOP上去指定，在需要切换数据源的方法，或者类上加注解，在请求接口时，AOP会拦截到方法，根据会话中的机构值，查询机构和数据库对应关系表，将对应数据源放入管理 不同线程当前数据源 的 DsContextHolder,在 determineCurrentLookupKey  （）指定数据源时，取出DsContextHolder中存放的值，作为 routing-key 。 方案二的优点：         在切换数据源时，可以通过在类或具体的方法上添加注解的方式，决定当前请求的方法要使用哪个数据库，使用上会更加灵活。如果要实现方法级的数据源切换控制，使用方案二会更好一点。 方案二的缺点：         当前需求是根据登录用户的机构来确定使用哪个数据源的，方案二的实现相对复杂，并且冗余。（场景不是很匹配）

 

测试环境准备：（这里和方案一的测试环境一致）

（1）创建三个数据库：jeecg、db1、db2（这三个库都用jeecg-boot 的建库语句创建，表结构完全一致）

（2）创建测试用表：

CREATE TABLE `test` (
  `NAME` varchar(32) DEFAULT NULL COMMENT '数据库名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意：分别在三个库分别创建这张表，并添加对应的值

（3）在用户表中创建三个用户，分别对应三个库，使用post字段记录用户对应的数据库

insert  into `sys_user`(`id`,`username`,`realname`,`password`,`salt`,`avatar`,`birthday`,`sex`,`email`,`phone`,`org_code`,`status`,`del_flag`,`third_id`,`third_type`,`activiti_sync`,`work_no`,`post`,`telephone`,`create_by`,`create_time`,`update_by`,`update_time`,`user_identity`,`depart_ids`,`rel_tenant_ids`,`client_id`)
values
('1440505914032746498','db1','db1','7e6aba936f80ec0e67548952f27077a1','wXUzx9cx',NULL,'2021-09-22 10:38:33',1,'767608432@qq.com','13718444519','A01',1,0,NULL,NULL,1,'001','db1',NULL,NULL,'2021-09-22 10:39:08',NULL,NULL,2,'c6d7cb4deeac411cb3384b1b31278596','1','{"beanName":"db1","driverClassName":"com.mysql.cj.jdbc.Driver","password":"root","testOnBorrow":true,"url":"jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai","username":"root","validationQuery":"select 1"}')
,('1440556389574676482','db2','db2','453fecdd611ddf8a','fmW6aCnl',NULL,'2021-09-22 13:59:07',1,'767608438@qq.com','13718444516','A01',1,0,NULL,NULL,1,'002','db2',NULL,NULL,'2021-09-22 13:59:42',NULL,NULL,1,'',NULL,'{"beanName":"db2","driverClassName":"com.mysql.cj.jdbc.Driver","password":"root","testOnBorrow":true,"url":"jdbc:mysql://127.0.0.1:3306/db2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai","username":"root","validationQuery":"select 1"}'),('3d464b4ea0d2491aab8a7bde74c57e95','zhangsan','张三','02ea098224c7d0d2077c14b9a3a1ed16','x5xRdeKB','https://static.jeecg.com/temp/jmlogo_1606575041993.png',NULL,NULL,NULL,NULL,'财务部',1,0,NULL,NULL,1,'0005','总经理',NULL,'admin','2020-05-14 21:26:24','admin','2020-09-09 14:42:51',1,'','',NULL),('a75d45a015c44384a04449ee80dc3503','jeecg','jeecg','58a714412072f0b9','mIgiYJow','https://static.jeecg.com/temp/国炬软件logo_1606575029126.png',NULL,1,NULL,NULL,'A02A01',1,0,NULL,NULL,1,'00002','devleader',NULL,'admin','2019-02-13 16:02:36','admin','2020-11-26 15:16:05',1,'',NULL,NULL)
,('e9ca23d68d884d4ebb19d07889727dae','admin','管理员','cb362cfeefbf3d8d','RCGTeGiH','https://static.jeecg.com/temp/国炬软件logo_1606575029126.png','2018-12-05 00:00:00',1,'jeecg@163.com','18611111111','A01',1,0,NULL,NULL,1,'00001','master',NULL,NULL,'2019-06-21 17:54:10','admin','2020-07-10 15:27:10',2,'c6d7cb4deeac411cb3384b1b31278596','',NULL);

方案步骤：

（1）配置多数据源

（2）定义管理不同线程当前数据源的操作类DsContextHolder

（3）自定义注解

（4）自定义AOP拦截需要切换数据源的方法，将此方法要使用的数据源放入到DsContextHolder中

（5）将多数据源都加入自定义数据源 DynamicDataSource中（继承spring 的AbstractRoutingDataSource） 

（6）配置动态数据源（SqlSessionFactory 使用动态数据源作为绑定的数据源）

 实现步骤：

第一步：配置多数据源

datasource:
  master:
    url: jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    # 多数据源配置
  db1:
    url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
  db2:
    url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

第二步：定义管理不同线程当前数据源的操作类DsContextHolder

package org.jeecg.config.datasource.toolkit;

import org.springframework.core.NamedThreadLocal;
import org.springframework.util.StringUtils;

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

public class DsContextHolder {

    private static final ThreadLocal> LOOKUP_KEY_HOLDER = new NamedThreadLocal>("dynamic-datasource") {
        @Override
        protected Deque initialValue() {
            return new ArrayDeque();
        }
    };

    private DsContextHolder() {
    }

    public static String peek() {
        return (String)((Deque)LOOKUP_KEY_HOLDER.get()).peek();
    }

    public static void push(String ds) {
        ((Deque)LOOKUP_KEY_HOLDER.get()).push(StringUtils.isEmpty(ds) ? "" : ds);
    }

    public static void poll() {
        Deque deque = (Deque)LOOKUP_KEY_HOLDER.get();
        deque.poll();
        if (deque.isEmpty()) {
            LOOKUP_KEY_HOLDER.remove();
        }

    }

    public static void clear() {
        LOOKUP_KEY_HOLDER.remove();
    }
}

第三步：自定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public  @interface DsSwitcher {
    String value() default "master";
}

第四步：自定义AOP拦截需要切换数据源的方法，将此方法要使用的数据源放入到DsContextHolder中

@Component
@Aspect
public class DsSwitcherAspect {

    @Pointcut("@annotation(org.jeecg.config.datasource.annotation.DsSwitcher)|| @within(org.jeecg.config.datasource.annotation.DsSwitcher)")
    private void serviceMethod() {}

    
    @Before("serviceMethod()")
    public void transServiceMethod(JoinPoint joinPoint) {
        switchDataSource(joinPoint);
    }

    
    @After("serviceMethod()")
    public void clearDs(JoinPoint joinPoint) {
        clearDataSource(joinPoint);
    }

    
    private void switchDataSource(JoinPoint joinPoint) {
        MethodSignature signature =
                (MethodSignature) joinPoint.getSignature();
        DsSwitcher dsSwitcher =
                signature.getMethod().getAnnotation(DsSwitcher.class);
        if (!Objects.isNull(dsSwitcher)) {
            LoginUser loginUser=(LoginUser) SecurityUtils.getSubject().getPrincipal();
            if (loginUser != null) {
                String dbtype = loginUser.getPost();
                System.out.println("current orgcode--" + dbtype);
                DsContextHolder.push(dbtype);
            }
        }
    }

    
    private void clearDataSource(JoinPoint joinPoint) {
        MethodSignature signature =
                (MethodSignature) joinPoint.getSignature();
        DsSwitcher dsSwitcher =
                signature.getMethod().getAnnotation(DsSwitcher.class);
        if (!Objects.isNull(dsSwitcher)) {
            DsContextHolder.clear();
        }
    }
}

第五步：多数据源都加入自定义数据源 DynamicDataSource中（继承spring 的AbstractRoutingDataSource）

public class DynamicDataSource extends AbstractRoutingDataSource {
  public  DynamicDataSource(DataSource defaultTargetDataSource, Map targetDataSources) {
    super.setDefaultTargetDataSource(defaultTargetDataSource);
    super.setTargetDataSources(targetDataSources);
    super.afterPropertiesSet();
  }

  @Override
  protected Object determineCurrentLookupKey() {

      return DsContextHolder.peek();
  }
}

（6）配置动态数据源（SqlSessionFactory 使用动态数据源作为绑定的数据源）

package org.jeecg.config.datasource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.jeecg.config.init.DynamicDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class MultiDataSourceConfig {

    //实体类位置
    @Value("${mybatis-plus.mapper-locations}")
    private String mapperLocations;
    //实体类位置
    @Value("${mybatis-plus.type-aliases-package}")
    private String typeAliasesPackage;

    @Bean(name = "master")
    @ConfigurationProperties("spring.datasource.dynamic.datasource.master")
    public DataSource masterDataSource() {
        return  DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "db1")
    @ConfigurationProperties("spring.datasource.dynamic.datasource.db1")
    public DataSource db1DataSource() {
        return  DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "db2")
    @ConfigurationProperties("spring.datasource.dynamic.datasource.db2")
    public DataSource db2DataSource() {
        return  DruidDataSourceBuilder.create().build();
    }

    
    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(@Qualifier("master") DataSource masterDataSource,
                                        @Qualifier("db1") DataSource db1Datasource,
                                        @Qualifier("db2") DataSource db2Datasource) {
        //多数据源设置
        Map targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);

        //以下数据源应该从主数据库表里面读出来，目前是直接写死在代码里
        //初始化db1数据源
        targetDataSources.put("db1", db1Datasource);
        targetDataSources.put("db2", db2Datasource);
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(
            PaginationInterceptor paginationInterceptor,
            @Qualifier("dynamicDataSource") DataSource multiDataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(multiDataSource);
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        //添加分页功能
        sqlSessionFactory.setPlugins(new Interceptor[]{
                paginationInterceptor           
        });
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        //配置mapper位置
        sqlSessionFactory.setMapperLocations(resolver.getResources(mapperLocations));  
        //配置实体类位置
        sqlSessionFactory.setTypeAliasesPackage(typeAliasesPackage);
        return sqlSessionFactory.getObject();
    }
}

方案测试：

使用admin 登录，查询test 表对应的数据源为master

使用db1 登录，查询test 表对应的数据源为db1

 使用db2 登录，查询test 表对应的数据源为db2