Mastering Multiple Database Connections in Spring Boot: A Complete Guide

Introduction

Modern enterprise applications often require data from multiple sources. Whether you are integrating legacy systems or implementing a micro-database pattern, Spring Boot provides robust ways to handle multiple DataSource beans.

The Strategy

Two main approaches exist for multi-database setups:

Static Configuration: Defining multiple DataSource, EntityManager, and TransactionManager beans. Best for fixed, distinct databases (e.g., Auth DB vs. Inventory DB).
Dynamic Routing: Using AbstractRoutingDataSource. Best for multi-tenancy or Read/Write splitting.

When to Use Which?

Choosing between Primary and Secondary depends on your architectural pattern:

Read/Write Splitting: Use Primary for all @Transactional write operations (INSERT/UPDATE/DELETE) and Secondary (a read-replica) for heavy SELECT queries to reduce load.
Micro-database Pattern: Use Primary for your application's core domain (e.g., Users, Orders) and Secondary for auxiliary data like audit logs or analytics that reside in a different physical database.
Legacy Integration: Use Primary for your new modern schema and Secondary as a read-only bridge to a legacy system you are migrating away from.
Data Archiving: Keep "Hot" data in Primary for performance and move "Cold" historical data to Secondary (possibly a cheaper storage engine) for compliance or reporting.

Implementation Guide

1. Configuration Properties (Spring Boot 3.4+)

Define your database properties in application.yml. Note the jdbc-url property required for DataSourceBuilder when using Spring Boot 3.


    
        
          1
          spring:
        
        
          2
            datasource:
        
        
          3
              primary:
        
        
          4
                url: jdbc:postgresql://localhost:5432/primary_db
        
        
          5
                username: user
        
        
          6
                password: password
        
        
          7
                driver-class-name: org.postgresql.Driver
        
        
          8
              secondary:
        
        
          9
                url: jdbc:mysql://localhost:3306/secondary_db
        
        
          10
                username: user
        
        
          11
                password: password
        
        
          12
                driver-class-name: com.mysql.cj.jdbc.Driver

2. DataSource Configuration (Jakarta EE)

In Spring Boot 3, namespaces moved from javax.* to jakarta.*.


    
        
          1
          @Configuration
        
        
          2
          @EnableJpaRepositories(
        
        
          3
              basePackages = "com.example.repository.primary",
        
        
          4
              entityManagerFactoryRef = "primaryEntityManagerFactory",
        
        
          5
              transactionManagerRef = "primaryTransactionManager"
        
        
          6
          )
        
        
          7
          public class PrimaryDbConfig {
        
        
          8
          
        
        
          9
              @Primary
        
        
          10
              @Bean
        
        
          11
              @ConfigurationProperties("spring.datasource.primary")
        
        
          12
              public DataSourceProperties primaryDataSourceProperties() {
        
        
          13
                  return new DataSourceProperties();
        
        
          14
              }
        
        
          15
          
        
        
          16
              @Primary
        
        
          17
              @Bean
        
        
          18
              public DataSource primaryDataSource() {
        
        
          19
                  return primaryDataSourceProperties()
        
        
          20
                      .initializeDataSourceBuilder()
        
        
          21
                      .build();
        
        
          22
              }
        
        
          23
          
        
        
          24
              // EntityManagerFactory using jakarta.persistence.EntityManager
        
        
          25
          }

3. Dynamic Routing with AbstractRoutingDataSource

For switching connections at runtime (e.g., based on a Header or Context):


    
        
          1
          public class TransactionRoutingDataSource extends AbstractRoutingDataSource {
        
        
          2
              @Override
        
        
          3
              protected Object determineCurrentLookupKey() {
        
        
          4
                  return DbContextHolder.getDbType();
        
        
          5
              }
        
        
          6
          }

Practical Usage

Once configured, you can use @Qualifier or specific repository packages to direct your data operations.

1. Using Repositories

Spring Data JPA will automatically link repositories in com.example.repository.primary to the primary database.


    
        
          1
          @Service
        
        
          2
          @RequiredArgsConstructor
        
        
          3
          public class DataService {
        
        
          4
              private final PrimaryRepository primaryRepo;
        
        
          5
              private final SecondaryRepository secondaryRepo;
        
        
          6
          
        
        
          7
              @Transactional("primaryTransactionManager")
        
        
          8
              public void syncData() {
        
        
          9
                  var data = secondaryRepo.findAll(); // Reads from Secondary
        
        
          10
                  primaryRepo.saveAll(data);          // Writes to Primary
        
        
          11
              }
        
        
          12
          }

2. Manual DataSource Selection

If using JdbcTemplate, inject the specific bean:


    
        
          1
          @Service
        
        
          2
          public class NativeQueryService {
        
        
          3
              private final JdbcTemplate primaryJdbcTemplate;
        
        
          4
          
        
        
          5
              public NativeQueryService(@Qualifier("primaryDataSource") DataSource dataSource) {
        
        
          6
                  this.primaryJdbcTemplate = new JdbcTemplate(dataSource);
        
        
          7
              }
        
        
          8
          }

Conclusion

Multiple database connections add complexity to transaction management. Always ensure your @Transactional annotations specify which manager to use if you aren't using a ChainedTransactionManager.