Configuring Multiple Data Sources with Spring Boot – With Example

Configuring Multiple Data Sources with Spring Boot

Spring Boot is a popular framework for building web applications that require database access. In many real-world scenarios, it is common to have multiple databases in an application. This is where Spring Boot’s flexibility and ease of configuration comes in handy. In this blog, we will explore how to configure and manage multiple data sources in Spring Boot.

We will cover how to configure multiple data sources, including configuring properties, defining beans, and using annotations.

By the end of this blog, you will have a solid understanding of how to configure and manage multiple data sources in Spring Boot, giving you the flexibility and power to build robust and scalable database-driven applications

Advantages of Configuring Multiple Data Sources in Spring Boot Application:

• Improved scalability: By configuring multiple data sources, you can distribute the load across multiple databases, which can help improve the scalability of your application.

• Improved performance: By using multiple data sources, you can improve the performance of your application by reducing the load on a single database and by distributing the load across multiple databases.

• Improved fault tolerance: If one of your databases goes down, having multiple data sources can help ensure that your application remains available and can continue to operate using the other available data sources.

• Improved security: By using multiple data sources, you can improve the security of your application by segregating sensitive data into separate databases.

• Support for multiple data types: With multiple data sources, you can use different types of databases to store different types of data. For example, you can use a NoSQL database for storing unstructured data and a relational database for storing structured data.

• Flexibility: Configuring multiple data sources with Spring Boot gives you more flexibility in terms of how you manage your data. You can choose to use different databases for different purposes or use multiple databases to support different parts of your application.

Disadvantages of Configuring Multiple Data Sources in Spring Boot Application:

• Increased complexity: Configuring and managing multiple data sources can be more complex than using a single data source, which may require additional development and maintenance efforts.

• Increased resource usage: Using multiple data sources can increase the resource usage of your application, including memory and CPU usage, which may have an impact on performance.

• Potential data inconsistencies: If data is stored in multiple databases, there is a risk of data inconsistencies between the databases, which can be difficult to identify and resolve.

• Potential performance issues: While using multiple data sources can improve performance, it can also lead to additional network latency and increased data transfer between databases, which may impact overall application performance.

• Additional maintenance: Managing multiple data sources may require additional maintenance efforts, including backup and recovery, software updates, and database management tasks.

So opting for multiple data sources in a single Spring Boot application totally depends on the requirements of the project.

Project Structure:

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>3.0.2</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.javainfinite</groupId>
	<artifactId>multipledatabases</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>multipledatabases</name>
	<description>Connect with Multiple Databases</description>
	<properties>
		<java.version>17</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>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

application.properties

first.datasource.jdbc-url=jdbc:mysql://localhost:3306/employee
first.datasource.username=username
first.datasource.password=password

second.datasource.jdbc-url=jdbc:mysql://localhost:3306/manager
second.datasource.username=username
second.datasource.password=password

spring.jpa.hibernate.ddl-auto=create-drop

FirstDatabaseConfiguration.java

package com.javainfinite.multipledatabases.employee.configuration;

import jakarta.persistence.EntityManagerFactory;
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 javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "employeeEntityManager",
        transactionManagerRef = "employeeTransactionManager",
        basePackages = {"com.javainfinite.multipledatabases.employee.repository"}
)
public class FirstDatabaseConfiguration {

    @Primary
    @Bean(name = "employeedb")
    @ConfigurationProperties(prefix = "first.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "employeeEntityManager")
    public LocalContainerEntityManagerFactoryBean employeeManager(EntityManagerFactoryBuilder builder,
                                                                  @Qualifier("employeedb") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.javainfinite.multipledatabases.employee")
                .build();
    }

    @Primary
    @Bean(name = "employeeTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("employeeEntityManager") EntityManagerFactory employeeEntityManagerFactory) {

        return new JpaTransactionManager(employeeEntityManagerFactory);
    }

}

LocalContainerEntityManagerFactoryBean:

LocalContainerEntityManagerFactoryBean is a class in the Spring Framework that is used to configure and create a JPA EntityManagerFactory in a Spring Boot application.

The JPA EntityManagerFactory is responsible for managing the persistence context and creating EntityManager instances, which are used to interact with the database in a JPA-based application.

LocalContainerEntityManagerFactoryBean provides a way to configure the JPA EntityManagerFactory using Spring Boot’s autoconfiguration mechanism. It takes care of setting up the necessary components, such as the JPA vendor adapter, transaction manager, and data source, based on the application’s configuration.

PlatformTransactionManager:

PlatformTransactionManager is an interface in the Spring Framework that provides a common abstraction for transaction management in a Spring-based application.

Transaction management is the process of ensuring that a group of related database operations are treated as a single, atomic unit of work, either all succeeding or all failing, so that the data remains consistent and accurate. In a Spring application, transactions can be managed using various strategies, such as JDBC, JPA, Hibernate, and JTA.

Why do we need Separate LocalContainerEntityManagerFactoryBean and PlatformTransactionManager?

An EntityManagerFactory represents a persistence unit, which is a set of entity classes and other configuration information needed to interact with a database. Each data source requires its own EntityManagerFactory because they may have different configurations, such as different database URLs, user credentials, and so on.

PlatformTransactionManager is responsible for managing Transactions for a specific datasource. A transaction manager is used to ensure that multiple database operations are performed as a single, atomic unit of work. Each data source requires its own transaction manager because different data sources may have different transaction requirements, such as different isolation levels or propagation behaviors

SecondDatabaseConfiguration.java

package com.javainfinite.multipledatabases.manager.configuration;

import jakarta.persistence.EntityManagerFactory;
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 javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "managerEntityManager",
        transactionManagerRef = "managerTransactionManager",
        basePackages = {"com.javainfinite.multipledatabases.manager.repository"}
)
public class SecondDatabaseConfiguration {

    @Bean(name = "managerdb")
    @ConfigurationProperties(prefix = "second.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "managerEntityManager")
    public LocalContainerEntityManagerFactoryBean employeeManager(EntityManagerFactoryBuilder builder,
                                                                  @Qualifier("managerdb") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.javainfinite.multipledatabases.manager")
                .build();
    }

    @Bean(name = "managerTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("managerEntityManager") EntityManagerFactory managerEntityManagerFactory) {

        return new JpaTransactionManager(managerEntityManagerFactory);
    }
}

Employee.java

package com.javainfinite.multipledatabases.employee.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Data
@NoArgsConstructor
@Table(name="employee")
public class Employee {

    @Id
    @GeneratedValue (strategy = GenerationType.AUTO)
    private int id;
    private String eName;
    private String eDept;
}

Manager.java

package com.javainfinite.multipledatabases.manager.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Data
@NoArgsConstructor
public class Manager {

    @Id
    @GeneratedValue (strategy = GenerationType.AUTO)
    private int id;
    private String mName;
    private String mDept;
}

EmployeeRepository.java

package com.javainfinite.multipledatabases.employee.repository;

import com.javainfinite.multipledatabases.employee.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;


public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

}

ManagerRepository.java

package com.javainfinite.multipledatabases.manager.repository;

import com.javainfinite.multipledatabases.manager.model.Manager;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ManagerRepository extends JpaRepository<Manager, Integer> {

}

EmployeeService.java

package com.javainfinite.multipledatabases.employee.service;

import com.javainfinite.multipledatabases.employee.model.Employee;
import com.javainfinite.multipledatabases.employee.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;

@Service
public class EmployeeService {

    private EmployeeRepository repository;

    private DataSource dataSource;

    public EmployeeService(EmployeeRepository repository, @Qualifier("employeedb") DataSource dataSource) {
        this.repository = repository;
        this.dataSource = dataSource;
    }

    public Employee saveEmployee(Employee employee) {
        return repository.save(employee);
    }
}

ManagerService.java

package com.javainfinite.multipledatabases.manager.service;

import com.javainfinite.multipledatabases.manager.model.Manager;
import com.javainfinite.multipledatabases.manager.repository.ManagerRepository;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;

@Service
public class ManagerService {

    private DataSource dataSource;

    private ManagerRepository repository;

    public ManagerService(@Qualifier("managerdb") DataSource dataSource, ManagerRepository repository) {
        this.repository = repository;
        this.dataSource = dataSource;
    }

    public Manager saveManager(Manager manager) {
        return repository.save(manager);
    }
}

EmployeeController.java

package com.javainfinite.multipledatabases.employee.controller;

import com.javainfinite.multipledatabases.employee.model.Employee;
import com.javainfinite.multipledatabases.employee.service.EmployeeService;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/employee")
public class EmployeeController {

    private EmployeeService service;

    public EmployeeController(EmployeeService service) {
        this.service = service;
    }

    @PostMapping("/save")
    public Employee saveEmployee(@RequestBody Employee employee) {
        return service.saveEmployee(employee);
    }
}

ManagerController.java

package com.javainfinite.multipledatabases.manager.controller;

import com.javainfinite.multipledatabases.manager.model.Manager;
import com.javainfinite.multipledatabases.manager.service.ManagerService;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/manager")
public class ManagerController {

    private ManagerService service;

    public ManagerController(ManagerService service) {
        this.service = service;
    }

    @PostMapping("/save")
    public Manager saveManager(@RequestBody Manager manager) {
        return service.saveManager(manager);

    }
}

MultipleDatabaseApplication.java

package com.javainfinite.multipledatabases;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@EntityScan("com.javainfinite.multipledatabases.employee.model")
public class MultipleDatabaseApplication {

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

}

Now let us see the output,

For Manager,

We have successfully Integrated 2 data sources in our spring boot application.