Configure Multiple Databases in Spring Boot

Suppose there is requirement of Spring Boot application to interact with multiple databases.

Let’s we have two types of data or entities – Customer and Orders. One need to store in one type of database and other need to store in different type of database.

We need to configure mainly three spring beans for each type of entities-

DataSource
EntityManagerFactory
TransactionManager

Datasource Configuration

application.properties

# customer Database
spring.datasource.customer.url=jdbc:h2:mem:user
spring.datasource.customer.username=sa
spring.datasource.customer.password=password

# Order Database
spring.datasource.order.url=jdbc:postgresql://localhost:5432/order
spring.datasource.order.username=postgres
spring.datasource.order.password=password

JPA Configuration

Configuration beans for Customers –

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.customer.repository",
    entityManagerFactoryRef = "customerEntityManagerFactory",
    transactionManagerRef = "customerTransactionManager"
)
public class CustomerDataSourceConfig {

    @Bean(name = "customerDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.custmer")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "customerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean customerEntityManagerFactory(
            @Qualifier("customerDataSource") DataSource dataSource) {
            
        LocalContainerEntityManagerFactoryBean em 
                           = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(new String[] { "com.example.customer.entity" });
        
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }
    
    @Bean(name = "customerTransactionManager")
    public PlatformTransactionManager customerTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Configuration beans for Orders-

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.order.repository",
    entityManagerFactoryRef = "orderEntityManagerFactory",
    transactionManagerRef = "orderTransactionManager"
)
public class OrderDataSourceConfig {

    @Bean(name = "orderDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.order")
    public DataSource orderDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "orderEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean orderEntityManagerFactory(
            @Qualifier("orderDataSource") DataSource dataSource) {
            
        LocalContainerEntityManagerFactoryBean em 
                              = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(new String[] { "com.example.order.entity" });
        
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }
    
    @Bean(name = "orderTransactionManager")
    public PlatformTransactionManager orderTransactionManager(
            @Qualifier("orderEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Entities

package com.example.customer.entity;

@Entity
@Table(schema = "customers")
public class Customer{
   
   @Id
   private int id;
   
   private String name;
}

Order Entity

package com.example.order.entity;

@Entity
@Table(schema = "orders")
public class Order{

  @Id
  private int orderId;

  private String[] items; 

}

JPA Repository

Customer repository –

package com.example.customer.repository;

public interface CustomerRepository extends JpaRepository<Customer, Integer> { }

Order Repository

package com.example.order.repository;

public interface OrderRepository extends JpaRepository<Order, Integer> { }