Before diving into the implementation, it's important to understand how Spring handles transaction management. Spring abstracts transaction handling by delegating to a TransactionManager, which allows integration with different persistence frameworks through various implementations.

This guide covers the scenario where you need to configure multiple data sources in a Spring Boot application without requiring distributed transactions across them. When each data source operates independently and atomic operations are only needed within a single datasource, this approach works well.

Configuration Setup

Create a configuration class that defines beans for each datasource, its corresponding JdbcTemplate, and the transaction manager. The DataSourceTransactionManager is suitable for single-datasource transactions and can be instantiated separately for each data source.

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
public class MultiDataSourceConfiguration {

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource createPrimaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource createSecondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate createPrimaryJdbcTemplate(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate createSecondaryJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager createPrimaryTransactionManager(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager createSecondaryTransactionManager(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

The @Primary annotation marks the secondary data source as the default, which Spring will inject when no qualifier is specified. Each data source requires its own dedicated transaction manager instance.

Database Configuration

Define the connection properties in your application properties file. Spring Boot's auto-configuration will bind these values to the configuration class beans:

spring.datasource.primary.jdbc-url=jdbc:mysql://192.168.2.234:3306/db_primary?serverTimezone=Asia/Shanghai&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cacheResultSetMetadata=true&maintainTimeStats=false
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.secondary.jdbc-url=jdbc:mysql://192.168.2.234:3306/db_secondary?serverTimezone=Asia/Shanghai&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cacheResultSetMetadata=true&maintainTimeStats=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

Service Layer Usage

When using these data sources in your service layer, you must explicitly specify which transaction manager to use via the transactionManager attribute. This ensures Spring uses the correct transaction coordinator for your target datasource:

@Transactional(transactionManager = "primaryTransactionManager")
public Map<String, Object> saveUserLayout(String userId, String layoutConfig) {
    int resultCount = userDao.persistLayout(userId, layoutConfig);
    return responseMessage;
}

To verify the transcation management is functioning correctly, introduce an unhandled exception within the transaction boundayr. A simple approach is adding a division operation that throws ArithmeticException:

int i = 1 / 0;

If the transaction manager is properly configured, rolling back should occur when the exception propagates beyond the transactional method. Switch between transaction managers by changing the transactionManager attribute value to either "primaryTransactionManager" or "secondaryTransactionManager" depending on which datasource you need atomic operations on.