springboot动态数据源之配置数据库

码农记事本

已于 2022-08-29 11:49:15 修改

阅读量6.2k

点赞数 2

分类专栏： java笔记本文章标签： springboot 动态数据源

于 2019-10-31 11:23:35 首次发布

本文链接：https://blog.csdn.net/leopast/article/details/92393738

版权

java笔记本专栏收录该内容

32 篇文章 2 订阅

订阅专栏

前言

前一段时间要把公司的一老项目重构，那项目的逻辑比较简单，就是从n个库里的订单、商品、退款单等数据按照一定的关系抽取到m个mysql集群商家库里，所有商家库都是一样的结构，所以项目就涉及到多数据源。

之前作的是hibernate3.2，用动态创建connection，然后用下面的试打开不用的session连接，然后操作数据：

    @Autowired
    public HibernateTemplate htpl;
  
    Session session = htpl.getSessionFactory().openSession(动态创建的connection);

现在我用的是springboot+jpa，查了很多动态数据源的资料，都是在配置文件里写死的多数据源配置，而这样的我是不需要的。为什么？因为公司里是一个商家一库，而且是不定，有时候加有时候少，关键数据量多的吓人，上百了！如果我写的配置文件里，不好维护，还和之前的库不兼容。

我找了好久资料，才找到这种方法可以将多数据源配置在表里，而且可以支持动态添加，很方便！这里记录下，给碰到和我类似问题的人一个参考！

项目结构

代码实现

application.yml

配置主库数据，这里说的主库是配置其它商家库的那个库，以后再加商家库，只要在这个库里对应的表里加一行数据就行了，如：

server:
  port: 8080

ribbon:
  ReadTimeout: 60000   #default=1000
  ConnectTimeout: 60000  #default=1000
  maxAutoRetries: 0 #调用失败的重试次数

spring:
  profiles:
    active: dev
  datasource:
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    filters: stat,wall,log4j2
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    useGlobalDataSourceStat: true
  jpa:
    database: MYSQL
    show-sql: false
    hibernate:
      ddl-auto: none #validate | update | create | create-drop
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

taobao:
    appKey: 123
    appSecret: 123
    serverUrl: https://eco.taobao.com/router/rest
    randomNumber: 123
    instanceId: 0      #多实例时从外部传入区别自动任务

---

spring:
  profiles: dev
  datasource:
    url: jdbc:mysql://192.168.20.241:3306/spider?useSSL=false
    username: 'root'
    password: '12354'
    driver-class-name: com.mysql.jdbc.Driver
eureka:
  instance:
    prefer-ip-address: true
  client:
    serviceUrl:
      defaultZone: http://eureka-dev:8761/eureka/,http://eureka-dev:8762/eureka/

DataSourceConfig

项目启动时加载配置文件里的默认主库

package com.hongware.taobaoapi.system.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @desc:
 * @author: 猪猪侠
 * @date: 2018/11/26 16:55
 */
@Configuration
public class DataSourceConfig {


    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private Integer initialSize;
    @Value("${spring.datasource.minIdle}")
    private Integer minIdle;
    @Value("${spring.datasource.maxActive}")
    private Integer maxActive;
    @Value("${spring.datasource.maxWait}")
    private Integer maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private Integer minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private Boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private Boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private Boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private Integer maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("${spring.datasource.connectionProperties}")
    private Properties connectionProperties;
    @Value("${spring.datasource.useGlobalDataSourceStat}")
    private Boolean useGlobalDataSourceStat;


    @Bean(name = "dynamicDataSource")
    public DataSource dataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();

        DruidDataSource mainDatasource = getMainDatasource();
        // 默认数据源，必须给一个，我给的是配置商家库数据连的主库
        dynamicDataSource.setDefaultTargetDataSource(mainDatasource);

        // 配置多数据源,这里是关键（商家库就是配置是这个目标数据源里，这是项目则初始化，默认给个主库）
        Map<Object, Object> dsMap = DataSourceHolder.dsMap;
        dsMap.put("main", mainDatasource);
        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    public DruidDataSource getMainDatasource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try{
            dataSource.setFilters(filters);
        }catch (SQLException sqle){
            sqle.printStackTrace();
        }
        dataSource.setConnectProperties(connectionProperties);
        dataSource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
        return dataSource;
    }

}

DataSourceHolder

切换数据源辅助类，执行service之前，调用setDataSource(String dbType)切换数据源，dbType是每个商家的唯一标识，本例中对应是的上表中的orgcode

package com.hongware.taobaoapi.system.datasource;


import com.alibaba.druid.pool.DruidDataSource;
import com.hongware.taobaoapi.system.MyCommandLineRunner;
import com.hongware.taobaoapi.system.util.SpringUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.HashMap;
import java.util.Map;

/**
 * @desc:
 * @author: 猪猪侠
 * @date: 2018/11/26 16:57
 */
public class DataSourceHolder {

    public static Map<Object, Object> dsMap = new HashMap<Object, Object>();

    //线程本地环境
    private static final ThreadLocal<String> dataSources = new ThreadLocal<String>();

    //设置数据源
    public static void setDataSource(String dbType) throws Exception{
        clearDataSource();
        if(!dsMap.containsKey(dbType)){
            MyCommandLineRunner myCommandLineRunner = (MyCommandLineRunner) SpringUtil.getBean("myCommandLineRunner");
            myCommandLineRunner.instanceDataSource(dbType);
        }
        if(!dsMap.containsKey(dbType)) throw new RuntimeException("数据源ID不存在==> " + dbType);
        dataSources.set(dbType);
    }
    //获取数据源
    public static String getDataSource() {
        return (String) dataSources.get();
    }
    //清除数据源
    public static void clearDataSource() {
        dataSources.remove();
    }

    /**
     * @desc: 验证数据源
     * @author: 猪猪侠
     * @date: 2018/12/13 下午2:35
     */
    public static boolean validateConnection(DruidDataSource druidDataSource){
        try{
            Connection con = DriverManager.getConnection(druidDataSource.getUrl() , druidDataSource.getUsername() , druidDataSource.getPassword()) ;
            con.close();
        }catch(Exception se){
            return false;
        }
        return true;
    }

}

DynamicDataSource

重写determineCurrentLookupKey方法，jpa每次查数据前就会调用此方法，我们返回给它一个数据ID就行了

package com.hongware.taobaoapi.system.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @desc:
 * @author: 猪猪侠
 * @date: 2018/11/26 16:56
 */
public class DynamicDataSource  extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        //System.out.println("切换数据源===>"+DataSourceHolder.getDataSource());
        return DataSourceHolder.getDataSource();
    }

}

MyCommandLineRunner

项目启动自动调用这个类的run方法，这里时候就可以里把数据库里的数据源全查出来了，查出来放到目标数据源中。

package com.hongware.taobaoapi.system;

import com.alibaba.druid.pool.DruidDataSource;
import com.hongware.taobaoapi.dao.OrgdbinfoDao;
import com.hongware.taobaoapi.model.po.TOrgdbinfo;
import com.hongware.taobaoapi.system.datasource.DataSourceConfig;
import com.hongware.taobaoapi.system.datasource.DataSourceHolder;
import com.hongware.taobaoapi.system.datasource.DynamicDataSource;
import com.hongware.taobaoapi.system.util.SpringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Auther: 猪猪侠
 * @Date: 2018/10/29 11:19
 * @Description:
 */
@Component
public class MyCommandLineRunner implements CommandLineRunner {

    private static final Logger logger = LoggerFactory.getLogger(MyCommandLineRunner.class);

    @Autowired
    private OrgdbinfoDao orgdbinfoDao;

    @Autowired
    private DataSourceConfig dataSourceConfig;

    @Override
    public void run(String... args) throws Exception {
        instanceDataSource();
    }

    public void instanceDataSource() {
        List<TOrgdbinfo> orgdbinfoList = orgdbinfoDao.findByIsOpen(1);
        if (orgdbinfoList != null && orgdbinfoList.size() > 0) {
            for (TOrgdbinfo orgdbinfo : orgdbinfoList) {
                String msg = "success";
                try{
                    String url = "jdbc:mysql://" + orgdbinfo.getDbIp()+ ":" + orgdbinfo.getDbPort() + "/" + orgdbinfo.getDbName() + "?useSSL=false";
                    String username = orgdbinfo.getDbUser();
                    String password = orgdbinfo.getDbPass();
                    String orgCode = orgdbinfo.getOrgCode();

                    DruidDataSource druidDataSource = dataSourceConfig.getMainDatasource();
                    druidDataSource.setUrl(url);
                    druidDataSource.setUsername(username);
                    druidDataSource.setPassword(password);
                    boolean flag = DataSourceHolder.validateConnection(druidDataSource);
                    if (flag) {
                        DataSourceHolder.dsMap.put(orgCode,druidDataSource);
                    } else {
                        msg = "fail";
                    }
                }catch (Exception e){
                    msg = "fail";
                }
                logger.info("验证数据源==>【"+orgdbinfo.getOrgCode()+":"+msg+"】");
            }
            DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringUtil.getBean("dynamicDataSource");
            dynamicDataSource.setTargetDataSources(DataSourceHolder.dsMap);
            dynamicDataSource.afterPropertiesSet();
            logger.info("初始化数据源完成==>"+DataSourceHolder.dsMap.keySet());
        }
    }

    @Transactional
    public String instanceDataSource(String orgCode) {
        TOrgdbinfo orgdbinfo = orgdbinfoDao.findByOrgCode(orgCode);
        String msg = "success";
        if (orgdbinfo != null) {
            try {
                String url = "jdbc:mysql://" + orgdbinfo.getDbIp() + ":" + orgdbinfo.getDbPort() + "/" + orgdbinfo.getDbName() + "?useSSL=false";
                String username = orgdbinfo.getDbUser();
                String password = orgdbinfo.getDbPass();

                DruidDataSource druidDataSource = dataSourceConfig.getMainDatasource();
                druidDataSource.setUrl(url);
                druidDataSource.setUsername(username);
                druidDataSource.setPassword(password);
                boolean flag = DataSourceHolder.validateConnection(druidDataSource);
                if (flag) {
                    DataSourceHolder.dsMap.put(orgCode, druidDataSource);
                } else {
                    msg = "fail";
                }
            } catch (Exception e) {
                msg = "fail";
            }
            logger.info("验证数据源==>【" + orgdbinfo.getOrgCode() + ":" + msg + "】");
            if("fail".equals(msg)){
                orgdbinfo.setIsOpen(-1);
            }
        }
        if (msg.equals("success")) {
            DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringUtil.getBean("dynamicDataSource");
            dynamicDataSource.setTargetDataSources(DataSourceHolder.dsMap);
            dynamicDataSource.afterPropertiesSet();
            logger.info("【"+orgCode+"】初始化数据源完成==>" + DataSourceHolder.dsMap.keySet());
        }
        return msg;
    }

}

使用方法如下: