Dynamic Multi-DataSource Management and Transaction Handling in Spring

The article discusses the challenges of operating a primary database together with multiple application databases, focusing on dynamic data source management and ensuring transaction consistency across multiple data sources.

Data Source Switching Principle

By extending Spring's AbstractRoutingDataSource , dynamic switching can be achieved. The class uses targetDataSources and defaultTargetDataSource to store configured data sources, and the abstract method determineCurrentLookupKey() determines the current lookup key.

When the Spring container creates the AbstractRoutingDataSource bean, afterPropertiesSet() copies the target data sources, after which new data sources cannot be added at runtime.

Configuration File Solution

Define a DynamicDataSource class extending AbstractRoutingDataSource and override determineCurrentLookupKey() . Configure multiple data sources in application properties and inject them via a @Configuration class.

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# master datasource
spring.datasource.druid.master.url=jdbcUrl
spring.datasource.druid.master.username=***
spring.datasource.druid.master.password=***
# other datasource
spring.datasource.druid.second.url=jdbcUrl
spring.datasource.druid.second.username=***
spring.datasource.druid.second.password=***

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

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

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) {
        Map
targetDataSources = new HashMap<>(5);
        targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
        targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
        return new DynamicDataSource(firstDataSource, targetDataSources);
    }
}

AOP Handling

Use a DataSourceAspect with a custom @SwitchDataSource annotation to switch the lookup key in thread context.

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface SwitchDataSource {
    String value();
}

Database Table Solution

Store application data source definitions in a database table, load them at startup, and manage them via a custom DataSourceManager interface.

public interface DataSourceManager {
    void put(String var1, DataSource var2);
    DataSource get(String var1);
    Boolean hasDataSource(String var1);
    void remove(String var1);
    void closeDataSource(String var1);
    Collection
all();
}

Implement DynamicDataSource that reads from the table and supports runtime addition/removal of data sources.

Multi-Database Transaction Handling

The article explains Spring transaction concepts and then presents a custom transaction management approach using a ConnectionProxy that overrides commit and close to defer them, and a MultiTransaction annotation to control transaction boundaries across data sources.

public class ConnectionProxy implements Connection {
    private final Connection connection;
    public ConnectionProxy(Connection connection) { this.connection = connection; }
    @Override public void commit() throws SQLException { /* no‑op */ }
    public void realCommit() throws SQLException { connection.commit(); }
    @Override public void close() throws SQLException { /* no‑op */ }
    public void realClose() throws SQLException { if (!connection.getAutoCommit()) { connection.setAutoCommit(true); } connection.close(); }
    @Override public void rollback() throws SQLException { if (!connection.isClosed()) connection.rollback(); }
    // ... other methods omitted for brevity
}

Transaction context is stored in TransactionHolder , maintaining stacks for nested transactions and data source keys.

public class TransactionHolder {
    private boolean isOpen;
    private boolean readOnly;
    private IsolationLevel isolationLevel;
    private ConcurrentHashMap
connectionMap;
    private Stack
executeStack;
    private Stack
datasourceKeyStack;
    private String mainTransactionId;
    private AtomicInteger transCount;
    private ConcurrentHashMap
executeIdDatasourceKeyMap;
}

The MultiTransactionAop aspect orchestrates transaction start, commit, rollback, and cleanup based on the annotation.

Conclusion

The article presents a solution for dynamic multi‑data source management and transaction handling without XA, suitable for monolithic applications. For microservice architectures, distributed transaction frameworks such as Seata are recommended.