Skip to main content

SpringBoot Multi Tenancy with JPA and JdbcTemplate - Dynamically RoutingDataSource - AbstractRoutingDataSource

We were doing the steps to let working a SpringBoot application in the previous post in a multi tenant mode with JdbcTemplate, in this post we are going to be showing how to do it with JPA and JdbcTemplate at the same time, I do it so that it is really useful sometimes taking advantages of both ways, sometimes for doing reports with JdbcTemplate in my case is just amazing, and sometimes for creating new records in to the database with JPA is amazing too and so on, you can combine them whenever you want

the whole explanation is the same thing as in the previous post https://springboot-vuejs-reactjs.blogspot.com/2019/08/springboot-multi-tenancy-with.html  the only thing is just adding a dependency in the .pom and a couple of settings in the @SpringBoot Application.

Let's create a new project in https://start.spring.io/ and naming it MultitenantJPA-and-JdbcTemplate 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.


let's create this hierarchy folder

|---AplicationLayer
|------------------------JDBC
|----------------------------Controllers
|----------------------------Dao
|----------------------------Models
|-------------------------JPA
|----------------------------Controllers
|----------------------------Models
|----------------------------Repositories
|----------------------------Servicies
|---MultiTenantSettings
|----------------------TenantDataSources
|----------------------TenantInterceptorRoutingDataSource



* 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);


application.properties

server.port=8085
logging.level.org.springframework.web=DEBUG

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
# Naming strategy
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

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.MultitenantJPAandJdbcTemplate.MultiTenantSettings.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.MultitenantJPAandJdbcTemplate.MultiTenantSettings.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 MultitenantJpaAndJdbcTemplateApplication.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.MultitenantJPAandJdbcTemplate;

import javax.sql.DataSource;

import com.tenancy.MultitenantJPAandJdbcTemplate.MultiTenantSettings.TenantDataSources.DataSourceMap;
import com.tenancy.MultitenantJPAandJdbcTemplate.MultiTenantSettings.TenantInterceptorRoutingDataSource.CustomRoutingDataSource;

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

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;

@EnableTransactionManagement
@ComponentScan(basePackages = "com.tenancy.MultitenantJPAandJdbcTemplate")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
//@EnableJpaRepositories(basePackages = "com.tenancy.MultitenantJPAandJdbcTemplate")
public class MultitenantJpaAndJdbcTemplateApplication {

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


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


JDBC - Model - User.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.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;
    }

}


JDBC - Controller - UserController.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JDBC.Controller;

import java.util.List;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JDBC.Dao.UserDAO;
import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.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);
 }

}

JDBC - Dao - UserDAO.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JDBC.Dao;

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

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.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



so now let's start doing the process to persist data into an Entity called Employee with JPA

JPA - Model - Employee.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Models;

import javax.persistence.Entity;
import javax.persistence.Column;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
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;
    }
}


JPA - Repository - EmployeeRepository.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Repositories;


import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Models.Employee;


/**
 * Created by Java Developer Zone on 03-08-2017.
 */

public interface EmployeeRepository extends CrudRepository<Employee,Integer> {
  
}


JPA - Services - EmployeeService.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Servicies;

import java.util.List;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Models.Employee;


public interface EmployeeService{

    public void createEmployee(Employee item);
    public List<Employee> getItems();

}


JPA - Services - EmployeeServiceImplementation.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Servicies;

import java.util.List;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Models.Employee;
import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Repositories.EmployeeRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class EmployeeServiceImplementation implements EmployeeService{

    @Autowired
    EmployeeRepository em;

    @Override
    public void createEmployee(Employee item) {

        em.save(item);
    }

 @Override
 public List<Employee> getItems() {
 return null;
 }

}



JPA - Controllers - EmployeeController.java

package com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Controllers;




import org.springframework.web.bind.annotation.PostMapping;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Models.Employee;
import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JPA.Servicies.EmployeeService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.util.UriComponentsBuilder;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
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.util.UriComponentsBuilder;
/**
 * Created by Java Developer Zone on 19-07-2017.
 */
@RestController
@RequestMapping(value = { "/create" })
public class EmployeeController {
    @Autowired
    EmployeeService employeeDAO;
   

    @PostMapping(value = "/user")
    public ResponseEntity<Void> createItem(@RequestBody Employee item,UriComponentsBuilder builder) {

        employeeDAO.createEmployee(item);

        return new ResponseEntity<Void>(HttpStatus.CREATED);
    }

}

Let's tested with Postman

NOTE: keep in mind,  when we start the project with mvn spring-boot:run the database by default is db1 so if the table Employee is not created yet, it creates the table automatically, but if you switch to the second database and if the table is not created yet in the second database and if you are going to persist data in an unknown table in runtime it is going to throw an exception, so the best thing to persist the data between db1 and db2 is having all tables synchronized before starting persisting data with JPA

persisting data into the database db1 http://localhost:8085/create/user?tenantId=tenantId1



persisting data into the database db2 http://localhost:8085/create/user?tenantId=tenantId2







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