Using Multiple Databases with Spring Data JPA in Spring Boot

This tutorial will guide you through the process of using multiple databases with Spring Data JPA in a Spring Boot application. Whether you're dealing with different types of data or optimizing performance, leveraging multiple databases can be a powerful solution.

The guide covers the use of multiple databases, including SQL Server, MySQL, Oracle, and PostgreSQL, within a single Spring Boot project.

Follow the steps below to complete this tutorial:

Step 1: Add Dependencies

Start by adding the necessary database driver dependencies for SQL Server, MySQL, Oracle, and PostgreSQL. Customize these additions based on your specific project needs.

<!--For SQLServer-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

<!--For MySQL-->	
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

<!--For Oracle-->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <scope>runtime</scope>
</dependency>
		
<!--For PostgreSQL-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!--For Spring JPA-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
	
<!--For Lombok-->	
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

//For SQLServer
runtimeOnly 'com.microsoft.sqlserver:mssql-jdbc'

//For MySQL
runtimeOnly 'com.mysql:mysql-connector-j'

//For Oracle
runtimeOnly 'com.oracle.database.jdbc:ojdbc8'

//For PostgreSQL
runtimeOnly 'org.postgresql:postgresql'

//For Spring JPA
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

//For Lombok
annotationProcessor 'org.projectlombok:lombok'

Step 2: Add Database Configurations

Define database connection properties such as URL, username, password, and driver class for each database in your application.properties or application.yml file. For example:

server.port=8080

# MySQL Configuration database 1
spring.datasource.mysql.jdbc-url=jdbc:mysql://localhost:3306/my_mysql_db1
spring.datasource.mysql.username=root
spring.datasource.mysql.password=Testing123$
spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver

# MySQL Configuration database 2
spring.second-datasource.mysql.jdbc-url=jdbc:mysql://localhost:3306/my_mysql_db2
spring.second-datasource.mysql.username=root
spring.second-datasource.mysql.password=Testing123$
spring.second-datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver

# PostgreSQL Configuration
spring.datasource.postgresql.jdbc-url=jdbc:postgresql://localhost:5432/your_database_name
spring.datasource.postgresql.username=your_username
spring.datasource.postgresql.password=your_password
spring.datasource.postgresql.driver-class-name=org.postgresql.Driver

# SqlServer Configuration
spring.datasource.sqlserver.jdbc-url=jdbc:sqlserver://localhost:1433;databaseName=your_database_name;encrypt=true;trustServerCertificate=true;integratedSecurity=false;
spring.datasource.sqlserver.username=your_user_name
spring.datasource.sqlserver.password=your_password
spring.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

# Oracle Configuration
spring.datasource.oracle.jdbc-url=jdbc:oracle:thin:@localhost:1521/your_oracle_SID
spring.datasource.oracle.username=your_username
spring.datasource.oracle.password=your_password
spring.datasource.oracle.driver-class-name=oracle.jdbc.OracleDriver


spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

Step 3: Define Data Source Configurations

You will need to configure data sources for each database in your application. Spring Boot provides a way to define multiple data source beans. Create a class named DataSourceConfig to define data source beans for each database (We must define at least one data source as a primary by annotating with @primary):

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class DataSourceConfig {

  
  // Datasource bean for MySQL database 1
  @Primary
  @Bean
  @ConfigurationProperties("spring.datasource.mysql")
  public DataSource mysqlDb1DataSource() {
    return DataSourceBuilder.create().build();
  }

  // Datasource bean for MySQL database 2
  @Bean
  @ConfigurationProperties("spring.second-datasource.mysql")
  public DataSource mysqlDb2DataSource() {
    return DataSourceBuilder.create().build();
  }

  // Datasource bean for Oracle
  @Bean
  @ConfigurationProperties("spring.datasource.oracle")
  public DataSource oracleDb1DataSource() {
    return DataSourceBuilder.create().build();
  }

  // Datasource bean for PostgreSQL database 1
  @Bean
  @ConfigurationProperties("spring.datasource.postgresql")
  public DataSource postgresqlDb1DataSource() {
    return DataSourceBuilder.create().build();
  }
  

  // Datasource bean for SQLServer
  @Bean
  @ConfigurationProperties("spring.datasource.sqlserver")
  public DataSource sqlserverDb1DataSource() {
    return DataSourceBuilder.create().build();
  }

}

Step 4:  Configure EntityManagerFactory and TransactionManager

Create separate classes to configure the EntityManagerFactory and TransactionManager for each database.

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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.transaction.PlatformTransactionManager;
import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.app.product.repository",
    entityManagerFactoryRef = "mysqlDb1EntityManagerFactory",
    transactionManagerRef = "mysqlDb1TransactionManager")
public class MySqlDb1Config {

  @Primary
  @Bean(name = "mysqlDb1EntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean mysqlDb1EntityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("mysqlDb1DataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages(new String[] {"com.example.app.product.entity"})
        .persistenceUnit("mysql").build();
  }

  @Primary
  @Bean(name = "mysqlDb1TransactionManager")
  public PlatformTransactionManager mysqlDb1TransactionManager(
      @Qualifier("mysqlDb1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

}

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import org.springframework.transaction.PlatformTransactionManager;
import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.app.customer.repository",
    entityManagerFactoryRef = "mysqlDb2EntityManagerFactory",
    transactionManagerRef = "mysqlDb2TransactionManager")
public class MySqlDb2Config {

  @Bean(name = "mysqlDb2EntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean mysqlDb2EntityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("mysqlDb2DataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages(new String[] {"com.example.app.customer.entity"})
        .persistenceUnit("mysql").build();
  }

  @Bean(name = "mysqlDb2TransactionManager")
  public PlatformTransactionManager mysqlDb2TransactionManager(
      @Qualifier("mysqlDb2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

}

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import org.springframework.transaction.PlatformTransactionManager;
import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.app.transaction.repository",
    entityManagerFactoryRef = "postgresqlDb1EntityManagerFactory",
    transactionManagerRef = "postgresqlDb1TransactionManager")
public class PostgresqlDb1Config {

  @Bean(name = "postgresqlDb1EntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean postgresqlDb1EntityManagerFactory(
      EntityManagerFactoryBuilder builder,
      @Qualifier("postgresqlDb1DataSource") DataSource dataSource) {
    return builder.dataSource(dataSource)
        .packages(new String[] {"com.example.app.transaction.entity"}).persistenceUnit("postgres")
        .build();
  }

  @Bean(name = "postgresqlDb1TransactionManager")
  public PlatformTransactionManager postgresqlDb1TransactionManager(
      @Qualifier("postgresqlDb1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

}

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import org.springframework.transaction.PlatformTransactionManager;
import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.app.employee.repository",
    entityManagerFactoryRef = "sqlserverDb1EntityManagerFactory",
    transactionManagerRef = "sqlserverDb1TransactionManager")
public class SqlserverDb1Config {

  @Bean(name = "sqlserverDb1EntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean sqlserverDb1EntityManagerFactory(
      EntityManagerFactoryBuilder builder,
      @Qualifier("sqlserverDb1DataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages(new String[] {"com.example.app.employee.entity"})
        .build();
  }

  @Bean(name = "sqlserverDb1TransactionManager")
  public PlatformTransactionManager sqlserverDb1TransactionManager(
      @Qualifier("sqlserverDb1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

}

package com.example.app.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import org.springframework.transaction.PlatformTransactionManager;
import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.app.student.repository",
    entityManagerFactoryRef = "oracleDb1EntityManagerFactory",
    transactionManagerRef = "oracleDb1TransactionManager")
public class OracleDb1Config {

  @Bean(name = "oracleDb1EntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean oracleDb1EntityManagerFactory(
      EntityManagerFactoryBuilder builder,
      @Qualifier("oracleDb1DataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages(new String[] {"com.example.app.student.entity"})
        .build();
  }

  @Bean(name = "oracleDb1TransactionManager")
  public PlatformTransactionManager oracleDb1TransactionManager(
      @Qualifier("oracleDb1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

}

Step 5: Create Entity Classes

Create separate entity classes for each database, annotating them with @Entity and defining their relationships. Make sure the entities correspond to the tables in their respective databases. For example:

package com.example.app.product.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "product")
public class Product {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "name")
  private String name;

  @Column(name = "price")
  private double price;
  
  @Column(name = "deleted")
  private boolean deleted;

  @Column(name = "created_by")
  private Date createdBy;

  @Column(name = "modified_by")
  private Date modifiedBy;

  @CreatedDate
  @Column(name = "created_date")
  private Date createdDate;

  @LastModifiedDate
  @Column(name = "modified_date")
  private Date modifiedDate;

}

package com.example.app.customer.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "customer")
public class Customer {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  
  @Column(name = "first_name")
  private String firstName;
  
  @Column(name = "last_name")
  private String lastName;
  
  @Column(name = "email")
  private String email;
  
  @Column(name = "deleted")
  private boolean deleted;
  
  @Column(name = "created_by")
  private Date createdBy;

  @Column(name = "modified_by")
  private Date modifiedBy;

  @CreatedDate
  @Column(name = "created_date")
  private Date createdDate;

  @LastModifiedDate
  @Column(name = "modified_date")
  private Date modifiedDate;
}

package com.example.app.transaction.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "transaction")
public class Transaction {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "sender_id")
  private String senderId;

  @Column(name = "receiver_id")
  private String receiverId;

  @Column(name = "amount")
  private double amount;

  @Column(name = "fee")
  private double fee;

  @Column(name = "status")
  private int status;

  @Column(name = "created_by")
  private Date createdBy;

  @Column(name = "modified_by")
  private Date modifiedBy;

  @CreatedDate
  @Column(name = "created_date")
  private Date createdDate;

  @LastModifiedDate
  @Column(name = "modified_date")
  private Date modifiedDate;

}

package com.example.app.employee.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "employee")
public class Employee {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "first_name")
  private String firstName;

  @Column(name = "last_name")
  private String lastName;

  @Column(name = "email")
  private String email;
  
  @Column(name = "deleted")
  private boolean deleted;

  @Column(name = "created_by")
  private Date createdBy;

  @Column(name = "modified_by")
  private Date modifiedBy;

  @CreatedDate
  @Column(name = "created_date")
  private Date createdDate;

  @LastModifiedDate
  @Column(name = "modified_date")
  private Date modifiedDate;
  
}

package com.example.app.student.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.SequenceGenerator;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "student")
public class Student {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "student_seq_gen")
  @SequenceGenerator(name = "student_seq_gen", sequenceName = "student_seq", allocationSize = 1)
  private Long id;

  @Column(name = "first_name")
  private String firstName;

  @Column(name = "last_name")
  private String lastName;

  @Column(name = "email")
  private String email;

  @Column(name = "deleted")
  private boolean deleted;

  @Column(name = "created_by")
  private Date createdBy;

  @Column(name = "modified_by")
  private Date modifiedBy;

  @CreatedDate
  @Column(name = "created_date")
  private Date createdDate;

  @LastModifiedDate
  @Column(name = "modified_date")
  private Date modifiedDate;

}

Step 6: Create Repositories

Create separate repository interfaces for each data source:

package com.example.app.product.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.app.product.entity.Product;

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

}

package com.example.app.customer.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.app.customer.entity.Customer;

@Repository
public interface CustomerRepository extends CrudRepository<Customer, Long> {

}

package com.example.app.transaction.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.app.transaction.entity.Transaction;

@Repository
public interface TransactionRepository extends CrudRepository<Transaction, Long> {

}

package com.example.app.employee.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.app.employee.entity.Employee;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee, Long> {

}

package com.example.app.student.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.app.student.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Long> {

}