How to Configure Multiple Databases in the Spring Boot Application Using Spring Data JPA

Have you ever wondered how to configure multiple data sources (databases) in the spring boot project using spring data JPA?

If yes, then you have landed in the correct place. This article will walk you through, How to configure multiple data sources in the spring boot project using spring data JPA.

In this example, I will be using a MySQL database. You can feel free to use any database. I'll pick an example of a customer and vendor. Hence, I'll create two schemas in MySQL, one for the customer and the second for the vendor.

You can find the full project source code on Github

toc

Prerequisite

---

1. MySQL is installed on your machine.

2. Java version 8 or later, I'll use Amazon Corretto JDK 11

3. Maven

Dependencies

---

 <dependencies>
   <!-- spring data jpa -->
   <dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>
   <!-- mysql connector -->
   <dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
   </dependency>
   <!-- unit test case -->
   <dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<scope>test</scope>
   </dependency>
 </dependencies>
    

The structure of the project is shown below.

---

src ├───main │ ├───java │ │ └───in │ │ └───learnjavaskills │ │ └───multipledatabaseconfigure │ │ ├───configuration │ │ ├───customer │ │ │ ├───entity │ │ │ └───repository │ │ └───vendor │ │ ├───entity │ │ └───repository │ └───resources │ │ └───test └───java └───in └───learnjavaskills └───multipledatabaseconfigure (code-box)

Entities

---

Let's start first with entities. Create two entities, i.e., customer and vendor.

Cutomer Entity

Create a customer entity class under the following package structure. Note: we will be separating customer entity and vendor entity packages.

package in.learnjavaskills.multipledatabaseconfigure.customer.entity;

		 @Entity
 @Getter
 @Setter
 @ToString
 @Table(name = "customer")
 public class Customer 
 {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "customer_id")
   private Long customerId;
     
   @Column(name = "first_name")
   private String firstName;
     
   @Column(name = "last_name")
   private String lastName;
 }
		
	

Vendor Entity

Create a vendor entity under the following package structure.

package in.learnjavaskills.multipledatabaseconfigure.vendor.entity;

		 @Entity
 @Getter
 @Setter
 @ToString
 @Table(name = "vendor")
 public class Vendor 
 {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "vendor_id")
   private Long vendorId;
	
   @Column(name = "first_name")
   private String firstName;
	
   @Column(name = "last_name")
   private String lastName;
 }
		
	

Repository

---

Now, It's time to create repository layer for both customer and vendor.

Customer Repository

Create a customer repository under the following package structure.

package in.learnjavaskills.multipledatabaseconfigure.customer.repository;

		 @Repository
 public interface CustomerRepository extends JpaRepository {}
	

Vendor Repository

Similarly, we will set up a vendor repository.

package in.learnjavaskills.multipledatabaseconfigure.vendor.repository;

		 @Repository
 public interface VendorRepository extends JpaRepository {}
	

Setting up the database

---

Now, we are ready to configure customer and vendor databases. Three important configurations need to be established, and they are as followed.

1. DataSource

2. EntityManagerFactory

3. TransactionManager

Write a class that will allow you to configure a customer database.

Create a CustomerDatabaseConfiguration class under the following package structure and annotate it with @Configuration because this class is a configuration class.

package in.learnjavaskills.multipledatabaseconfigure.configuration;

		 import org.springframework.context.annotation.Configuration;
 @Configuration
 public class CustomerDatabaseConfiguration
 {
 }
	

It's time to add the required database connection to the application.properties file and later, we will load these properties into the CustomerDatabaseConfiguration class.

		 # customer database details
 spring.datasource-customer.url = jdbc:mysql://localhost:3306/customer
 spring.datasource-customer.username = learnjavaskills.in
 spring.datasource-customer.password = learnjavaskills.in
 spring.datasource-customer.driver-class-name = com.mysql.cj.jdbc.Driver
	

DataSource

Let's configure DataSource in the CustomerDatabaseConfiguration class. First, we will load the properties using the @ConfigurationProperties("spring.datasource-customer") annotation, which we added in the application.properties file by creating the customerDataSourceProperties bean.

	 @Primary
 @Bean(name = "customerDataSourceProperties")
 @ConfigurationProperties("spring.datasource-customer")
 public DataSourceProperties customerDataSourceProperties()
 {
   return new DataSourceProperties();
 }

Here, we have marked the bean as "Primary" using the @Primary annotation because, when we configure multiple data sources, one data source must be Primary.

Here comes the DataSource configuration

	  @Primary
 @Bean(name = "customerDataSource")
 public DataSource customerDataSource()
 {
   return customerDataSourceProperties()
   	   .initializeDataSourceBuilder()
	   .type(HikariDataSource.class)
	   .build();
 }
 

EntityManagerFactory

Once the datasource configuration is completed, we can now proceed to the EntityManagerFactory configuration. Create a method inside the CustomerDatabaseConfiguration class named CustomerEntityManager.

		 @Primary
 @Bean(name = "customerEntityManagerFactory")
 public LocalContainerEntityManagerFactoryBean customerEntityManager(EntityManagerFactoryBuilder entityManagerFactoryBuilder)
 {
   return entityManagerFactoryBuilder.dataSource(customerDataSource())
	  .packages(Customer.class)
	  .build();
 }
	

Spring Boot will inject the EntityManagerFactoryBuilder bean inside our customerEntityManager method, which we created.

In this case, we used the EntityManagerFactoryBuilder class's dataSource method to pass our datasource configuration to the EntityManagerFactoryBuilder class.

Separated from this, we need to provide the package location of the customer entity to scan our entity. Remember how we made a separate package for the customer entity, i.e in.learnjavaskills.multipledatabaseconfigure.customer.entity.customer? Here, we called the packages method of the EntityManagerFactoryBuilder class and instructed it to scan the package of the customer class. Now every entity inside this package can be used for the customer database.

TransactionManager

Create a method to configure a transaction manager.

		 @Primary
 @Bean(name = "customerTransactionManager")
 public PlatformTransactionManager customerTransactionManager(@Qualifier("customerEntityManagerFactory") EntityManagerFactory customerEntityManagerFactory)
 {
   return new JpaTransactionManager(customerEntityManagerFactory);
 }
	

@EnableJpaRepositories and @EnableTransactionManagement

---

We have completed the configuration of the datasource, entityManagerFactoryBuilder, and the TransactionManager. Now, to make this configuration work, we need to enable JPA repositories. Add the following code at the class level.

 @EnableJpaRepositories(entityManagerFactoryRef = "customerEntityManagerFactory", transactionManagerRef = "customerTransactionManager", 
    basePackages = {"in.learnjavaskills.multipledatabaseconfigure.customer.repository"})

Here, we have equipped the customerEntityManagerFactory bean to the entityManagerFactoryRef parameter, the customerTransactionManager bean to the transactionManagerRef parameter, and the basePackage location of the repository to scan it.

To complete the transaction on this configuration, we will also enable transaction management. All the annotations jointly on the CustomerDatabaseConfiguration will examine like down.

 @Configuration
 @EnableTransactionManagement
 @EnableJpaRepositories(entityManagerFactoryRef = "customerEntityManagerFactory", transactionManagerRef = "customerTransactionManager", basePackages = {"in.learnjavaskills.multipledatabaseconfigure.customer.repository"})
 public class CustomerDatabaseConfiguration
 {}
 

We can do the same with the vendor datasource.

---

Applicaiton properties

 # vendor database details
 spring.datasource-vendor.url = jdbc:mysql://localhost:3306/vendor
 spring.datasource-vendor.username = learnjavaskills.in
 spring.datasource-vendor.password = learnjavaskills.in
 spring.datasource-vendor.driver-class-name = com.mysql.cj.jdbc.Driver

configuration class for vendor datasources

 package in.learnjavaskills.multipledatabaseconfigure.configuration;
 import javax.persistence.EntityManagerFactory;
 import javax.sql.DataSource;
 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;
 import org.springframework.transaction.PlatformTransactionManager;
 import org.springframework.transaction.annotation.EnableTransactionManagement;	
 import com.zaxxer.hikari.HikariDataSource;
 import in.learnjavaskills.multipledatabaseconfigure.vendor.entity.Vendor;
	
 @Configuration
 @EnableTransactionManagement
 @EnableJpaRepositories(entityManagerFactoryRef = "vendorEntityManagerFactory", transactionManagerRef = "vendorTransactionManager",
	 basePackages = {"in.learnjavaskills.multipledatabaseconfigure.vendor.repository"})
 public class VendorDatabaseConfiguration 
 {
   @Bean(name = "vendorDataSourceProperties")
   @ConfigurationProperties("spring.datasource-vendor")
   public DataSourceProperties vendorDataSourceProperties()
   {
  	 return new DataSourceProperties();
   }
		
   @Bean(name = "vendorDataSource")
   public DataSource vendorDataSource()
   {
	 return vendorDataSourceProperties()
	  .initializeDataSourceBuilder()
	  .type(HikariDataSource.class)
	  .build();
   }
		
   @Bean(name = "vendorEntityManagerFactory")
   public LocalContainerEntityManagerFactoryBean vendorEntityManagerFactory(EntityManagerFactoryBuilder entityManagerFactoryBuilder)
   {
	 return entityManagerFactoryBuilder.dataSource(vendorDataSource())
	  .packages(Vendor.class)
	  .build();
   }
		
   @Bean(name = "vendorTransactionManager")
   public PlatformTransactionManager vendorTransactionManager(@Qualifier("vendorEntityManagerFactory") EntityManagerFactory vendorEntityManagerFactory)
   {
	 return new JpaTransactionManager(vendorEntityManagerFactory);
   }
 }

Testing using Junit

---

 package in.learnjavaskills.multipledatabaseconfigure;
 import org.junit.jupiter.api.Test;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.boot.test.context.SpringBootTest;
 import in.learnjavaskills.multipledatabaseconfigure.customer.entity.Customer;
 import in.learnjavaskills.multipledatabaseconfigure.customer.repository.CustomerRepository;
 import in.learnjavaskills.multipledatabaseconfigure.vendor.entity.Vendor;
 import in.learnjavaskills.multipledatabaseconfigure.vendor.repository.VendorRepository;
	
 @SpringBootTest
 class MultipleDatabaseConfigureApplicationTests 
 {
   @Autowired
   private CustomerRepository customerRepository;
		
   @Autowired
   private VendorRepository vendorRepository;
		
   @Test
   void addCustomer()
   {
	  Customer customer = new Customer();
	  customer.setFirstName("John");
	  customer.setLastName("Henry");
	  customerRepository.save(customer);
   }
		
   @Test
   void addVendor()
   {
	  Vendor vendor = new Vendor();
	  vendor.setFirstName("Bruno");
	  vendor.setLastName("wonder");
	  vendorRepository.save(vendor);
   }
 }

Is data persisted in the customer and vendor DBs or not?

 mysql> use customer;
 Database changed
 mysql> select * from customer;
 +-------------+------------+-----------+
 | customer_id | first_name | last_name |
 +-------------+------------+-----------+
 |           1 | John       | Henry     |
 +-------------+------------+-----------+
 1 row in set (0.01 sec)

 mysql> use vendor;
 Database changed
 mysql> select * from vendor;
 +-----------+------------+-----------+
 | vendor_id | first_name | last_name |
 +-----------+------------+-----------+
 |         1 | Bruno      | wonder    |
 +-----------+------------+-----------+
 1 row in set (0.00 sec)

Conclusion

---

In this article, we have learned how to configure two MySQL databases, but you can also explore multiple databases like MySQL, PostgreSQL, SQLite, etc.

We learn how to configure DataSource, EntityManagerFactory, and the TransactionManager and perform save operations on the customer schema and the vendor schema.

I recommend you not to stop your learning here. Go ahead and explore and perform hand-on experience on this topic.

Keep learning and keep growing.