SpringBoot项目多数据源配置

SpringBoot项目多数据源配置

1、配置文件

1. 在配置文件中配置数据库连接的参数
2. 使用Json键值对的方式配置动态数据源参数

#配置数据源
spring:
  datasource:   
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    filters: stat,slf4j
    initialSize: 10
    maxActive: 30
    maxPoolPreparedStatementPerConnectionSize: 50
    maxWait: 60000
    minEvictableIdleTimeMillis: 300000
    minIdle: 5
    poolPreparedStatements: true
    testOnBorrow: false
    testOnReturn: false
    testWhileIdle: true
    timeBetweenEvictionRunsMillis: 60000
    validationQuery: SELECT 1 FROM DUAL
    Multiple:

 # 动态数据源配置
 dynamicDataSource: "{
  	'key1':{
  		 # Orcale数据源
      	'key2':{
              'url': 'jdbc:oracle:thin:url:port:name',
              'driverClassName': 'oracle.jdbc.driver.OracleDriver',
              'username': 'username',
              'password': 'password'
     	 },
     	 # SqlServer数据源
          'key2':{
              'url': 'jdbc:sqlserver:url:port;DatabaseName=name',
              'driverClassName': 'oracle.jdbc.driver.OracleDriver',
              'username': 'username',
              'password': 'password'
          }
    }
}"  

2、读取配置信息

从配置文件中读取数据源配置

/**
* 线程池通用配置初始化类
*/
private DruidDataSource init(String serviceType){
    DruidDataSource druidDataSource = new DruidDataSource();
    try {
        // Druid 数据库连接池具体配置  从配置文件中读取
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

        druidDataSource.setValidationQuery(validationQuery);
        // 不同数据源的Validation参数不同，可以根据Json的key来指定Validation的内容
        if (StringUtils.equals(serviceType,"ch")) {
            druidDataSource.setValidationQuery("SELECT 1");
        }
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        //配置 druid 监控sql语句
        druidDataSource.setFilters(filters);
        druidDataSource.setConnectionProperties(connectionProperties);
    } catch (SQLException e) {
        log.error("初始化连接池异常",e);
    }
    return druidDataSource;

}

从配置文件中读取多数据源配置信息

@Bean(name = "dynamicDataSource")
public DynamicDataSource dynamicDataSource() {
    DruidDataSource defaultTargetDataSource = null;
    this.dynamicDataSource = JSON.parseObject(this.dynamicDataSourceConfig);
    Map<Object, Object> targetDataSources = new HashMap<>();
    for(String regionKey : this.dynamicDataSource.keySet()){
        JSONObject regionConfig = this.dynamicDataSource.getJSONObject(regionKey);
        for(String serviceType : regionConfig.keySet()){
            // 多数据源创建DataSource 对象，并指定数据库连接池,我这里使用AliBaBa Druid 数据库连接池
            DruidDataSource druidDataSource = init(serviceType);
            try {
                JSONObject dataObj = regionConfig.getJSONObject(serviceType);
                druidDataSource.setUsername(dataObj.getString("username"));
                druidDataSource.setPassword(dataObj.getString("password"));
                druidDataSource.setUrl(dataObj.getString("url"));
                druidDataSource.setDriverClassName(regionConfig.getString("driverClassName"));
            } catch (Exception e) {
                log.error("配置主数据源异常{}",e.getLocalizedMessage(),e);
            }
            String dataSourceId = regionKey + "#" + serviceType;
            targetDataSources.put(dataSourceId, druidDataSource);
            DataSourceContextHolder.dataSourceIds.add(dataSourceId);
            if(defaultTargetDataSource == null){
                defaultTargetDataSource = druidDataSource;
            }
        }
    }
    // 设置数据源
    DynamicDataSource dataSource = new DynamicDataSource();
    // 该方法是AbstractRoutingDataSource的方法
    dataSource.setTargetDataSources(targetDataSources);
    // 默认的datasource设置为dataSourceDB1
    dataSource.setDefaultTargetDataSource(defaultTargetDataSource);
    return dataSource;
}

3、设置数据源

public class DynamicDataSource extends AbstractRoutingDataSource{
    //制定数据源使用
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

4、切换数据源

@Slf4j
public class DataSourceContextHolder {

    //存放当前线程使用的数据源类型信息
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    //存放数据源id
    public static List<String> dataSourceIds = new ArrayList<String>();

    //设置数据源ID
    public static void setDataSourceType(String dataSourceType) {
        contextHolder.set(dataSourceType);
    }
    /**
     * 动态设置数据源
     * @param regionCode 区县id
     * @param serviceType 外联服务类型
     */
    public static void setDataSourceType(String regionCode, FeignServiceType serviceType) {
        String key = regionCode + "#" + serviceType.toString();
        if(isContainsDataSource(key) == false){
            throw new BizException(405, key + "数据源不存在！");
        }
        log.debug(key);
        contextHolder.set(key);
    }

    //获取数据源
    public static String getDataSourceType() {
        log.info(contextHolder.get());
        return contextHolder.get();
    }

    //清除数据源
    public static void clearDataSourceType() {
        contextHolder.remove();
    }

    //判断当前数据源是否存在
    public static boolean isContainsDataSource(String dataSourceId) {
        return dataSourceIds.contains(dataSourceId);
    }
}

5、使用

在使用数据库连接操作前调用DataSourceContextHolder.setDataSourceType(key1,key2);