How to connect multiple databases using Spring Boot

Spring boot allows you to connect to multiple databases by configuring multiple data sources in a single spring boot application using hibernate and JPA. Spring boot enables repositories to connect to multiple databases using JPA from a single application. Multiple data source configurations allow multiple database connections to be established in a spring boot application. With application properties file configurations, spring boot makes it very easy to use multiple databases in a single spring boot application.

Each database connection should have its own DataSource, EntityManagerFactory, and PlatformTransactionManager. To connect multiple databases, each database should be configured in its own spring boot configuration file. Multiple data sources should be configured for multiple databases.

For spring data classes and spring data JPA repositories, two separate java packages need be created. Application properties files should be used to configure database credentials. The JPA repositories included in various Java packages will connect to the appropriate database and execute database queries automatically.

Database SQL Query

There are two different databases created to configure multiple databases in a single spring boot application. The first database will contain employee table. The table contains three columns id, name and salary. The second database contains manager table. The manager table contains three columns id, name and salary. Three rows are inserted in each tables.

Database – testdb1

create table employee (
id int primary key,
name varchar(100),
salary int
);

insert into employee values (1,'name01',1000);
insert into employee values (2,'name02',2000);
insert into employee values (3,'name03',3000);

Database – testdb2

create table manager (
id int primary key,
name varchar(100),
salary int
);

insert into manager values (1,'man01',1000);
insert into manager values (2,'man02',2000);
insert into manager values (3,'man03',3000);

Application properties details

The application properties will be placed in the resources folder’s application.properties files. The application.properties now has two database configurations. The hibernate JPA related configurations also added in the application.properties file.

application.properties

spring.datasource.jdbc-url=jdbc:mysql://localhost/testdb1
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.seconddatasource.jdbc-url=jdbc:mysql://localhost/testdb2
spring.seconddatasource.username=root
spring.seconddatasource.password=root
spring.seconddatasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.database=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Data Model class

Two packages are created for two database related configuration classes. The packages are com.yawintutor.primary and com.yawintutor.secondary. The employee table related configurations are configured in the primary package. The manager table related configurations are configured in the secondary package.

com.yawintutor.primary.Employee.java

package com.yawintutor.primary;

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

@Entity
public class Employee {
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Id
	private int id;
	private String name;
	private int salary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}	
}

com.yawintutor.secondary.Manager.java

package com.yawintutor.secondary;

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

@Entity
public class Manager {
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Id
	private int id;
	private String name;
	private int salary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}	
}

JPA Repository

The employee jpa repository is created to access the employee table from the primary database. The manager jpa repository is used to connect the manager table from the secondary database.

com.yawintutor.primary.EmployeeRepository.java

package com.yawintutor.primary;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

com.yawintutor.secondary.ManagerRepository.java

package com.yawintutor.secondary;

import org.springframework.data.jpa.repository.JpaRepository;

public interface ManagerRepository extends JpaRepository<Manager, Long> {

}

Database Configurations

The multiple database configurations should be created in different spring boot configuration java classes. Two database configuration classes are created to connect two database. The PrimaryDBConfig java class contains the testdb1 database configurations such as data source, entity manager factory and transaction manager. The SecondaryDBConfig java class contains the testdb2 database configurations.

com.yawintutor.PrimaryDBConfig.java

package com.yawintutor;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "primaryEntityManagerFactory",
  transactionManagerRef = "primaryTransactionManager",
  basePackages = { "com.yawintutor.primary" }
)
public class PrimaryDBConfig {
	
	@Bean(name="primaryDataSource")
	@Primary
	@ConfigurationProperties(prefix="spring.datasource")
	public DataSource primaryDataSource() {
	    return DataSourceBuilder.create().build();
	}

	@Primary
	@Bean(name = "primaryEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("primaryDataSource") DataSource primaryDataSource) {
		return builder
				.dataSource(primaryDataSource)
				.packages("com.yawintutor.primary")
				.build();
	}

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

com.yawintutor.SecondaryDBConfig.java

package com.yawintutor;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
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.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;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "secondaryEntityManagerFactory",
  transactionManagerRef = "secondaryTransactionManager",
  basePackages = { "com.yawintutor.secondary" }
)
public class SecondaryDBConfig {
	
	@Bean(name="secondaryDataSource")
	@ConfigurationProperties(prefix="spring.seconddatasource")
	public DataSource secondaryDataSource() {
	    return DataSourceBuilder.create().build();
	}

	@Bean(name = "secondaryEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
		return builder
				.dataSource(secondaryDataSource)
				.packages("com.yawintutor.secondary")
				.build();
	}

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

Spring Boot Rest Controller class

A rest controller class is created to access the tables from multiple databases using multiple data sources. The rest controller class will have two apis /primary and /secondary to access employee and manager table data. The rest controller class is as below.

TestController.java

package com.yawintutor;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.yawintutor.primary.Employee;
import com.yawintutor.primary.EmployeeRepository;
import com.yawintutor.secondary.Manager;
import com.yawintutor.secondary.ManagerRepository;

@RestController
public class TestController {
	
	@Autowired
	EmployeeRepository employeeRepository;

	@Autowired
	ManagerRepository managerRepository;

	@RequestMapping(value="/primary", method=RequestMethod.GET)
	public List<Employee> getPrimaryDatabaseData() {
		List<Employee> list = employeeRepository.findAll();
		return list;
	}
	
	@RequestMapping(value="/secondary", method=RequestMethod.GET)
	public List<Manager> getSecondaryDatabaseData() {
		List<Manager> list = managerRepository.findAll();
		return list;
	}
}

Spring boot Batch Main Class

The main class for the spring boot multiple database connection will be shown as below. The default implementation will be used for the main class. The main method in this file will be executed when the spring boot application starts.

SpringBootMultipleDataBasesApplication.java

package com.yawintutor;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootMultipleDataBasesApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootMultipleDataBasesApplication.class, args);
	}
}

Application configuration using pom.xml file

The required application configurations and dependencies are created using maven pom.xml file. The Maven POM.xml file contains dependencies for spring boot web, JPA Repository and mysql database. If you are using some other database other than MySQL Database, you need to add the dependency to the respective database in the pom.xml file.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.yawintutor</groupId>
	<artifactId>SpringBootMultipleDataBases</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringBootMultipleDataBases</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

How to run

When the spring boot application starts, the application will start tomcat and listen the port 8080. The below url will execute and get data from different database.

http://localhost:8080/primary

[{"id":1,"name":"name01","salary":1000},{"id":2,"name":"name02","salary":2000},{"id":3,"name":"name03","salary":3000}]

http://localhost:8080/secondary

[{"id":1,"name":"man01","salary":1000},{"id":2,"name":"man02","salary":2000},{"id":3,"name":"man03","salary":3000}]