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

方案三：（在登录时指定routing-key,但是数据源从不再从配置文件加载，而是改为从数据库加载 ）

Spring 内置了一个 AbstractRoutingDataSource，它可以把多个数据源配置成一个Map，然后，根据不同的key返回不同的数据源。因为AbstractRoutingDatasource 也是一个DataSource接口,因此，应用程序可以先设置好key,访问数据库的代码就可以从AbstractRoutingDatasource 拿到对应的一个真实的数据源，从而访问指定的数据库。AbstractRoutingDataSource 的targetDataSources属性,该属性存放数据源属性，当把数据源放入这个Map中，就可以通过指定routing-key来切换这个Map中存放的数据源。所以可以从数据库中查询数据源信息，创建数据源放入targetDataSources，就可以实现数据源的动态加载，而不需要在配置文件中配置了。

测试环境准备：（这里用sys_user表的client_id 来记录datasource 的信息）

注意：这里为了简化测试，没有动态的从数据库中的数据源信息表中读取数据源信息，而是把生成好的数据源信息放入sys_user表的client_id 字段，省略了从表中读取数据源信息，生成数据源bean,并放入会话中的过程。

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

（2）创建测试用表：

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

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

（3）创建数据源信息表

CREATE TABLE `other_datasource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datasource_name` varchar(64) DEFAULT NULL,
  `database_ip` varchar(64) DEFAULT NULL,
  `database_port` varchar(64) DEFAULT NULL,
  `database_name` varchar(64) DEFAULT NULL,
  `database_username` varchar(64) DEFAULT NULL,
  `database_password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

 数据源信息表数据

insert  into `other_datasource`(`id`,`datasource_name`,`database_ip`,`database_port`,`database_name`,`database_username`,`database_password`) values (1,'db1','127.0.0.1','3306','db1','root','root'),(2,'db2','127.0.0.1','3306','db2','root','root');

（4）在用户表中创建三个用户，分别对应三个库，使用client_id字段记录当前用户机构对应的数据源信息。

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);

（5）生成数据源信息，将数据源信息填入sys_user 表的client_id字段上

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT,classes = JeecgSystemApplication.class)
@SuppressWarnings({"FieldCanBeLocal", "SpringJavaAutowiredMembersInspection"})
public class SysUserTest {

    @Autowired
    private IOtherDatasourceService iOtherDatasourceService;

    @Autowired
    private SysUserMapper sysUserMapper;

    
    @Test
    public void createDatasourceinfo() {
        
        //查询数据源信息
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.eq("datasource_name", "db2");
        OtherDatasource datasource = iOtherDatasourceService.getOne(wrapper);
        //根据数据源信息生成datasourceBean
        DataSourceBean dataSourceBean = new DataSourceBean(new DataSourceBeanBuilder(
                datasource.getDatasourceName(),datasource.getDatabaseIp(),datasource.getDatabasePort(),
                datasource.getDatabaseName(), datasource.getDatabaseUsername(), datasource.getDatabasePassword()
        ));
        //将datasourceBean转为json 串
        String dataSourceStr = JSON.toJSonString(dataSourceBean);
        UpdateWrapper updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("post", "db2");

        SysUser user = new SysUser();
        user.setClientId(dataSourceStr);
        //将数据源信息写入sys_user 的client_id
        sysUserMapper.update(user, updateWrapper);
        System.out.println(user);
    }
}

实现步骤：

第一步：配置主数据源

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

第二步：编写数据源信息bean

package org.jeecg.config.dynamicdatasource.bean;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.jeecg.config.dynamicdatasource.builder.DataSourceBeanBuilder;


import java.io.Serializable;



public class DataSourceBean implements Serializable{

    //注册在spring中bean名字
    private String beanName;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private String validationQuery;
    private Boolean testOnBorrow;

    public String getBeanName() {
        return beanName;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public Boolean getTestonBorrow() {
        return testOnBorrow;
    }

    public void setBeanName(String beanName) {
        this.beanName = beanName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestonBorrow(Boolean testOnBorrow) {
        this.testonBorrow = testOnBorrow;
    }

    public DataSourceBean(DataSourceBeanBuilder builder) {
        this.beanName = builder.getBeanName();
        this.driverClassName = builder.getDriverClassName();
        this.url = builder.getUrl();
        this.username = builder.getUsername();
        this.password = builder.getPassword();
        this.validationQuery = builder.getValidationQuery();
        this.testonBorrow = builder.getTestonBorrow();
    }

    public DataSourceBean() {
        super();
    }

    @Override
    public String toString() {
        return "DataSourceBean{" +
                "beanName='" + beanName + ''' +
                ", driverClassName='" + driverClassName + ''' +
                ", url='" + url + ''' +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", validationQuery='" + validationQuery + ''' +
                ", testonBorrow=" + testonBorrow +
                '}';
    }
}

第三步：编写数据源bean 的建造者

package org.jeecg.config.dynamicdatasource.builder;


import org.jeecg.config.dynamicdatasource.bean.DataSourceBean;


public class DataSourceBeanBuilder {

    private String beanName;
    private String driverClassName = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
    private String databaseIP;
    private String databasePort;
    private String databaseName;
    private String username;
    private String password;
    private String validationQuery = "select 1";
    private Boolean testonBorrow = true;

    public static void main(String[] args) {
        String ip = "127.0.0.1";
        String port = "3306";
        String db = "db1";
        String url = "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";


        String format = String.format(url, ip, port, db);
        System.out.println(format);
    }

    public DataSourceBeanBuilder(String beanName, String databaseIP, String databasePort, String databaseName,
                                 String username, String password) {
        super();
        this.beanName = beanName;
        this.databaseIP = databaseIP;
        this.databasePort = databasePort;
        this.databaseName = databaseName;
        this.username = username;
        this.password = password;
    }

    public DataSourceBeanBuilder() {
        super();
    }

    public DataSourceBeanBuilder driverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
        return this;
    }

    public DataSourceBeanBuilder validationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
        return this;
    }

    public DataSourceBeanBuilder testonBorrow(Boolean testOnBorrow) {
        this.testonBorrow = testOnBorrow;
        return this;
    }

    public String getUrl() {
        return String.format(url, this.databaseIP, this.databasePort, this.databaseName);
    }

    public String getBeanName() {
        return beanName;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public String getDatabaseIP() {
        return databaseIP;
    }

    public String getDatabasePort() {
        return databasePort;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public Boolean getTestonBorrow() {
        return testOnBorrow;
    }
}

第四步：编写RoutingDataSource

使用Spring 内置的RoutingDataSource,把真实的数据源代理为一个动态数据源，并且根据当前登录信息中的client_id(数据源信息)创建数据源，数据库访问路由至新建的数据源

package org.jeecg.config.dynamicdatasource;


import com.alibaba.druid.pool.DruidDataSource;


import com.alibaba.fastjson.JSON;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.config.dynamicdatasource.bean.DataSourceBean;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;


public class DynamicDataSource extends AbstractRoutingDataSource implements ApplicationContextAware{

    private ApplicationContext applicationContext;

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

    
    @Override
    protected Object determineCurrentLookupKey() {

        DataSourceBean dataSourceBean = null;
        try {
            //1.获取当前登录用户
            LoginUser loginUser=(LoginUser) SecurityUtils.getSubject().getPrincipal();
            if (null == loginUser) {
                //如果未登录，返回null，让应用获取到主数据源进行登录
                return null;
            }
            //这里通过clientid 字段记录当前用户机构对应的数据源信息
            String clientId = loginUser.getClientId();
            System.out.println(clientId);
            //2.获取手动设置的数据源参数DataSourceBean
            dataSourceBean = JSON.parseObject(clientId, DataSourceBean.class);
            if (dataSourceBean == null) {
                    return null;
            }

            System.out.println("current database is :" + dataSourceBean.toString());
            //2.获取AbstractRoutingDataSource 的targetDataSources属性,该属性存放数据源属性
            Map targetSourceMap = getTargetSource();
            synchronized (this) {
                
                //3.判断targetDataSource中是否已经存在要设置的数据源bean,存在的话，则直接返回beanName
                if (!targetSourceMap.keySet().contains(dataSourceBean.getBeanName())) {
                    //不存在
                    //3.1 先在spring 容器中创建该数据源bean
                    Object dataSource = createDataSource(dataSourceBean);
                    //3.2 在创建后的bean，放入到targetDataSource Map 中
                    targetSourceMap.put(dataSourceBean.getBeanName(), dataSource);
                    
                    super.afterPropertiesSet();
                }
            }
            for (Map.Entry entry : targetSourceMap.entrySet()) {
                System.out.println(entry.getKey() + "-" + entry.getValue());
            }
            return dataSourceBean.getBeanName();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    
    public Map getTargetSource() throws NoSuchFieldException, IllegalAccessException {
        Field field = AbstractRoutingDataSource.class.getDeclaredField("targetDataSources");
        field.setAccessible(true);
        return (Map) field.get(this);
    }

    public Object createDataSource(DataSourceBean dataSourceBean) throws IllegalAccessException {
        //1.将applicationContext转化为ConfigurableApplicationContext
        ConfigurableApplicationContext context = (ConfigurableApplicationContext)applicationContext;
        //2.获取bean工厂并转换为DefaultListableBeanFactory
        DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory)context.getBeanFactory();
        
        BeanDefinitionBuilder beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(DruidDataSource.class);
        
        Map propertyKeyValues = getPropertyKeyValues(DataSourceBean.class, dataSourceBean);
        for (Map.Entry entry : propertyKeyValues.entrySet()) {
            beanDefinitionBuilder.addPropertyValue(entry.getKey(), entry.getValue());
        }
        //5.bean定义创建好以后，将其交给beanFactory注册成bean对象，由spring容器管理
        beanFactory.registerBeanDefinition(dataSourceBean.getBeanName(), beanDefinitionBuilder.getBeanDefinition());
        //6.最后获取步骤5生成的bean，并将其返回
        return context.getBean(dataSourceBean.getBeanName());
    }

    
    @SuppressWarnings("unused")
    private  Map getPropertyKeyValues(Class clazz, Object object) throws IllegalAccessException {
        Field[] fields = clazz.getDeclaredFields();
        Map map = new HashMap<>();
        for (Field field : fields) {
            field.setAccessible(true);
            map.put(field.getName(), field.get(object));
        }
        map.remove("beanName");
        return map;
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) {
        this.applicationContext = applicationContext;
    }
}

第五步: 初始化动态数据源，并把SqlSessionFactory与动态数据源绑定

package org.jeecg.config.dynamicdatasource;


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.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.type-aliases-package}")
    private String typeAliasesPackage;


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


    
    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(@Qualifier("master") DataSource masterDataSource ) {

        //多数据源设置
        Map targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);

        //以下数据源应该从主数据库表里面读出来，目前是直接写死在代码里
        //初始化db1数据源
        //targetDataSources.put("db1", db1Datasource);
        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();
        sqlSessionFactory.setMapperLocations(resolver.getResources(mapperLocations));  //配置mapper位置
        sqlSessionFactory.setTypeAliasesPackage(typeAliasesPackage);//配置实体类位置
        return sqlSessionFactory.getObject();
    }
}

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

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