Spring Data JPA – Multiple Databases: A Step-by-Step Tutorial

In some applications, you might need to interact with multiple databases. Spring Data JPA provides the necessary abstractions to handle this complexity seamlessly. In this tutorial, we will set up a Spring Boot 3.2 application to work with multiple databases using a Product example.

Prerequisites

• JDK 17 or later
• Maven or Gradle
• IDE (IntelliJ IDEA, Eclipse, etc.)

Step 1: Set Up a Spring Boot Project

1.1 Create a New Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

• Spring Web
• Spring Data JPA
• H2 Database (for simplicity, but you can use any databases)

Download and unzip the project, then open it in your IDE.

1.2 Configure application.properties

Set up the application properties for your project. This file is located in the src/main/resources directory. We will define two data sources and their corresponding JPA properties.

# src/main/resources/application.properties

# DataSource 1 (Primary)
spring.datasource.primary.url=jdbc:h2:mem:primarydb
spring.datasource.primary.driverClassName=org.h2.Driver
spring.datasource.primary.username=sa
spring.datasource.primary.password=password
spring.datasource.primary.platform=h2

# DataSource 2 (Secondary)
spring.datasource.secondary.url=jdbc:h2:mem:secondarydb
spring.datasource.secondary.driverClassName=org.h2.Driver
spring.datasource.secondary.username=sa
spring.datasource.secondary.password=password
spring.datasource.secondary.platform=h2

# JPA properties for both data sources
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Explanation:

• Configures two H2 in-memory databases: primarydb and secondarydb.
• Sets common JPA properties.

Step 2: Define Entity Classes

2.1 Create the Product Entity for Primary Database

Create an entity class to represent a product in the primary database.

package com.example.demo.primary.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private double price;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }
}

2.2 Create the Product Entity for Secondary Database

Create an entity class to represent a product in the secondary database.

package com.example.demo.secondary.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private double price;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }
}

Step 3: Create Repository Interfaces

3.1 Create the ProductRepository for Primary Database

Create a repository interface for the Product entity in the primary database.

package com.example.demo.primary.repository;

import com.example.demo.primary.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}

3.2 Create the ProductRepository for Secondary Database

Create a repository interface for the Product entity in the secondary database.

package com.example.demo.secondary.repository;

import com.example.demo.secondary.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}

Step 4: Configure Data Sources and Entity Managers

4.1 Create Configuration for Primary DataSource

Create a configuration class to set up the primary data source and entity manager.

package com.example.demo.config;

import com.example.demo.primary.entity.Product;
import jakarta.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.demo.primary.repository",
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create()
                .url("jdbc:h2:mem:primarydb")
                .driverClassName("org.h2.Driver")
                .username("sa")
                .password("password")
                .build();
    }

    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            @Qualifier("primaryDataSource") DataSource primaryDataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(primaryDataSource);
        em.setPackagesToScan(Product.class.getPackage().getName());
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }

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

4.2 Create Configuration for Secondary DataSource

Create a configuration class to set up the secondary data source and entity manager.

package com.example.demo.config;

import com.example.demo.secondary.entity.Product;
import jakarta.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.demo.secondary.repository",
    entityManagerFactoryRef = "secondaryEntityManagerFactory",
    transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDataSourceConfig {

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create()
                .url("jdbc:h2:mem:secondarydb")
                .driverClassName("org.h2.Driver")
                .username("sa")
                .password("password")
                .build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(secondaryDataSource);
        em.setPackagesToScan(Product.class.getPackage().getName());
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }

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

Step 5: Create Service and Controller Layers

5.1 Create the ProductService

Create a service class to handle business logic related to products for both databases.

package com.example.demo.service;

import com.example.demo.primary.entity.Product as PrimaryProduct;
import com.example.demo.primary.repository.ProductRepository as PrimaryProductRepository;
import com.example.demo.secondary.entity.Product as SecondaryProduct;
import com.example.demo.secondary.repository.ProductRepository as SecondaryProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ProductService {

    @Autowired
    private PrimaryProductRepository primaryProductRepository;

    @Autowired
    private SecondaryProductRepository secondaryProductRepository;

    public List<PrimaryProduct> getAllPrimaryProducts() {
        return primaryProductRepository.findAll();
    }

    public List<SecondaryProduct> getAllSecondaryProducts

() {
        return secondaryProductRepository.findAll();
    }

    public PrimaryProduct createPrimaryProduct(PrimaryProduct product) {
        return primaryProductRepository.save(product);
    }

    public SecondaryProduct createSecondaryProduct(SecondaryProduct product) {
        return secondaryProductRepository.save(product);
    }
}

5.2 Create the ProductController

Create a REST controller to expose endpoints for interacting with products in both databases.

package com.example.demo.controller;

import com.example.demo.primary.entity.Product as PrimaryProduct;
import com.example.demo.secondary.entity.Product as SecondaryProduct;
import com.example.demo.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/products")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping("/primary")
    public List<PrimaryProduct> getAllPrimaryProducts() {
        return productService.getAllPrimaryProducts();
    }

    @GetMapping("/secondary")
    public List<SecondaryProduct> getAllSecondaryProducts() {
        return productService.getAllSecondaryProducts();
    }

    @PostMapping("/primary")
    public PrimaryProduct createPrimaryProduct(@RequestBody PrimaryProduct product) {
        return productService.createPrimaryProduct(product);
    }

    @PostMapping("/secondary")
    public SecondaryProduct createSecondaryProduct(@RequestBody SecondaryProduct product) {
        return productService.createSecondaryProduct(product);
    }
}

Step 6: Running and Testing the Application

6.1 Run the Application

Run the Spring Boot application using your IDE or the command line:

./mvnw spring-boot:run

6.2 Test the Endpoints

Use a tool like Postman or your browser to test the endpoints.

Create a Product in the Primary Database

• URL: http://localhost:8080/products/primary
• Method: POST
• Body:

  {
      "name": "Primary Product",
      "price": 100.0
  }
  

Create a Product in the Secondary Database

• URL: http://localhost:8080/products/secondary
• Method: POST
• Body:

  {
      "name": "Secondary Product",
      "price": 200.0
  }
  

Get All Products from the Primary Database

• URL: http://localhost:8080/products/primary
• Method: GET

Get All Products from the Secondary Database

• URL: http://localhost:8080/products/secondary
• Method: GET

Conclusion

In this tutorial, you have learned how to configure and use multiple databases in a Spring Boot 3.2 application with Spring Data JPA. We covered:

• Setting up a Spring Boot project with multiple data sources.
• Defining entity classes and repositories for each database.
• Configuring data sources and entity managers.
• Creating service and controller layers.
• Running and testing the application using REST endpoints.

By following these steps, you can effectively manage and interact with multiple databases in your Spring Boot applications using Spring Data JPA.