Elegant SpringBoot Dynamic Data Source Switching with ThreadLocal and AbstractRoutingDataSource

1. Problem

Business logic requires reading from multiple databases and writing back to the current one. The existing dynamic-datasource-spring-boot-starter could not be used due to project‑environment conflicts, so a custom solution based on ThreadLocal and AbstractRoutingDataSource is built.

2. ThreadLocal

Provides a per‑thread variable stored in a map keyed by the thread instance, ensuring isolation without explicit synchronization.

Purpose : per‑thread data sharing, cross‑thread isolation.

Mechanism : the current thread object is the key in the internal map.

3. AbstractRoutingDataSource

Routes each JDBC operation to a concrete DataSource according to a user‑defined lookup key. The method determineCurrentLookupKey() returns that key.

4. Implementation

4.1 ThreadLocal holder

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

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

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

    /** Remove the current datasource name */
    public static void removeDataSource() {
        DATASOURCE_HOLDER.remove();
    }
}

4.2 DynamicDataSource

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

4.3 Spring configuration

application.yml defines a master and a slave datasource (both Druid). The configuration class creates beans for each datasource and registers a primary DynamicDataSource bean that holds a map of the two.

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

5. Basic switch test

Two identical tables test_user(user_name varchar(255) not null) are created in the master and slave databases, each containing a single row with values 'master' and 'slave' respectively.

create table test_user (user_name varchar(255) not null comment '用户名');
insert into test_user (user_name) value ('master');
insert into test_user (user_name) value ('slave');

A controller method sets the datasource name, executes a MyBatis query, and finally clears the thread‑local value.

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

Calling the endpoint with master returns master; with slave returns slave, confirming correct routing.

Exclude SpringBoot’s auto‑configuration for data sources to avoid circular‑dependency errors.

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

6. Annotation‑based switching

6.1 @DS annotation

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

6.2 AOP aspect

@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 (ds != null) {
            DataSourceContextHolder.setDataSource(ds.value());
        }
        try {
            return point.proceed();
        } finally {
            DataSourceContextHolder.removeDataSource();
        }
    }
}

6.3 Test endpoints

@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 master endpoint inherits the default master datasource; the slave endpoint explicitly uses @DS("slave"). The returned values match the earlier manual test.

7. Dynamic addition of datasources

7.1 DataSourceEntity

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

7.2 Extended DynamicDataSource

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

    /** Add a list of datasource definitions at runtime */
    public void createDataSource(List<DataSourceEntity> dataSources) {
        try {
            if (CollectionUtils.isNotEmpty(dataSources)) {
                for (DataSourceEntity ds : dataSources) {
                    // Verify connection
                    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();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error("---程序报错---:{}", e.getMessage());
        }
    }

    /** Check whether a datasource key already exists */
    public boolean existsDataSource(String key) {
        return Objects.nonNull(this.targetDataSourceMap.get(key));
    }
}

7.3 Load datasources at 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 info : testDbInfos) {
                DataSourceEntity entity = new DataSourceEntity();
                BeanUtils.copyProperties(info, entity);
                entity.setKey(info.getName());
                ds.add(entity);
            }
            dynamicDataSource.createDataSource(ds);
        }
    }
}

The table test_db_info stores external datasource definitions (url, username, password, driver, name). After the application starts, the runner reads these rows, builds Druid datasources, and registers them in the dynamic map.

create table test_db_info (
    id int auto_increment primary key comment '主键Id',
    url varchar(255) not null comment '数据库URL',
    username varchar(255) not null comment '用户名',
    password varchar(255) not null comment '密码',
    driver_class_name varchar(255) not null comment '数据库驱动',
    name varchar(255) not null comment '数据库名称'
);

insert into test_db_info (url, username, password, driver_class_name, name) values (
    'jdbc:mysql://xxxxx:3306/test2?characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false',
    'root', '123456', 'com.mysql.cj.jdbc.Driver', 'add_slave'
);

Calling the previous test endpoint after startup shows that the newly added datasource is usable.

8. Summary

The article demonstrates a complete, production‑ready approach to dynamic datasource routing in SpringBoot: a low‑overhead ThreadLocal holder, a custom AbstractRoutingDataSource subclass, optional @DS annotation with AOP, and a runtime mechanism for adding new datasources without restarting the application.