Spring Boot Dynamic DataSource Routing using AbstractRoutingDataSource

By Manish Fartiyal | Last Updated: January 15, 2020 Previous       Next

This page will walk through Dynamic DataSource Routing using AbstractRoutingDataSource and Spring Data JPA in Spring Boot. AbstractRoutingDataSource is an abstract implementation of DataSource that routes call to one of the various target data source based on a lookup key.

AbstractRoutingDataSource introduced in Spring’s 2.0.1 version to provide a way of dynamically determining the actual data source based on the current context. It maintains the map of multiple data sources that get switched via changing context.

Similar Post: Spring Boot Multiple Data Sources Example with Spring JPA

Sometimes we have a requirement to switch databases dynamically on the basis of region or language and perform the operation based on the request.

Suppose, we have two branches i.e. Bangkok and Hongkong and one database for each branch. We need to get details from the Bangkok database if the request comes from the Bangkok branch and if the request comes from the Hongkong branch then from the Hongkong database.

1. What we’ll build

In this tutorial, we will expose a REST endpoint that connects with either hongkongdb or bangkokdb and fetch the employee information from the table based on the request and return the JSON objects.

Endpoint: http://localhost:8080/employee

1.1 If branch = hongkong then it will connect with hongkongdb and fetch the employee information and return:

[
    {
        "id": 5,
        "name": "Jackie Chan",
        "branch": "hongkong"
    },
    {
        "id": 8,
        "name": "Maggie Cheung",
        "branch": "hongkong"
    }
]

1.2 Similarly if branch = bangkok then it will connect with bangkokdb and fetch the employee information of Bangkok branch and return:

[
    {
        "id": 1,
        "name": "Tony Jaa",
        "branch": "bangkok"
    }
]

Note: In the above request, we have added a request header named “branch” with value Bangkok for the first request and Hongkong for the second request.

2. What we’ll need

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

3. Dependencies Required

Add the below dependencies to your pom.xml file.

<?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-datasource-routing</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.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:

5. application.properties

Configure the database (data source) connections strings in the application.properties file for both data sources i.e. hongkongdb and bangkokdb.

#database details for bangkok branch
bangkok.datasource.url=jdbc:mysql://localhost:3306/bangkokdb?createDatabaseIfNotExist=true
bangkok.datasource.username=root
bangkok.datasource.password=root

#database details for hongkong branch
hongkong.datasource.url=jdbc:mysql://localhost:3306/hongkongdb?createDatabaseIfNotExist=true
hongkong.datasource.username=root
hongkong.datasource.password=root

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

6. Entity

First – let’s create a simple entity, which will be living in both of the databases.

package org.websparrow.entity;

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

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

7. DataSource Context

We have created a BranchEnum which holds the name of both branches. AbstractRoutingDataSource needs a piece of information to which database to route to. Here this enum will work as a context for AbstractRoutingDataSource class.

package org.websparrow.constant;

public enum BranchEnum {

	BANGKOK, HONGKONG
}

8. Context Holder

BranchContextHolder will hold the current context of the branch as a ThreadLocal reference. This class will provide thread-bound access to BranchEnum.

package org.websparrow.config;

import org.websparrow.constant.BranchEnum;

public class BranchContextHolder {

	private static ThreadLocal<BranchEnum> threadLocal = new ThreadLocal<>();

	public static void setBranchContext(BranchEnum branchEnum) {
		threadLocal.set(branchEnum);
	}

	public static BranchEnum getBranchContext() {
		return threadLocal.get();
	}

	public static void clearBranchContext() {
		threadLocal.remove();
	}
}

9. DataSource Routing

DataSourceRouting extends the AbstractRoutingDatasource class, which will contain the map of real data sources. Override its determineCurrentLookupKey() method to determine the current lookup key. This will typically be implemented to check a thread-bound transaction context.

package org.websparrow.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.websparrow.constant.BranchEnum;

public class DataSourceRouting extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		return BranchContextHolder.getBranchContext();
	}

	public void initDatasource(DataSource bangkokDataSource,
			DataSource hongkongDataSource) {
		Map<Object, Object> dataSourceMap = new HashMap<>();
		dataSourceMap.put(BranchEnum.BANGKOK, bangkokDataSource);
		dataSourceMap.put(BranchEnum.HONGKONG, hongkongDataSource);
		this.setTargetDataSources(dataSourceMap);
		this.setDefaultTargetDataSource(bangkokDataSource);
	}
}

Data source map is set to targetDataSources and one data source is selected as a default data source.

10. DataSource Config Details

We will create two classes that will hold database connection properties for both the databases.

package org.websparrow.model;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "hongkong.datasource")
public class HongkongDetails {

	private String url;
	private String password;
	private String username;
	// Generates Getters and Setters...
}

package org.websparrow.model;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "bangkok.datasource")
public class BangkokDetails {

	private String url;
	private String password;
	private String username;
	// Generates Getters and Setters...
}

11. DataSource Configuration

Now we will create data sources for both of our databases put them into a map and provide it to DataSourceRouting.

package org.websparrow.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
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.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.websparrow.entity.Employee;
import org.websparrow.model.BangkokDetails;
import org.websparrow.model.HongkongDetails;

@Configuration
@EnableJpaRepositories(
		basePackages = "org.websparrow.repo",
		transactionManagerRef = "transcationManager",
		entityManagerFactoryRef = "entityManager")
@EnableTransactionManagement
public class DataSourceConfig {

	@Autowired
	private BangkokDetails bangkokDetails;
	@Autowired
	private HongkongDetails hongkongDetails;

	@Bean
	@Primary
	@Autowired
	public DataSource dataSource() {
		DataSourceRouting routingDataSource = new DataSourceRouting();
		routingDataSource.initDatasource(bangkokDataSource(),
				hongkongDataSource());
		return routingDataSource;
	}

	public DataSource hongkongDataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setUrl(hongkongDetails.getUrl());
		dataSource.setUsername(hongkongDetails.getUsername());
		dataSource.setPassword(hongkongDetails.getPassword());
		return dataSource;
	}

	public DataSource bangkokDataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setUrl(bangkokDetails.getUrl());
		dataSource.setUsername(bangkokDetails.getUsername());
		dataSource.setPassword(bangkokDetails.getPassword());
		return dataSource;
	}

	@Bean(name = "entityManager")
	public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
			EntityManagerFactoryBuilder builder) {
		return builder.dataSource(dataSource()).packages(Employee.class)
				.build();
	}

	@Bean(name = "transcationManager")
	public JpaTransactionManager transactionManager(
			@Autowired @Qualifier("entityManager") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
		return new JpaTransactionManager(entityManagerFactoryBean.getObject());
	}
}

12. DataSource Interceptor

DataSourceInterceptor intercepts every request and get branch information from request headers and put it to context holder as we already created to BranchContextHolder via which we will switch data source.

package org.websparrow.config;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Component;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;
import org.websparrow.constant.BranchEnum;

@Component
public class DataSourceInterceptor extends HandlerInterceptorAdapter {

	@Override
	public boolean preHandle(HttpServletRequest request,
			HttpServletResponse response, Object handler) throws Exception {

		String branch = request.getHeader("branch");
		if (BranchEnum.BANGKOK.toString().equalsIgnoreCase(branch))
			BranchContextHolder.setBranchContext(BranchEnum.BANGKOK);
		else
			BranchContextHolder.setBranchContext(BranchEnum.HONGKONG);
		return super.preHandle(request, response, handler);
	}
}

Register this intercepter to WebMvcConfigurer.

package org.websparrow.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebConfig implements WebMvcConfigurer {

	@Autowired
	private DataSourceInterceptor dataSourceInterceptor;

	@Override
	public void addInterceptors(InterceptorRegistry registry) {
		registry.addInterceptor(dataSourceInterceptor).addPathPatterns("/**");
		WebMvcConfigurer.super.addInterceptors(registry);
	}
}

13. Repository & Service

package org.websparrow.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.websparrow.entity.Employee;

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

package org.websparrow.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.websparrow.entity.Employee;
import org.websparrow.repo.EmployeeRepository;

@Service
public class EmployeeService {

	@Autowired
	private EmployeeRepository employeeRepository;

	public List<Employee> getEmployees() {
		return employeeRepository.findAll();
	}
}

14. Controller

EmployeeController class exposes the REST endpoint for the application user. In this controller class, we have created a REST endpoint as follows:

http://localhost:8080/employee: will retrieve records of the employee from selected data-source.

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.Employee;
import org.websparrow.service.EmployeeService;

@RestController
public class EmployeeController {

	@Autowired
	private EmployeeService employeeService;

	@GetMapping(value = "employee")
	public ResponseEntity<List<Employee>> getEmployees() {
		return ResponseEntity.status(HttpStatus.ACCEPTED)
				.body(employeeService.getEmployees());
	}
}

15. Run the application

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

package org.websparrow;

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

@SpringBootApplication
public class DataSourceRoutingApp {

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

16. Test the application

To test the application, start the Spring Boot application by executing the above class and hit the below API with header params i.e branch and its value:

API: http://localhost:8080/employee

16.1 If branch = hongkong then it will connect with hongkongdb and fetch the employee information:

[
    {
        "id": 5,
        "name": "Jackie Chan",
        "branch": "hongkong"
    },
    {
        "id": 8,
        "name": "Maggie Cheung",
        "branch": "hongkong"
    }
]

16.2 And if branch = bangkok then it will connect with bangkokdb and fetch the employee information of Bangkok branch:

[
    {
        "id": 1,
        "name": "Tony Jaa",
        "branch": "bangkok"
    },
    {
        "id": 2,
        "name": "Urassaya Sperbund",
        "branch": "bangkok"
    }
]

16.3 How to set header params?

Using the Postman client, the header params can be set in the Header tab:

References

1. Spring Boot Multiple Data Sources Example with Spring JPA
2. Dynamic DataSource Routing
3. AbstractRoutingDataSource Class