How to Gracefully Switch Data Sources Dynamically in a SpringBoot Project

1. Introduction

When a business requirement needs to read from multiple databases and write to the current one, dynamic data‑source switching is required. The built‑in dynamic-datasource-spring-boot-starter could not be used due to environment conflicts, so a custom solution based on ThreadLocal and AbstractRoutingDataSource was created.

2. Core Concepts

2.1 ThreadLocal

ThreadLocal

stores a separate value for each thread. The current thread instance is used as the key in a map attached to the thread, so values are shared only within the same thread and isolated across threads.

2.2 AbstractRoutingDataSource

AbstractRoutingDataSource

determines the target data source before each query by invoking the abstract method determineCurrentLookupKey(). The returned key is used to select a data source from a map.

3. Implementation

3.1 ThreadLocal Helper

public class DataSourceContextHolder {
    private static final ThreadLocal<String> DATASOURCE_HOLDER = new ThreadLocal<>();

    /** Set data source */
    public static void setDataSource(String dataSourceName) {
        DATASOURCE_HOLDER.set(dataSourceName);
    }

    /** Get current thread's data source */
    public static String getDataSource() {
        return DATASOURCE_HOLDER.get();
    }

    /** Remove current data source */
    public static void removeDataSource() {
        DATASOURCE_HOLDER.remove();
    }
}

3.2 DynamicDataSource extending AbstractRoutingDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {
    public DynamicDataSource(DataSource defaultDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultDataSource);
        super.setTargetDataSources(targetDataSources);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

3.3 Database Configuration (application.yml)

# data sources
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      master:
        url: jdbc:mysql://xxxxxx:3306/test1?characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave:
        url: jdbc:mysql://xxxxx:3306/test2?characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      initial-size: 15
      min-idle: 15
      max-active: 200
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: ""
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: false
      connection-properties: false

@Configuration
public class DateSourceConfig {

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

    @Bean
    @ConfigurationProperties("spring.datasource.druid.slave")
    public DataSource slaveDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource createDynamicDataSource(){
        Map<Object,Object> dataSourceMap = new HashMap<>();
        DataSource defaultDataSource = masterDataSource();
        dataSourceMap.put("master", defaultDataSource);
        dataSourceMap.put("slave", slaveDataSource());
        return new DynamicDataSource(defaultDataSource, dataSourceMap);
    }
}

3.4 Simple Test Controller

@GetMapping("/getData.do/{datasourceName}")
public String getMasterData(@PathVariable("datasourceName") String datasourceName){
    DataSourceContextHolder.setDataSource(datasourceName);
    TestUser testUser = testUserMapper.selectOne(null);
    DataSourceContextHolder.removeDataSource();
    return testUser.getUserName();
}

Two tables test_user are created in the master and slave databases, each containing a single column user_name. Inserting 'master' into the master table and 'slave' into the slave table demonstrates that passing different data‑source names yields different query results.

Exclude SpringBoot’s auto‑configured data source to avoid circular‑dependency errors.

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

4. Optimizations and Extensions

4.1 Annotation‑Driven Switching

A custom annotation @DS removes repetitive code when many services need to switch data sources.

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DS {
    String value() default "master";
}

An AOP aspect intercepts methods annotated with @DS, sets the data source before method execution, and removes it afterwards.

@Aspect
@Component
@Slf4j
public class DSAspect {

    @Pointcut("@annotation(com.jiashn.dynamic_datasource.dynamic.aop.DS)")
    public void dynamicDataSource() {}

    @Around("dynamicDataSource()")
    public Object datasourceAround(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DS ds = method.getAnnotation(DS.class);
        if (Objects.nonNull(ds)) {
            DataSourceContextHolder.setDataSource(ds.value());
        }
        try {
            return point.proceed();
        } finally {
            DataSourceContextHolder.removeDataSource();
        }
    }
}

Testing the annotation:

@GetMapping("/getMasterData.do")
public String getMasterData(){
    TestUser testUser = testUserMapper.selectOne(null);
    return testUser.getUserName();
}

@GetMapping("/getSlaveData.do")
@DS("slave")
public String getSlaveData(){
    TestUser testUser = testUserMapper.selectOne(null);
    return testUser.getUserName();
}

The default value of @DS is master, so the master endpoint does not need the annotation.

4.2 Dynamic Addition of Data Sources

To add new data sources at runtime, DynamicDataSource is extended with a mutable targetDataSourceMap and a createDataSource(List<DataSourceEntity>) method that validates connections, builds DruidDataSource instances, and updates the routing map.

public class DynamicDataSource extends AbstractRoutingDataSource {
    private final Map<Object,Object> targetDataSourceMap;

    public DynamicDataSource(DataSource defaultDataSource, Map<Object,Object> targetDataSources){
        super.setDefaultTargetDataSource(defaultDataSource);
        super.setTargetDataSources(targetDataSources);
        this.targetDataSourceMap = targetDataSources;
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }

    public void createDataSource(List<DataSourceEntity> dataSources){
        try {
            if (CollectionUtils.isNotEmpty(dataSources)) {
                for (DataSourceEntity ds : dataSources) {
                    Class.forName(ds.getDriverClassName());
                    DriverManager.getConnection(ds.getUrl(), ds.getUserName(), ds.getPassWord());
                    DruidDataSource dataSource = new DruidDataSource();
                    BeanUtils.copyProperties(ds, dataSource);
                    dataSource.setTestOnBorrow(true);
                    dataSource.setTestWhileIdle(true);
                    dataSource.setValidationQuery("select 1 ");
                    dataSource.init();
                    this.targetDataSourceMap.put(ds.getKey(), dataSource);
                }
                super.setTargetDataSources(this.targetDataSourceMap);
                super.afterPropertiesSet();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error("---program error---:{}", e.getMessage());
        }
    }

    public boolean existsDataSource(String key){
        return Objects.nonNull(this.targetDataSourceMap.get(key));
    }
}

Entity class holding connection parameters:

@Data
@Accessors(chain = true)
public class DataSourceEntity {
    private String url;
    private String userName;
    private String passWord;
    private String driverClassName;
    private String key;
}

A table test_db_info stores these parameters. A CommandLineRunner loads them at startup and registers the data sources.

@Component
public class LoadDataSourceRunner implements CommandLineRunner {
    @Resource
    private DynamicDataSource dynamicDataSource;
    @Resource
    private TestDbInfoMapper testDbInfoMapper;

    @Override
    public void run(String... args) throws Exception {
        List<TestDbInfo> testDbInfos = testDbInfoMapper.selectList(null);
        if (CollectionUtils.isNotEmpty(testDbInfos)) {
            List<DataSourceEntity> ds = new ArrayList<>();
            for (TestDbInfo testDbInfo : testDbInfos) {
                DataSourceEntity sourceEntity = new DataSourceEntity();
                BeanUtils.copyProperties(testDbInfo, sourceEntity);
                sourceEntity.setKey(testDbInfo.getName());
                ds.add(sourceEntity);
            }
            dynamicDataSource.createDataSource(ds);
        }
    }
}

After application startup, the data sources defined in the table are added to the routing map, and subsequent calls to the test endpoints retrieve data from the newly added sources.

5. Conclusion

The guide walks through implementing dynamic data‑source switching in SpringBoot: starting with a low‑level ThreadLocal approach, adding annotation‑driven AOP for concise switching, and finally enabling runtime addition of new data sources, providing a clean and extensible solution for multi‑database scenarios.