DataSource Switching in Spring Boot with ThreadLocal & AbstractRoutingDataSource

Problem Statement

In a business scenario the application must read from or write to different databases at runtime. The author first tried the dynamic-datasource-spring-boot-starter from MyBatis‑Plus, but environmental conflicts made it unusable, prompting a manual implementation.

Core Concepts

ThreadLocal

: provides a separate variable instance per thread, eliminating concurrency‑related data contamination. When a value is stored, the current thread object is used as the key in an internal Map. AbstractRoutingDataSource: determines the actual DataSource to use by invoking the abstract method determineCurrentLookupKey() before each query.

1. Implementing ThreadLocal Wrapper

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

    /** Set the current thread's datasource name */
    public static void setDataSource(String dataSourceName) {
        DATASOURCE_HOLDER.set(dataSourceName);
    }

    /** Retrieve the datasource name for the current thread */
    public static String getDataSource() {
        return DATASOURCE_HOLDER.get();
    }

    /** Remove the thread‑local entry to avoid memory leaks */
    public static void removeDataSource() {
        DATASOURCE_HOLDER.remove();
    }
}

2. Implementing the Routing DataSource

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

The constructor receives a default datasource (master) and a map of all target datasources (master, slave, etc.). The overridden method simply returns the name stored in the thread‑local holder.

3. YAML Configuration of Physical Databases

# application.yml
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

4. Spring Beans to Assemble the DynamicDataSource

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

The three beans register the master, slave, and the composite dynamic datasource in the Spring container. The @Primary annotation ensures that the dynamic datasource is injected wherever a DataSource is required.

5. Using the Routing Mechanism in Controllers

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

By setting the datasource name before the query and clearing it afterwards, the same mapper can operate against either the master or the slave database.

6. Annotation‑Based Switching (AOP)

To avoid repetitive manual calls, the author defines a custom @DS annotation and an aspect that intercepts methods annotated with it.

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

@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();
        }
    }
}

Methods annotated with @DS("slave") automatically switch to the slave datasource, while the default value master requires no explicit annotation.

7. Dynamic Addition of New Datasources at Runtime

The author introduces a DataSourceEntity POJO that holds connection parameters and a key used as the map entry identifier.

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

The enhanced DynamicDataSource now keeps a mutable targetDataSourceMap and provides a createDataSource(List<DataSourceEntity>) method that validates each entry, builds a DruidDataSource, and injects it into the 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 boolean createDataSource(List<DataSourceEntity> dataSources) {
        try {
            if (CollectionUtils.isNotEmpty(dataSources)) {
                for (DataSourceEntity ds : dataSources) {
                    // Verify connectivity
                    Class.forName(ds.getDriverClassName());
                    DriverManager.getConnection(ds.getUrl(), ds.getUserName(), ds.getPassWord());
                    // Build Druid datasource
                    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();
                return true;
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error("---program error---:{}", e.getMessage());
        }
        return false;
    }

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

A CommandLineRunner implementation reads rows from a dedicated test_db_info table, converts each row into a DataSourceEntity, and calls dynamicDataSource.createDataSource(ds) during application startup.

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

8. Testing Results

The author creates a simple table test_user(user_name varchar(255) not null) in both master and slave databases, inserts distinct values ( 'master' and 'slave'), and calls the endpoint with /getData.do/master and /getData.do/slave. Screenshots (kept as <img> tags) show that the returned usernames match the selected datasource, confirming the routing works.

Further tests using the @DS annotation on two controller methods ( getMasterData() and getSlaveData()) produce analogous results, demonstrating that both manual ThreadLocal handling and annotation‑driven switching are functionally equivalent.

Finally, after the dynamic addition step, a screenshot shows that a newly inserted datasource record is automatically available for routing, confirming the end‑to‑end capability.

Conclusion

The article demonstrates a complete, step‑by‑step construction of a dynamic datasource switching mechanism in Spring Boot. Starting from the low‑level ThreadLocal‑based routing, it evolves to an annotation‑driven approach and finally to runtime addition of arbitrary datasources, providing a reusable pattern for read/write separation, multi‑tenant architectures, and other scenarios where database routing is required.