Distributed Transaction Management for Multiple Databases with Springboot, JPA, and Hibernate

1. Overview

This tutorial will discuss the right way to configure transactions for multiple databases, how to use @Transactional, and some common pitfalls. For this tutorial, we are connecting to two databases, PgSql and DB2. In the end, we will talk about making distributed transactions using ChainedTransactionManager.

2. Configurations

Before moving on to the Configurations, make sure that your PgSql model classes are in a separate subpackage within the model package and DB2 model classes are within a subpackage within the model. As you can see, I have defined two entities Users and Orders.

Similarly your repository classes should follow the same format with UsersRepository under repository.db2 package and OrdersRepository under repository.Orders package.

3. Configure application.properties

PgSql is the primary data source here. DB2 is the secondary. This is how your application.properties file should be configured.

spring.datasource.jdbcUrl = [pgsql-url]
spring.datasource.username = [pgsql-username]
spring.datasource.password = [pgsql-password]
spring.db2.datasource.jdbcUrl = [db2-url]
spring.db2.datasource.username = [db2-username]
spring.db2.datasource.password = [db2-password]

4. Configure JPA with Java

We will need to set up 2 configuration classes, one for each database. In each of these Configurations, we need to define the following.

1. DataSource
2. EntityManagerFactory
3. PlatformTransactionManager

Here’s our PgSqlConfig.java file.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = “com.preplaced.multidbconnectionconfig.repository.pgsql”,
entityManagerFactoryRef = “pgsqlEntityManagerFactory”, transactionManagerRef = “pgsqlPlatformTransactionManager”)
public class PgSqlConfig {
@Primary
@Bean
@ConfigurationProperties(“spring.datasource”)
public DataSourceProperties pgsqlDataSourceProperties() {
    return new DataSourceProperties();
}
@Primary
@Bean
public DataSource pgsqlDataSource(@Qualifier(“pgsqlDataSourceProperties”) DataSourceProperties pgsqlDataSourceProperties) {
    return pgsqlDataSourceProperties
    .initializeDataSourceBuilder().build();
}
@Primary
@Bean
public LocalContainerEntityManagerFactoryBean pgsqlEntityManagerFactory(@Qualifier(“pgsqlDataSource”) DataSource pgsqlDataSource, EntityManagerFactoryBuilder builder) {
    return builder
    .dataSource(pgsqlDataSource)
    .packages(“com.preplaced.multidbconnectionconfig.model.pgsql”)
    .persistenceUnit(“pgsql”)
    .build();
}
@Primary
@Bean
public PlatformTransactionManager pgsqlPlatformTransactionManager(@Qualifier(“pgsqlEntityManagerFactory”) EntityManagerFactory pgsqlEntityManagerFactory) {
    return new JpaTransactionManager(pgsqlEntityManagerFactory);
}
}

Db2Config.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = “com.preplaced.multidbconnectionconfig.repository.db2”,
entityManagerFactoryRef = “db2EntityManagerFactory”, transactionManagerRef = “db2PlatformTransactionManager”)
public class Db2Config {
@Bean
@ConfigurationProperties(“spring.db2.datasource”)
public DataSourceProperties db2DataSourceProperties() {
    return new DataSourceProperties();
}
@Bean
public DataSource db2DataSource(@Qualifier(“db2DataSourceProperties”) DataSourceProperties db2DataSourceProperties) {
    return  db2DataSourceProperties
    .initializeDataSourceBuilder().build();
}
@Bean
public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(@Qualifier(“db2DataSource”) DataSource db2DataSource, EntityManagerFactoryBuilder builder) {
    return builder
    .dataSource(db2DataSource) .                        .packages(“com.preplaced.multidbconnectionconfig.model.db2”)
    .persistenceUnit(“db2”)
    .build();
}
@Bean
public PlatformTransactionManager db2PlatformTransactionManager(EntityManagerFactory db2EntityManagerFactory) {
    return new JpaTransactionManager(db2EntityManagerFactory);
}
}

5. Distributed Transactions

Now that we have configured our application with our databases, there might arise a scenario where we need to perform transactions on both these databases simultaneously and If one database operation fails, we would want to roll back both the DB operations. For example, let’s say we get an order from a new user and we want to insert it into the Users and Orders tables simultaneously. If we were using a single database, “@Transactional” would have sufficed. But using this annotation for distributed transactions will fail because now there are two different transaction managers, pgsqlPlatformTransactionManager and db2PlatformTransactionManager respectively. To overcome this, we need to use ChainedTransactionManager.

Create a new class called TransactionManagerConfig.java

@Bean(name = "chainedTransactionManager")
public ChainedTransactionManager transactionManager (
@Qualifier("pgsqlPlatformTransactionManager")           PlatformTransactionManager pgsqlTransactionManager,
@Qualifier("db2PlatformTransactionManager")
PlatformTransactionManager db2TransactionManager) {
    return new ChainedTransactionManager(db2TransactionManager,
           pgsqlTransactionManager);
}
}

Now in our persistence service class, we can invoke the chainedTransactionManager like this.

@Transactional (value = "chainedTransactionManager")
void insertIntoMultipleDBs(){
    db2repo.insert(...);
    pgsqlrepo.insert(...);
}

6. Conclusion

This article gave an overview of configuring springboot application to connect to multiple databases and performing distributed transactions using ChainedTransactionManager.

If you’re looking for a job change, check out Preplaced website for a FREE trial and assessment from industry professionals.

This blog post is contributed by Shamik Mukhopadhyay, Software Engineer at Target.

Preparing for an interview?

Prepare better with a mentor-led 1:1 interview prep program. Learn from professionals working in top-tier companies! Check out these mentor profiles and book your free trial session today.