Spring Boot Multiple Data Sources Example with Spring JPA

By Manish Fartiyal | Last Updated: January 14, 2020

This guide walks you through how to configure multiple data sources in the Spring Boot application using Spring Data JPA. Sometimes have a requirement to connect the application with multiple databases (data source) and perform the operation based on the request.

Similar Post: Spring Boot Dynamic DataSource Routing using AbstractRoutingDataSource

In this case, the flexibility of the Spring framework comes in picture.

1. What we’ll build

In this tutorial, we will create a fresh Spring Boot application, add the required dependencies, configure it with multiple data sources (databases), expose the REST endpoints and perform the operation.

For example, we have two different databases i.e. schooldb which holds the school’s information and studentdb which holds the student’s information.

1.1 API – http://localhost:8080/school fetch the school’s records from schooldb data source.

[
    {
        "id": 2,
        "name": "BHU",
        "address": "Lanka, Varanasi"
    }
]

1.2 API – http://localhost:8080/student fetch the student’s records from studentdb data source.

[
    {
        "id": 1,
        "name": "Pallavi",
        "age": 30
    },
    {
        "id": 2,
        "name": "Sunandana",
        "age": 27
    }
]

2. What we’ll need

  • About 30 minute
  • JDK 1.8 or later
  • Spring Boot 2.2.1.RELEASE
  • Gradle 4+ or Maven 3.2+
  • MySQL database
  • Your favorite IDE:
    • Spring Tool Suite (STS)
    • Eclipse
    • IntelliJ IDEA

3. Dependencies Required

Here is the pom.xml file including the required dependencies used in this project.

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.2.2.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>org.websparrow</groupId>
	<artifactId>spring-boot-multiple-datasource</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<java.version>1.8</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.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
		</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>

4. Project Structure

The final project structure of our application in STS 4 IDE will look like as follows:

Spring Boot Multiple Data Sources Example with Spring JPA

5. Configure Database Connection

The database (data source) connections strings will be configured in the application.properties file for both data sources i.e. schooldb and studentdb.

application.properties
# MySQL database connection strings for SCHOOL
school.datasource.url=jdbc:mysql://localhost:3306/schooldb?createDatabaseIfNotExist=true
school.datasource.username=root
school.datasource.password=root

# MySQL database connection strings for STUDENT
student.datasource.url=jdbc:mysql://localhost:3306/studentdb?createDatabaseIfNotExist=true
student.datasource.username=root
student.datasource.password=root

# JPA property settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true
spring.jpa.database=mysql

By default, Spring Boot will instantiate its default DataSource with the configuration properties prefixed by spring.datasource.*

6. Entities

First – let’s create two simple entities – each living in a separate database.

Student.java
package org.websparrow.entity.student;

@Entity
@Table(name = "student")
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;
	private String name;
	private int age;
	// Generate Getters and Setters...

}
School.java
package org.websparrow.entity.school;

@Entity
@Table(name = "school")
public class School {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;
	private String name;
	private String address;
	// Generate Getters and Setters...
}

As we have created two entities, now we must tell Spring which entity belongs to which data-source. And this can be configured in two ways:
1. Set schema property in @Table annotation.

@Entity
@Table(name = "student", schema = "studentdb")
public class Student {
	....
}

2. Set packages at the time of creating EntityManagerFactoryBuilder (explained in the next step).

7. Data Source Configuration

Since we have two different data sources (databases), so it will need to create two different beans for both data sources. And make sure to make one bean as a primary data source by annotating it with @Primary annotation.

7.1 Primary Data Source

// creates data-source properties bean with student database details
@Bean
@Primary
@ConfigurationProperties(prefix = "student.datasource")
public DataSourceProperties studentDataSourceProperties() {
	return new DataSourceProperties();
}

// creates data-source bean
@Bean
@Primary
public DataSource studentDataSource() {
	return studentDataSourceProperties().initializeDataSourceBuilder()
			.type(BasicDataSource.class).build();
}

If we failed to make one data source as primary, the application won’t start.

7.2 Secondary Data Source

@Bean
@ConfigurationProperties(prefix = "school.datasource")
public DataSourceProperties schoolDataSourceProperties() {
	return new DataSourceProperties();
}

@Bean
public DataSource schoolDataSource() {
	return schoolDataSourceProperties().initializeDataSourceBuilder()
			.type(BasicDataSource.class).build();
}

7.3 EntityManagerFactory Bean

Create EnttityManager bean for both the data-source. We will use EntityManagerFactory bean to obtain instances of EntityManager which interact with the JPA entities.

//creates entity manager with scanned entity classes of student database
@Bean(name = "studentEntityManager")
@Primary
public LocalContainerEntityManagerFactoryBean studentEntityManager(
		EntityManagerFactoryBuilder builder) {
	return builder.dataSource(studentDataSource()).packages(Student.class)
			.build();
}
	
//creates entity manager with scanned entity classes of school database	
@Bean(name = "schoolEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean schoolEntityManagerFactory(
		EntityManagerFactoryBuilder builder) {
	return builder.dataSource(schoolDataSource()).packages(School.class)
			.build();
}

As you can see we have passed our entities in packages(School.class) method.

7.4 Transaction Management

Now we will create TransactionManager for both data-sources, we will use @Qualifier annotation to auto-wire specific entity manager to the specific data-sources transaction manager.

// Transaction Manager for Student
@Bean(name = "studentTransactionManager")
@Primary
public PlatformTransactionManager studentTransactionManager(
		@Qualifier("studentEntityManager") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
	return new JpaTransactionManager(entityManagerFactoryBean.getObject());
}
	
// Transaction Manager for School
@Bean(name = "schoolTransactionManager")
public PlatformTransactionManager schoolTransactionManager(
		@Qualifier("schoolEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
	return new JpaTransactionManager(entityManagerFactoryBean.getObject());
}

7.5 JPA Repository Configuration

Configure our JPA repositories with @EnableJPARepositories annotation. Using this annotation we will specify below properties for each data source:

  • basePackages: This property contains all repository under data-source.
  • entityManagerFactoryRef: This property contains the bean name of the entity manager.
  • transactionManagerRef: This property contains the bean name of the transaction manager.
@EnableJpaRepositories(
		basePackages = "org.websparrow.repository.school", 
		entityManagerFactoryRef = "schoolEntityManagerFactory", 
		transactionManagerRef = "schoolTransactionManager"
		)

The final repository configuration file looks like below one. We have created different repository configuration for each of our data sources.

StudentRepositoryConfiguration.java
package org.websparrow.config;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
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.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;
import org.websparrow.entity.student.Student;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
		basePackages = "org.websparrow.repository.student", 
		entityManagerFactoryRef = "studentEntityManager",
		transactionManagerRef = "studentTransactionManager"
		)
public class StudentRepositoryConfiguration {

	// creates data-source properties bean with student database details

	@Bean
	@Primary
	@ConfigurationProperties(prefix = "student.datasource")
	public DataSourceProperties studentDataSourceProperties() {
		return new DataSourceProperties();
	}

	// creates data-source bean

	@Bean
	@Primary
	public DataSource studentDataSource() {
		return studentDataSourceProperties().initializeDataSourceBuilder()
				.type(BasicDataSource.class).build();
	}

	// creates entity manager with scanned entity classes of student database
	@Bean(name = "studentEntityManager")
	@Primary
	public LocalContainerEntityManagerFactoryBean studentEntityManager(
			EntityManagerFactoryBuilder builder) {
		return builder.dataSource(studentDataSource()).packages(Student.class)
				.build();
	}

	@Bean(name = "studentTransactionManager")
	@Primary
	public PlatformTransactionManager studentTransactionManager(
			@Qualifier("studentEntityManager") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
		return new JpaTransactionManager(entityManagerFactoryBean.getObject());
	}
}
SchoolRepositoryConfiguration.java
package org.websparrow.config;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
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 org.websparrow.entity.school.School;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
		basePackages = "org.websparrow.repository.school",
		entityManagerFactoryRef = "schoolEntityManagerFactory",
		transactionManagerRef = "schoolTransactionManager"
		)
public class SchoolRepositoryConfiguration {

	@Bean
	@ConfigurationProperties(prefix = "school.datasource")
	public DataSourceProperties schoolDataSourceProperties() {
		return new DataSourceProperties();
	}

	@Bean
	public DataSource schoolDataSource() {
		return schoolDataSourceProperties().initializeDataSourceBuilder()
				.type(BasicDataSource.class).build();
	}

	@Bean(name = "schoolEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean schoolEntityManagerFactory(
			EntityManagerFactoryBuilder builder) {
		return builder.dataSource(schoolDataSource()).packages(School.class)
				.build();
	}

	@Bean(name = "schoolTransactionManager")
	public PlatformTransactionManager schoolTransactionManager(
			@Qualifier("schoolEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
		return new JpaTransactionManager(entityManagerFactoryBean.getObject());
	}
}

8. Repository

Repository interfaces for both entities.

StudentRepository.java
package org.websparrow.repository.student;

@Repository
public interface StudentRepository
		extends JpaRepository<Student, Integer> {

}
SchoolRepository.java
package org.websparrow.repository.school;

@Repository
public interface SchoolRepository extends JpaRepository<School, Integer> {

}

9. Controller

MainController class exposes the REST endpoint for the application user. In this controller class, we have created 2 different REST endpoints as follows:

1. http://localhost:8080/school: will retrieve records from schooldb data-source.

2. http://localhost:8080/student: will retrieve records from studentdb data-source.

MainController.java
package org.websparrow.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.websparrow.entity.school.School;
import org.websparrow.entity.student.Student;
import org.websparrow.repository.school.SchoolRepository;
import org.websparrow.repository.student.StudentRepository;

@RestController
public class MainController {

	@Autowired
	private SchoolRepository schoolRepository;

	@Autowired
	private StudentRepository studentRepository;

	@GetMapping(value = "school")
	public ResponseEntity<List<School>> getSchool() {
		return ResponseEntity.status(HttpStatus.ACCEPTED)
				.body(schoolRepository.findAll());
	}

	@GetMapping(value = "student")
	public ResponseEntity<List<Student>> getStudent() {
		return ResponseEntity.status(HttpStatus.ACCEPTED)
				.body(studentRepository.findAll());
	}
}

10. Run the application

The MultipleDataSourceApplication class contains the main method and responsible to start the application.

MultipleDataSourceApplication.java
package org.websparrow;

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

@SpringBootApplication
public class MultipleDataSourceApplication {

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

11. Test the application

To test the application, start the Spring Boot application by executing the above class and hit the below API one by one:

1. http://localhost:8080/school

It will fetch the data from schooldb and return the JSON as given below:

[
    {
        "id": 1,
        "name": "RSMT",
        "address": "UP College Campus, Varanasi"
    },
    {
        "id": 2,
        "name": "BHU",
        "address": "Lanka, Varanasi"
    }
]

2. http://localhost:8080/student

It will fetch the data from studentdb and return the JSON as given below:

[
    {
        "id": 1,
        "name": "Pallavi",
        "age": 30
    },
    {
        "id": 2,
        "name": "Sunandana",
        "age": 27
    },
    {
        "id": 3,
        "name": "Kumud",
        "age": 25
    }
]

References

  1. Spring 5 @Qualifier annotation example
  2. Spring Boot RESTful CRUD Example with MySQL Database
  3. Spring Data CrudRepository interface Example

Similar Posts

About the Author

Manish Fartiyal
Hi!, I'm Manish Fartiyal, a full-stack web application developer. I love Java and other open-source technology, currently working at one of the top MNC in India. Read all published posts by Manish Fartiyal.