Skip to main content

SpringBoot Multi Tenancy with JdbcTemplate - Dynamically RoutingDataSource - AbstractRoutingDataSource

Introduction to multi tenant applications

 What are multi tenant apps?

 Multi tenant applications allow you to serve multiple customers with one install of the application. Each customer has their data completely isolated in such an architecture. Each customer is called a tenant.

 Most modern Software as a Service applications are multi tenant. Whether it is Salesforce, Freshbooks, Zoho or Wordpress, most modern cloud based applications are delivered with a multi-tenant architecture.
What about if you've got your application done in SpringBoot and you sell it to 10 costumers which want to use your application, there would be several ways to do this, one way would be a deployment per customer with its own database and so on, and what about if those10 costumers later turn into 30 and you must do an important change to the application, then you would have to make 30 redeploys of the same application,
so to avoid this kind of thing there is a solution turning our application into a multitenant.

We can develop multi-tenant applications using SpringBoot, where we can select the database at runtime based on the user. For example, when a request comes from 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.

 * We can pass the tenant as a request parameter, like ?tenantId=client1 or ?tentantId=client2

* We can store the tenant in session once user login and fetch from the session whenever it is needed.
Let's create 2 databases db1, and db2 and let's create a table on each database like this
CREATE TABLE `db1`.`users`
 ( `id` INT(2) NOT NULL AUTO_INCREMENT , `name`VARCHAR(100) NOT NULL , `rol` INT(2) NOT NULL , PRIMARY KEY (`id`)) ENGINE =InnoDB;

CREATE TABLE `db2`.`users` ( `id` INT(2) NOT NULL AUTO_INCREMENT , `name`VARCHAR(100) NOT NULL , `rol` INT(2) NOT NULL , PRIMARY KEY (`id`)) ENGINE =InnoDB;

and let's fill the tables with some data with https://mockaroo.com/
insert into `db1`.`users` (id, name, rol) values (1, 'DB-1-Haydon', 1);
insert into `db1`.`users` (id, name, rol) values (2, 'DB-1-Chandler', 1);
insert into `db1`.`users` (id, name, rol) values (3, 'DB-1-Enrica', 1);
insert into `db1`.`users` (id, name, rol) values (4, 'DB-1-Renie', 1);
insert into `db1`.`users` (id, name, rol) values (5, 'DB-1-Krishna', 1);


insert into `db2`.`users` (id, name, rol) values (1, 'DB-2-Haydon', 2);
insert into `db2`.`users` (id, name, rol) values (2, 'DB-2-Chandler', 2);
insert into `db2`.`users` (id, name, rol) values (3, 'DB-2-Enrica', 2);
insert into `db2`.`users` (id, name, rol) values (4, 'DB-2-Renie', 2);
insert into `db2`.`users` (id, name, rol) values (5, 'DB-2-Krishna', 2);


let's go to https://start.spring.io/ and let's create project naming MultitenantJdbcTemplate adding the next dependencies to the pom,  I am using MySQL you can choose any other database engine, or you can even have several at the same time
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</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-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>junit</groupId>

                    <artifactId>junit</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

application.properties

server.port=8085

let's create this hierarchy folder


|---JDBC
|-------Controller
|-------Dao
|-------Model
|---TenantDataSources
|---TenantInterceptorRoutingDataSource


DataSourceMap.java

DataSourceMap inside the package TenantDataSources 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.tenancy.MultitenantJdbcTemplate.TenantDataSources;

import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.util.HashMap;
import java.util.Map;
/**
 * Created by JavaDeveloperZone on 22-01-2017.
 */
public class DataSourceMap {
    public static Map<Object, Object> getDataSourceHashMap() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/db1?serverTimezone=UTC");
        dataSource.setUsername("root");
        dataSource.setPassword("");


        DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/db2?serverTimezone=UTC");
        dataSource1.setUsername("root");
        dataSource1.setPassword("");
        HashMap hashMap = new HashMap();
        hashMap.put("tenantId1", dataSource);
        hashMap.put("tenantId2", dataSource1);
        return hashMap;
    }
}

CustomRoutingDataSource.java

When any database operation is performed then it automatically comes here, This method will return a tenantId as a String based on that database will be selected to perform the operations. We are maintaining tenantId in URL so reading from URL as a parameter using ServletRequestAttributes
package com.tenancy.MultitenantJdbcTemplate.TenantInterceptorRoutingDataSource;

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 {
    @Override
    protected Object determineCurrentLookupKey() {
        ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();     // get request object
        System.out.println("THE URL IS ----> "+attr);
       
        if(attr!=null) {
            String tenantId = attr.getRequest().getParameter("tenantId");       // find parameter from request
            return tenantId;
        }else{
            return "tenantId1";             // default data source
        }
    }
}

@SpringBootApplication

Let's open the main java application if you gave it the same name as suggested then lets open MultitenantJdbcTemplateApplication.java and let's create 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.tenancy.MultitenantJdbcTemplate;

import javax.sql.DataSource;

import com.tenancy.MultitenantJdbcTemplate.TenantDataSources.DataSourceMap;
import com.tenancy.MultitenantJdbcTemplate.TenantInterceptorRoutingDataSource.CustomRoutingDataSource;

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.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@ComponentScan(basePackages = "com.tenancy.MultitenantJdbcTemplate")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MultitenantJdbcTemplateApplication {

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

 @Bean
    public DataSource dataSource(){
        CustomRoutingDataSource customDataSource=new CustomRoutingDataSource();
        customDataSource.setTargetDataSources(DataSourceMap.getDataSourceHashMap());
        return customDataSource;
    }

}

Model - User.java

package com.tenancy.MultitenantJdbcTemplate.JDBC.Model;

public class User{

    public String id;
    public String name;
    public String rol;

    public String getId() {
        return this.id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRol() {
        return this.rol;
    }

    public void setRol(String rol) {
        this.rol = rol;
    }

    public User(String id, String name, String rol) {
        this.id = id;
        this.name = name;
        this.rol = rol;
    }

}

Controller - UserController.java

package com.tenancy.MultitenantJdbcTemplate.JDBC.Controller;

import java.util.List;

import com.tenancy.MultitenantJdbcTemplate.JDBC.Dao.UserDAO;
import com.tenancy.MultitenantJdbcTemplate.JDBC.Model.User;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("multitenant")
public class UserController{

    @Autowired
    private UserDAO userDao;

    @GetMapping("users/{id}")
 public ResponseEntity<List<User>> getArticleById(@PathVariable("id") Integer id) {
        System.out.println("the id "+id);
 List<User> user = userDao.getAllUsers();
 return new ResponseEntity<List<User>>(user, HttpStatus.OK);
 }

}


Dao - UserDAO.java

package com.tenancy.MultitenantJdbcTemplate.JDBC.Dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.tenancy.MultitenantJdbcTemplate.JDBC.Model.User;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;



@Transactional
@Repository
public class UserDAO{

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    JdbcTemplate jdbcTemplate;


    public List<User> getAllUsers(){


        List<User> allUsers = new ArrayList<>();

        String queryTotal = "select id,name,rol from users";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(queryTotal);
            for (Map row : rows) {

                allUsers.add(new User(row.get("id").toString(),row.get("name").toString(),row.get("rol").toString()));
            }


        return allUsers;
    }



}




Let's tested with Postman

Let's get the data from the users table from the database db1
http://localhost:8085/multitenant/users/5?tenantId=tenantId1




http://localhost:8085/multitenant/users/5?tenantId=tenantId2



you can download the whole code from GitHub in https://github.com/juandavidmarin368/SpringBoot-MultitenantJdbcTemplate


next chapter we are going to be doing the same process but for JPA and JdbcTemplate at the same time

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

How to deploy a VueJS App using Nginx on Ubuntu

There are thousands of blogs and websites out there explaining how to do a hello world and how to start with VueJS, but in this little post, I’m just going to be explaining how to do deploy a VueJs app after you have run the command through the CLI npm run build . So when you run the command npm run build a dist folder is created and that folder’s got the essential .js files to run our app, when we want to run our app on an Nginx server by default the vue-router is not going to work well so that Nginx does not come ready to work by default with a VueJs app This is basically for a Linux Ubuntu distribution if you’ve got any other Linux distribution just pay attention where is going to be set the www/html folder and the Nginx settings so that this is useful for any Linux distribution  Install and configure nginx sudo apt install nginx Double check to make sure the nginx service is running with command service nginx status, then open your browser and enter ...
1 comment
Read more

How to secure SpringBoot with SSL and Tomcat or Undertow

when we are going to take our applications to production mode, we must have an SSL certificate for the FrontEnd and   BackEnd too, in this case, our backend is a  SpringBoot which is using a tomcat embedded server. Terminology TLS vs SSL TLS is the successor to SSL. It is a protocol that ensures privacy between communicating applications. Unless otherwise stated, in this document consider TLS and SSL as interchangable. Certificate (cert) The public half of a public/private key pair with some additional metadata about who issued it etc. It may be freely given to anyone. Private Key A private key can verify that its corresponding certificate/public key was used to encrypt data. It is never given out publicly. Certificate Authority (CA) A company that issues digital certificates. For SSL/TLS certificates, there are a small number of providers (e.g. Symantec/Versign/Thawte, Comodo, GoDaddy, LetsEncrypt) whose certificates are included by most browsers and Op...
2 comments
Read more

How to docker a Laravel 7.4 API with NuxtJS and a AWS RDS using Dockerfile and docker-compose.yml in a Centos EC2 instance

Most of the time people use docker to deploy Laravel, NuxtJS, a desired database engine as Mysql or Postgresql and even Nginx, but this tutorial is quite different it is for those ones who wants to deploy one or more Laravel APIs with NuxtJS using an existing AWS RDS, and a Nginx installed in your host with cerbot as the tool to get the ssl for the domain name Note: you're supposed to have running an AWS RDS with your database already available used tools: On Centos 7            yum update -y && yum install epel-release -y &&          yum install update -y && yum upgrade -y && yum install unzip -y &&          yum install screen -y && yum install nginx -y && yum install telnet -y &&          systemctl enable nginx && systemctl restart nginx && yum install...
1 comment
Read more