How to connect to multiple databases (PostgreSQL) with Spring JPA
spring boot |
---|
2.2.5.RELEASE |
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.
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.
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" )
コメント