Spring Boot configure and use two data sources

Mastering datasource configurations

To ensure your multiple data sources function like a well-oiled machine, tweak specifics to avoid overlaps and achieve peak performance.

Crafting custom data source properties

For each DataSource, you can add custom properties like defining a novel naming strategy or initialization mode, or specifying SQL script paths:

spring.datasource.primary.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.datasource.secondary.initialize=true
spring.datasource.secondary.schema=classpath:secondary-schema.sql
spring.datasource.secondary.data=classpath:secondary-data.sql


Managing transactions

Handle cross-data source transactions with the mighty ChainedTransactionManager or designate a DataSourceTransactionManager for each source:

@Bean
public PlatformTransactionManager transactionManager() {
    JpaTransactionManager primaryTransactionManager = new JpaTransactionManager(primaryEntityManagerFactory().getObject());
    DataSourceTransactionManager secondaryTransactionManager = new DataSourceTransactionManager(secondaryDataSource());

    // When transactions throw tantrums, send 'em to a chain manager 🕴️
    return new ChainedTransactionManager(primaryTransactionManager, secondaryTransactionManager);
}


When maneuvering transactions across multiple data sources, don't forget to specify the transaction manager with @Transactional("transactionManagerName").

Configuring distinct entity managers

Couple different EntityManagerFactory instances with each DataSource and use the @Primary and @Qualifier annotations to specify appropriate data sources and transaction managers:

@Bean
@Primary
public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier("primaryDataSource") DataSource dataSource) {
    // Hold my JPA, I'm going in! (into the primary database, that's it 🏊‍♀️)
    return builder
            .dataSource(dataSource)
            .packages("com.example.primary")
            .persistenceUnit("primary")
            .build();
}

@Bean
public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier("secondaryDataSource") DataSource dataSource) {
    // We've gone so secondary, we're practically tertiary! 😂
    return builder
            .dataSource(dataSource)
            .packages("com.example.secondary")
            .persistenceUnit("secondary")
            .build();
}


Assorting repositories

Spring Data allows you to sort repositories by data source and entity manager:

@Configuration
@EnableJpaRepositories(
  basePackages = "com.example.primary",
  entityManagerFactoryRef = "primaryEntityManagerFactory",
  transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDataSourceConfiguration {
  // ...
}

@Configuration
@EnableJpaRepositories(
  basePackages = "com.example.secondary",
  entityManagerFactoryRef = "secondaryEntityManagerFactory",
  transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDataSourceConfiguration {
  // ...
}


Initializing databases

Get your databases initialized during development by defining DDL and DML scripts for each data source.

Managing dependencies

Your dependency configuration in pom.xml needs to be comprehensive enough to support multiple data sources. Check your transactions and concurrency control!

Push for more complex setups

For more advanced configurations, you may involve Kotlin, Spring Boot, and Hibernate. Understand and configure properly to wield their full powers.

Navigating potential project pitfalls

When juggling multiple data sources, you might stumble upon several hurdles. However, they can be surmounted by adopting proactive and preventative measures:

Taking caution with datasource configuration

Ensure you specify the properties for each data source correctly to avoid naming overlaps and to guarantee each data source has its intended properties.

Averting transaction mismanagement

Misconfigured transactions can lead to data misalignment and bugs. Clearly qualify your transactions and, for complex scenarios, consider using ChainedTransactionManager.

Circumventing lazy loading pitfalls

Lazy loading across different data sources can result in unexpected behaviors. Properly manage proxying and transactions to support the lazy loading of entities.

Ensuring authority

When accessing multiple databases, ensure security constraints or multi-tenant considerations are kept in check to keep data secure and isolated, wherever necessary.