Managing Multiple Data Sources in Spring Boot

Applications often need to connect to more than one database. For example, you might need to separate read and write operations, or connect to different databases for different business domains. Spring Boot makes managing multiple data sources straightforward.

This guide demonstrates how to configure two distinct data sources: a primary one for `customers` and a secondary one for `orders`.

1. Configure Properties for Each Data Source

In your `application.properties` file, define connection details for each data source using unique prefixes.

# --- Primary Data Source (for Customers) ---
spring.datasource.primary.jdbc-url=jdbc:h2:mem:customersdb
spring.datasource.primary.username=sa
spring.datasource.primary.password=password
spring.datasource.primary.driver-class-name=org.h2.Driver

# --- Secondary Data Source (for Orders) ---
spring.datasource.secondary.jdbc-url=jdbc:h2:mem:ordersdb
spring.datasource.secondary.username=sa
spring.datasource.secondary.password=password
spring.datasource.secondary.driver-class-name=org.h2.Driver

2. Create a Java Configuration Class

You must manually configure the beans for each data source. Create a `@Configuration` class to define the `DataSource`, `JdbcTemplate`, and `PlatformTransactionManager` for each one.

import javax.sql.DataSource;

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;
import org.springframework.platform.transaction.PlatformTransactionManager;

@Configuration
public class DataSourceConfig {

    // --- Primary DataSource, JdbcTemplate, and Transaction Manager ---

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

    @Primary
    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(DataSource primaryDataSource) {
        return new JdbcTemplate(primaryDataSource);
    }
    
    @Primary
    @Bean(name="primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(DataSource primaryDataSource) {
        return new DataSourceTransactionManager(primaryDataSource);
    }

    // --- Secondary DataSource, JdbcTemplate, and Transaction Manager ---

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

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(DataSource secondaryDataSource) {
        return new JdbcTemplate(secondaryDataSource);
    }
    
    @Bean(name="secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(DataSource secondaryDataSource) {
        return new DataSourceTransactionManager(secondaryDataSource);
    }
}

3. Use the Data Sources in Repositories

To use a specific `JdbcTemplate`, you must use the @Qualifier annotation to specify which bean to inject. The primary bean can be autowired directly without `@Qualifier`.

Customer Repository (Using the Primary Data Source)

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class CustomerRepository {

    private final JdbcTemplate jdbcTemplate;

    // The @Primary bean is injected by default
    @Autowired
    public CustomerRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void createCustomer(String name) {
        jdbcTemplate.update("INSERT INTO customers (name) VALUES (?)", name);
    }
}

Order Repository (Using the Secondary Data Source)

Here, @Qualifier("secondaryJdbcTemplate") is required to inject the non-primary `JdbcTemplate`.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class OrderRepository {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public OrderRepository(@Qualifier("secondaryJdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int countOrders() {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM orders", Integer.class);
    }
}

Key Takeaways

• Separate Properties: Use distinct prefixes in `application.properties` for each data source.
• Explicit Bean Configuration: Create a `@Configuration` class to define all data source-related beans.
• Use `@Primary`: Designate one data source as the default to avoid injection conflicts.
• Use `@Qualifier`: When injecting a non-primary bean, use `@Qualifier` with the bean name to specify your choice.
• Transactional Integrity: Define a `PlatformTransactionManager` for each data source to manage transactions correctly. To use a specific transaction manager, you would annotate your method like so: `@Transactional("secondaryTransactionManager")`.