Distributed Transaction for multiple Databases with SpringBoot , JPA for monolithic Application.

Overview

This article demonstrates how to configure transactions across multiple databases using  @Transactional in SpringBoot with JPA. For this example we are taking 2 databases one is MySQL , and the other one is PostgresSQL.

Configurations

Before moving to configuration we have to make sure that the entity class and the repository  classes must be in different package.After that please make the changes in application.properties as shown below.

spring.mysql.datasource.jdbcUrl = jdbc:mysql://localhost:3306/testdb?createDatabaseIfNotExist=true
spring.mysql.datasource.username = root
spring.mysql.datasource.password = swarupbhol
spring.mysql.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.pgsql.datasource.jdbcUrl = jdbc:postgresql://localhost:5432/testsb?createDatabaseIfNotExist=true
spring.pgsql.datasource.username = postgres
spring.pgsql.datasource.password = swarupbhol
spring.pgsql.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql: true

Bean Configurations

We need to setup 3 configuration classes , two classes for two datasource and 1 class for ChainedTransactionManager.

1.MySqlConfig

2.PgSqlConfig

3.TransactionManagerConfig

MySQLConfig.java

package com.example.demo.config;

import jakarta.persistence.EntityManagerFactory;
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.jdbc.DataSourceBuilder;
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;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.example.demo.repository.mysql",
        entityManagerFactoryRef = "mysqlEntityManagerFactory", transactionManagerRef = "mysqlPlatformTransactionManager")
public class MySQLConfig {

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(@Qualifier("dataSource") DataSource mysqlDataSource) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        LocalContainerEntityManagerFactoryBean em
                = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(mysqlDataSource);
        em.setPackagesToScan(new String[]{"com.example.demo.model.mysql"});
        HibernateJpaVendorAdapter vendorAdapter
                = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean(name = "mysqlPlatformTransactionManager")
    public PlatformTransactionManager mysqlPlatformTransactionManager(@Qualifier("mysqlEntityManagerFactory") EntityManagerFactory mysqlEntityManagerFactory) {
        return new JpaTransactionManager(mysqlEntityManagerFactory);
    }
}

PgsqlConfig.java

package com.example.demo.config;

import jakarta.persistence.EntityManagerFactory;
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.jdbc.DataSourceBuilder;
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.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.example.demo.repository.pgsql",
        entityManagerFactoryRef = "pgsqlEntityManagerFactory", transactionManagerRef = "pgsqlPlatformTransactionManager")
public class PgsqlConfig {

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

    @Bean(name = "pgsqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean pgsqlEntityManagerFactory(@Qualifier("pgsqlDataSource") DataSource pgsqlDataSource) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        LocalContainerEntityManagerFactoryBean em
                = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(pgsqlDataSource);
        em.setPackagesToScan(new String[]{"com.example.demo.model.pgsql"});
        HibernateJpaVendorAdapter vendorAdapter
                = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean(name = "pgsqlPlatformTransactionManager")
    public PlatformTransactionManager pgsqlPlatformTransactionManager(@Qualifier("pgsqlEntityManagerFactory") EntityManagerFactory pgsqlEntityManagerFactory) {
        return new JpaTransactionManager(pgsqlEntityManagerFactory);
    }
}

TransactionManagerConfig.java

package com.example.demo.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;

@Component
public class TransactionManagerConfig {

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

As you see above in TransactionmanagerConfig.java we have used Changed transactionManager to overcome two different transactions in two separate databases.Now use

@EnableTransactionManagement to enable to the transaction in the application.
In service class use the @Transactional to maintained the transaction as bellow.

@Transactional(value = "chainedTransactionManager", rollbackFor = {Exception.class}, isolation = Isolation.READ_UNCOMMITTED)
    public String saveDetails() {
        User user = new User();
        user.setName("Jhon Doe");
        repository.save(user);
        Orders order = new Orders();
        order.setName("Oneplus 9rt");
        order.setPrice(10f);
        orderRepository.save(order);

        return "Working fine";
    }

You can find the project in github here