How to connect to multiple databases (PostgreSQL) with Spring JPA

How to connect to multiple databases (PostgreSQL) with Spring JPA

I want to connect to multiple databases with JPA but with separate transaction management. For example, if I update MyDB1 and then fail when updating MyDB2, I want to commit MyDB1 without rolling back.

We are using PostgreSQL 9.6 for both databases.

Connect to multiple databases

Let the first DB connection information be primary and the other DB connection information be secondary.

The application.yml should be written as follows

spring:
  jpa:
    database: POSTGRESQL
  datasource:
    primary:
      driver-class-name: org.postgresql.Driver
      url: jdbc:postgresql://localhost:5432/mydb1 #mydb1というデータベース
      username: postgres
      password: xxxxxxxx
    secondary:
      driver-class-name: org.postgresql.Driver
      url: jdbc:postgresql://localhost:5432/mydb2 #mydb2というデータベース
      username: postgres
      password: yyyyyyyy

Define classes for connection information. To define the connection information as a single class, two classes are created.

PrimaryConfig.java

package jp.co.confrage.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
@Configuration
@EnableJpaRepositories(
  basePackages = "jp.co.confrage.repository.primary",
  entityManagerFactoryRef = "primaryEntityManager",
  transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryConfig {
  @Bean
  @Primary
  @ConfigurationProperties(prefix = "spring.datasource.primary")
  public DataSourceProperties primaryProperties() {
    return new DataSourceProperties();
  }

  @Bean
  @Primary
  @Autowired
  public DataSource primaryDataSource(@Qualifier("primaryProperties")
    DataSourceProperties properties) {
      return properties.initializeDataSourceBuilder().build();
  }

  @Bean
  @Primary
  @Autowired
  public LocalContainerEntityManagerFactoryBean primaryEntityManager(EntityManagerFactoryBuilder builder,@Qualifier("primaryDataSource") DataSource dataSource){
    return builder.dataSource(dataSource)
      .packages("jp.co.confrage.entity.primary")
      .persistenceUnit("primary")
      .build();
  }

  @Bean
  @Primary
  @Autowired
  public JpaTransactionManager primaryTransactionManager(@Qualifier("primaryEntityManager") LocalContainerEntityManagerFactoryBean primaryEntityManager) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(primaryEntityManager.getObject());
    return transactionManager;
  }
}

SecondaryConfig.java

package jp.co.confrage.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
@Configuration
@EnableJpaRepositories(
  basePackages = "jp.co.confrage.repository.secondary",
  entityManagerFactoryRef = "secondaryEntityManager",
  transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryConfig {
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.secondary")
  public DataSourceProperties secondaryProperties() {
    return new DataSourceProperties();
  }

  @Bean
  @Autowired
  public DataSource secondaryDataSource(@Qualifier("secondaryProperties") DataSourceProperties properties) {
    return properties.initializeDataSourceBuilder().build();
  }

  @Bean
  @Autowired
  public LocalContainerEntityManagerFactoryBean secondaryEntityManager(EntityManagerFactoryBuilder builder,@Qualifier("secondaryDataSource") DataSource dataSource){
    return builder.dataSource(dataSource)
      .packages("jp.co.confrage.entity.secondary")
      .persistenceUnit("secondary")
      .build();
  }

  @Bean
  @Autowired
  public JpaTransactionManager secondaryTransactionManager(@Qualifier("secondaryEntityManager") LocalContainerEntityManagerFactoryBean secondaryEntityManager) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(secondaryEntityManager.getObject());
    return transactionManager;
  }
}

This will confirm the connection to the two databases. Transaction management is separate.

At the service level with @Transactional, I get an error “Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query” error, but @Transactional at the repository level allows separate transaction management.

DataSourceProperties

@ConfigurationProperties(prefix = “spring.datasource.primary”) annotation, @ConfigurationProperties(prefix = “spring.datasource.secondary”) annotation to the method to generate DataSourceProperties. (prefix should match application.yml)

DataSource

Generate a DataSource from a DataSourceProperties instance.

LocalContainerEntityManagerFactoryBean

This class creates an EntityManager, creating an instance from the EntityManagerFactoryBuilder class provided by Spring Boot.

Specify the Entity’s package in the packages method.

Specify the EntityManager unitName in the persistenceUnit method.

JpaTransactionManager

Create a TransactionManager for an EntityManager.

Two-phase commit (2-phase commit) to manage transactions in two databases

Although the database connection was confirmed, it seems that @Transactional cannot achieve transaction management in multiple databases, and that the following library must be used.

• Atomikos

• bitronix

Bitronix seems to have little information, but is still being developed.

Atomikos is also still being developed. There are other libraries, Spring boot officially supports these two.

https://spring.pleiades.io/spring-boot/docs/current/reference/html/spring-boot-features.html

spring.pleiades.io

I would like to try 2 phase commit with PostgreSQL 9.6 using Atomikos or bitronix.

Specify more than one for basePackages

Multiple repositories can be specified in the basePackages attribute of the @EnableJpaRepositories annotation by enclosing them in {}, separated by commas.

@EnableJpaRepositories(
  basePackages = {
    "jp.co.confrage.repository.primary1",
    "jp.co.confrage.repository.primary2"
  },
  entityManagerFactoryRef = "primaryEntityManager",
  transactionManagerRef = "primaryTransactionManager"
)

高橋