1. Overview

In this article, We will learn Spring JPA Multi Tenancy example or Spring JPA database routing Example. While developing SAAS (Software As A Service) based application we require to change the database at runtime or routing of the database.

Let try to understand what is a requirement of Multi-Tenancy application, Let’s assume that we are developing one application, and we want to sell the same application to 4 clients, In a simple case, we will create 4 web server (i.e tomcat), 4 databases and deploy the same application 4 times for each client. Now if any changes will come in the application then we must need to update at 4 places every time. We need to take care and maintain 4 environments, Now if business growing up and more clients purchase our application then create the same environments and time and cost will be increased.

To overcome the above problem, We can develop multi tenant application where we can select the database at runtime based on the user. For example, when a request comes form client1 then select client1’s database, the request comes from client2 then select client2’s database and so on. but for this, we need a specific identifier for each client’s request identifier also knows as the tenant. So we just need to maintain one web server (i.e. tomcat) and N databases only.

Let’s discuss some ways to discuss maintain tenant.

  1. We can maintain tenant in URL like,
    1. client1.example.com, client2.example.com, Here client1 and client2 are tenant using that we can identify that request comes from which client.
    2. We can pass tenant in as request parameter, like ?tenantId=client1 or ?tentantId=client2
    3. We can pass tenant in request header (More suitable for REST API).
    4. We can store tenant in session once user login and fetch from the session whenever requires.

Above we have discussed the architecture of the application, Now let discuss about it the technical implementation of database routing with Spring boot JPA. If do not have any idea about Spring JPA then here is an example to create web service in spring JPA with MySQL database.

In normal Spring JPA application we create DataSource but while applying to the route we require AbstractRoutingDataSource which will help us database routing at runtime. Let discuss with an example:

If you are using only Spring MVC and hibernate then here is an example for Hibernate Tenancy example.

2. Example : Spring JPA database routing Example

In this example, We have used Spring boot JPA with two database demo_database_1 and demo_database_2 , We will pass tenantId in request URL based on tenantId records will be fetched from the relative database:


Spring boot JPA Multi Tenancy example

Spring boot JPA Multi Tenancy example

2.1 pom.xml

  • spring-boot-starter-data-jpa for spring JPA.
  • mysql-connector-java for MySQL connection
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <description>Spring boot JPA Multi Tenancy example</description>
    <!-- Inherit defaults from Spring Boot -->
    <!-- Package as an executable jar -->

2.2 application.properties

Here are some properties related to spring jpa. spring.jpa.hibernate.ddl-auto will not work for routing database, It only works for @Primary database.

# Naming strategy
# Allows Hibernate to generate SQL optimized for a particular DBMS

2.3 SpringBootConfig.java

Here we have created a bean  DataSource of class CustomRoutingDataSource, TargetDataSources indicate Key and Value pair, where the key is tenantId and value is a data source.

package com.javadeveloperzone;
import com.javadeveloperzone.master.MasterService;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;

 * Created by Java Developer Zone on 19-07-2017.
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@ComponentScan(basePackages = "com.javadeveloperzone")
@EnableJpaRepositories(basePackages = "com.javadeveloperzone")
public class SpringBootConfig {
    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootConfig.class, args);            // it wil start application
    public DataSource dataSource(){
        CustomRoutingDataSource customDataSource=new CustomRoutingDataSource();
        return customDataSource;

2.4 CustomRoutingDataSource.java

When any database operation will be perform then control automatically comes here, This method will return tenantId as String based on that database will be selected to perform the operations. Here we write custom login to find tenantId, We are maintaining tenantId in URL so reading from URL as parameter using ServletRequestAttributes

package com.javadeveloperzone;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
public class CustomRoutingDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();     // get request object
        if(attr!=null) {
            String tenantId = attr.getRequest().getParameter("tenantId");       // find parameter from request
            return tenantId;
            return "tenantId2";             // default data source

2.5 MasterService.java

Master service will return the Map of data source where key contains tenantId and value contain data source. The datasource object contains a database driver, database URL, username and password.

package com.javadeveloperzone.master;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.util.HashMap;
import java.util.Map;
 * Created by JavaDeveloperZone on 22-01-2017.
public class MasterService {
    public static Map<Object, Object> getDataSourceHashMap() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
        HashMap hashMap = new HashMap();
        hashMap.put("tenantId1", dataSource);
        hashMap.put("tenantId2", dataSource1);
        return hashMap;

2.6 EmployeeDAO.java

It employee CrudRepository to preform a database operation.

package com.javadeveloperzone.dao;
import com.javadeveloperzone.model.Employee;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

 * Created by Java Developer Zone on 03-08-2017.
public interface EmployeeDAO extends CrudRepository<Employee,Integer> {
    List<Employee> findAll();                           // fetch all Employee

2.7 Employee.java

package com.javadeveloperzone.model;
import javax.persistence.*;
 * Created by Java Developer Zone on 03-08-2017.
@Table(name = "employee")
public class Employee {
    @Id                                                     // primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)     // auto increment
    @Column(name = "employeeId")
    private int employeeId;
    @Column(name = "employeeName")
    private String employeeName;
    @Column(name = "employeeRole")
    private String employeeRole;
    public int getEmployeeId() {
        return employeeId;
    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    public String getEmployeeRole() {
        return employeeRole;
    public void setEmployeeRole(String employeeRole) {
        this.employeeRole = employeeRole;
    public String getEmployeeName() {
        return employeeName;
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;

2.8 EmployeeController.java

package com.javadeveloperzone.controller;
import com.javadeveloperzone.dao.EmployeeDAO;
import com.javadeveloperzone.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 * Created by Java Developer Zone on 19-07-2017.
public class EmployeeController {
    private EmployeeDAO employeeDAO;
    @RequestMapping(value = "emploeeList")
    public java.util.List<Employee> emploeeList() {
        return employeeDAO.findAll();

2.9 Output:

  • http://localhost:8080/emploeeList?tenantId=tenantId1

Here data will fetch from demo_database_1

Spring boot JPA Multi Tenancy example - TenantID1

Spring boot JPA Multi Tenancy example – TenantID1

  • http://localhost:8080/emploeeList?tenantId=tenantId2

Here data will fetch from demo_database_2

Spring boot JPA Multi Tenancy example - TenantID2

Spring boot JPA Multi Tenancy example – TenantID2

3. Conclusion

Here we learned at a way to implements multiple tenant application using Spring boot JPA and Spring JPA ways to implements database routing in SAAS based application.

4. References

Was this post helpful?

6 comments. Leave new

Srinivas swamy
May 16, 2019 1:54 pm

As you have given in the introduction, we may have 100+ clients would come. When will be the data source object create? If it has already created, Is it safe to maintain multiple data source objects by default in the memory? Does it give memory leak?
Could you please tell me how this work if we have 100+ customers databases, and for every time we need to add a new data source in “MasterService.java” or can it be done dynamically?
I am new to this concept and I have to implement this functionality in my application. Could you please tell me how will it work if we have 100 number of customers? How can we make this dynamic?

Hi Srinivas swamy,

You can do one thing, You can create one master data source with a master database, Master database has a table which contains all the information about all the clients. so when the application will start you can load the client information from a master data source or you also load lazy loading of data. are you getting me?

Hi, first of all thanks for the nice article. Is there a way to reload the datasources list dynamically. Let’s say, in my application I want to give option to add new customer, which will create a new database for him with a tenantId. I would like to add this new datasource dynamically to CustomRoutingDataSource target datasources.

Hi Srinivasa,
You can do one thing, You can create one master data source with a master database, Master database has a table which contains all the information about all the clients. so when the application will start you can load the client information from a master data source or you also load lazy loading of data. are you getting me?

Hi, Thanks for the post. I do have one question is the AbstractRoutingDataSource creating a separate thread for each tenant? If not how can we create one thread per tenant?

Can you please explain to me the requirement what is the need for one thread per tenant so get a clear idea about that?

Leave a Reply

Your email address will not be published. Required fields are marked *