SpringBoot动态切换数据源

最新推荐文章于 2025-06-23 00:12:33 发布

世事白云苍狗

最新推荐文章于 2025-06-23 00:12:33 发布

阅读量417

点赞数

CC 4.0 BY-SA版权

文章标签： java

本文链接：https://blog.youkuaiyun.com/chen19951015/article/details/128214522

本文介绍了一种在Spring Boot应用中实现动态数据源切换的方法，包括YAML配置、自定义DynamicDataSource类、注入Bean及实际切换流程。适用于需要在运行时根据业务需求选择不同数据库的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

动态切换数据源的本质就是找到容器中DynamicDataSource这个对象,然后把自己配置的数据源放进去切换使用。

一、yml文件默认参数配置

#配置数据库参数
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@172.28.144.157:1521/SHPPSDB0
    username: JR_SFISAP
    password: qaz*9ujm
    initialSize: 5
    minIdle: 5
    maxActive: 1000
    maxWait: 500
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: false
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    #filters:
    #connectionProperties:


mybatis:
  mapper-locations: mapper/*.xml

二、继承AbstractRoutingDataSource并创建 DynamicDataSource类
一定要重写这个方法determineCurrentLookupKey很重要


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


public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        String currentDb = DynamicDataSourceService.currentDb();
        if (currentDb == null) return "default";
        return currentDb;
    }
}

三、配置并注入注入DynamicDataSource这个Bean

import com.alibaba.druid.pool.DruidDataSource;
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 util.DynamicDataSource;

import java.util.HashMap;
import java.util.Map;


@Configuration
public class DruidConfig {

    @Value("${spring.datasource.type}")
    private String db_type;

    @Value("${spring.datasource.driver-class-name}")
    private String db_driver_name;

    @Value("${spring.datasource.url}")
    private String db_url;

    @Value("${spring.datasource.username}")
    private String db_user;

    @Value("${spring.datasource.password}")
    private String db_pwd;

    // 连接池初始化大小
    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    // 连接池最小值
    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    // 连接池最大值
    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    // 配置获取连接等待超时的时间
    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    // 配置一个连接在池中最小生存的时间，单位是毫秒
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    // 用来验证数据库连接的查询语句,这个查询语句必须是至少返回一条数据的SELECT语句
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    // 检测连接是否有效
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    // 申请连接时执行validationQuery检测连接是否有效。做了这个配置会降低性能。
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    // 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    // 是否缓存preparedStatement，也就是PSCache。
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    // 指定每个连接上PSCache的大小。
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    // 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
   /* @Value("${spring.datasource.filters}")
    private String filters;*/

    // 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    /*@Value("${spring.datasource.connectionProperties}")
    private String connectionProperties;*/


    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    //默认数据源配置
    public DynamicDataSource druidDataSource() {
        Map<Object, Object> map = new HashMap<>();
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        DruidDataSource defaultDataSource = new DruidDataSource();
        defaultDataSource.setDriverClassName(db_driver_name);
        defaultDataSource.setUrl(db_url);
        defaultDataSource.setUsername(db_user);
        defaultDataSource.setPassword(db_pwd);
        defaultDataSource.setInitialSize(initialSize);
        defaultDataSource.setMinIdle(minIdle);
        defaultDataSource.setMaxActive(maxActive);
        defaultDataSource.setMaxWait(maxWait);
        defaultDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        defaultDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        defaultDataSource.setValidationQuery(validationQuery);
        defaultDataSource.setTestWhileIdle(testWhileIdle);
        defaultDataSource.setTestOnBorrow(testOnBorrow);
        defaultDataSource.setTestOnReturn(testOnReturn);
        defaultDataSource.setPoolPreparedStatements(poolPreparedStatements);
        defaultDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

        defaultDataSource.setBreakAfterAcquireFailure(true);
        defaultDataSource.setConnectionErrorRetryAttempts(0);

        map.put("default", defaultDataSource);
        dynamicDataSource.setTargetDataSources(map);
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

        return dynamicDataSource;
    } 
}

四、配置数据源并进行动态切换

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import spring.Application;

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

public class DynamicDataSourceService {
    private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceService.class);
    private static final Map<Object, Object> dataSources = new HashMap<>();
    private static final ThreadLocal<String> dbKeys = new ThreadLocal<>();

    /**
     * 动态添加⼀个数据源
     *
     * @param name       数据源的key
     * @param dataSource 数据源对象
     */
    public static void addDataSource(String name, DataSource dataSource) {
        /*
        * Application是启动类,context是容器对象,DynamicDataSource对象是一定要拿到的。
        * */
        DynamicDataSource dynamicDataSource = Application.context.getBean(DynamicDataSource.class);
        dataSources.put(name, dataSource);
        dynamicDataSource.setTargetDataSources(dataSources);
        dynamicDataSource.afterPropertiesSet();
        log.info("添加了数据源:{}", name);
    }

    /**
     * 切换数据源
     */
    public static void switchDb(String dbKey) {
        dbKeys.set(dbKey);
    }

    /**
     * 重置数据源
     */
    public static void resetDb() {
        dbKeys.remove();
    }

    /**
     * 获取当前数据源
     */
    public static String currentDb() {
        return dbKeys.get();
    }


    public static void dataSourceProvider(String IP,String SID) {
        String userName = "JR_SFISAP";
        String passWord = "qaz*9ujm";
        if (SID.contains("QA")) {
            userName="TP";
            passWord="psh#tp";
        }
        /*创建动态数据源*/
        if (!dataSources.containsKey(SID)) {
            DruidDataSource dynamicDataSource = new DruidDataSource();
            dynamicDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
            dynamicDataSource.setUsername(userName);
            dynamicDataSource.setPassword(passWord);
            dynamicDataSource.setUrl("jdbc:oracle:thin:@" + IP + ":1521/" + SID);
            dynamicDataSource.setInitialSize(5);
            dynamicDataSource.setMinIdle(5);
            dynamicDataSource.setMaxActive(1000);
            dynamicDataSource.setMaxWait(500);
            dynamicDataSource.setTimeBetweenEvictionRunsMillis(60000);
            dynamicDataSource.setMinEvictableIdleTimeMillis(300000);
            dynamicDataSource.setValidationQuery("SELECT 1 FROM DUAL");
            dynamicDataSource.setTestWhileIdle(false);
            dynamicDataSource.setTestOnBorrow(false);
            dynamicDataSource.setTestOnReturn(false);
            dynamicDataSource.setPoolPreparedStatements(true);
            dynamicDataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            //失败之后不进行重试
            dynamicDataSource.setBreakAfterAcquireFailure(false);
            dynamicDataSource.setConnectionErrorRetryAttempts(0);
            addDataSource(SID, dynamicDataSource);
        }
        switchDb(SID);
    }
}