Spring Data JPA - working with multiple databases

Table of Contents

Multiple databases with Spring Boot and Spring Data JPA

https://github.com/explorer436/programming-playground/tree/main/java-playground/spring-data-examples/spring-data-jpa-multi-datasource-with-h2

Issues identified

The app is shutting down if we try to start it from IntelliJ. Start it from terminal using mvn spring-boot:run

After starting the application, launch h2 console using:

http://localhost:8080/h2-ui
jdbc url: jdbc:h2:mem:spring_jpa_user;
username: sa
password: sa

And look at the data in the User table.

jdbc url: jdbc:h2:mem:spring_jpa_product;
username: sa
password: sa

And look at the data in the Product table.

Steps for the configuration

We are just extending the default behavior where Spring maps these settings to an instance of org.springframework.boot.autoconfigure.jdbc.DataSourceProperties using the @ConfigurationProperties annotation:

Spring Data JPA - setup and configuration (Configure) Spring Datasource, JPA, Hibernate using application.properties)

So, to use multiple data sources, we need to declare multiple beans with different mappings within Spring’s application context.

How are they wired together?

  1. By default, Spring Boot will instantiate its default DataSource with the configuration properties prefixed by spring.datasource.*
    1. Look at this for more details: Spring Data JPA - setup and configuration (Configure) Spring Datasource, JPA, Hibernate using application.properties)
  2. Now, in addition to using the primary datasource, we want to configure a second DataSource, but with a different property namespace. This is done by using: 
    spring.datasource.jdbcUrl=jdbc:h2:mem:spring_jpa_user;NON_KEYWORDS=user;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=sa

spring.second-datasource.jdbcUrl=jdbc:h2:mem:spring_jpa_product;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;
spring.second-datasource.driverClassName=org.h2.Driver
spring.second-datasource.username=sa
spring.second-datasource.password=sa

Configuration classes

  1. In order for Spring Boot autoconfiguration to pick up the properties related to the two different databases (and instantiate two different DataSources), we need two configuration classes. The first one to work with one datasource and the second one to work with the other datasource.

  2. In each configuration class, we’ll need to define the following interfaces:

    1. DataSource
    2. EntityManagerFactory (userEntityManager)
    3. TransactionManager (userTransactionManager)
    package com.example.springbootmultipledatabasespoc.persistenceconfig;

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.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
@PropertySource({"classpath:persistence-multiple-db-boot.properties"})
@EnableJpaRepositories(
     basePackages = "com.example.springbootmultipledatabasespoc.repositories.product",
     entityManagerFactoryRef = "productEntityManager",
     transactionManagerRef = "productTransactionManager")
public class PersistenceProductAutoConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "spring.second-datasource")
    public DataSource productDataSource() {
     return DataSourceBuilder.create().build();
    }

    // productEntityManager bean
    @Bean
    public LocalContainerEntityManagerFactoryBean productEntityManager() {
     LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
     em.setDataSource(productDataSource());
     em.setPackagesToScan(new String[]{"com.example.springbootmultipledatabasespoc.entities.product"});

     HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
     em.setJpaVendorAdapter(vendorAdapter);

     HashMap<String, Object> properties = new HashMap<>();
     properties.put("hibernate.hbm2ddl.auto", "create-drop");
     properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
     em.setJpaPropertyMap(properties);

     return em;
    }

    // productTransactionManager bean
    @Bean
    public PlatformTransactionManager productTransactionManager() {
     JpaTransactionManager transactionManager = new JpaTransactionManager();
     transactionManager.setEntityManagerFactory(productEntityManager().getObject());
     return transactionManager;
    }
}

Primary beans

We are using userTransactionManager as our Primary TransactionManager by annotating the bean definition with @Primary. That’s helpful whenever we’re going to implicitly or explicitly inject the transaction manager without specifying which one by name.

Creating datasource beans using @ConfigurationProperties

We need to annotate the data source bean creation method with @ConfigurationProperties. We need to specify the corresponding config prefix.

For the first one:

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

For the second one:

@Bean
@ConfigurationProperties(prefix = "spring.second-datasource")
public DataSource productDataSource() {
    return DataSourceBuilder.create().build();
}
  1. But how do the configured properties get injected into the DataSource configuration?
  2. In this method, we’re using a DataSourceBuilder.
  3. When SpringBoot calls the build() method on the DataSourceBuilder, it’ll call its private bind() method:
public T build() {
    Class<? extends DataSource> type = getType();
    DataSource result = BeanUtils.instantiateClass(type);
    maybeGetDriverClassName();
    bind(result);
    return (T) result;
}

This private method performs much of the autoconfiguration magic, binding the resolved configuration to the actual DataSource instance:

private void bind(DataSource result) {
    ConfigurationPropertySource source = new MapConfigurationPropertySource(this.properties);
    ConfigurationPropertyNameAliases aliases = new ConfigurationPropertyNameAliases();
    aliases.addAliases("url", "jdbc-url");
    aliases.addAliases("username", "user");
    Binder binder = new Binder(source.withAliases(aliases));
    binder.bind(ConfigurationPropertyName.EMPTY, Bindable.ofInstance(result));
}

Links to this note