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:

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

Nginx reverse proxy for ReactJS, Vuejs,Laravel and Django with or without Docker

Nginx is an important piece on the working chain apps so that it works as bridge between the end user and the application servers, to do that we will be using nginx as a reverse proxy to know more in deep about nginx reverse proxy go to https://docs.nginx.com/nginx/admin-guide/web-server/reverse-proxy/ let's suppose you have a domain name called yourdomainame.com Nginx - ReactJS/Vuejs each time you need to create a new domain name, set it on the path /etc/nginx/conf.d/ create a file with vim or nano or whatever you want, and give it the domain name .conf and set the settings on it, this is really useful when you start handling several domain names under the same server vim /etc/nginx/conf.d/ yourdomainame.com.conf server {         server_name   yourdomainame.com wwww.yourdomainame.com;         # Load configuration files for the default server block.         include /etc/nginx/default.d/ *.conf ;   ...
Post a Comment
Read more

Uploading and Deleting Files with Amazon S3 and SpringBoot

Today I am going to be sharing about how to upload and delete files at AmazonS3 with SpringBoot , Amazon Simple Storage Service (S3)  is an object storage platform which helps you to store the files in form of objects, it allows us to store and retrieve any amount of data from anywhere.  Each file is stored in Amazon S3 (as an object) and is represented using a key. we are going to be using AWS Java SDK which supports various APIs related to Amazon S3 service for working with files stored in S3 bucket. so after going to  https://start.spring.io/  and create a new SpringBoot project with the Artifact as  AmazonS3  or as you prefer, open your pom.xml and let's add this dependency. AWS Java SDK supports several  APIs related to Amazon S3 service for working with files stored in S3 bucket.  <!-- https://mvnrepository.com/artifact/com.amazonaws/aws-java-sdk --> < dependency >     < groupId >...
7 comments
Read more