Skip to main content

SpringBoot Multi Tenancy with JPA and JdbcTemplate - Dynamically reading RoutingDataSource from a data base table

The last 2 posts were about how to do a Multitenant mode with JPA and JdbcTemplate but the dataSources were hardcoded, what we are going to be doing in this post is to be able to read the dataSources from a database instead of being hardcoded.

 Let's supposed you've got a SaaS  Application and there are more and more customers who want to use your application, and you want to create a customer in an automatically way, with this post you are going to be able to do part of it, so that for doing a process like that you would need a DevOps for sure  when dealing with new DNS and  deploying a database structure to start with and may other things.

what this post will cover is doing 2 SpringBoot Applications, the first one,it is a Dynamic Database Manager which is going to get the url, the tenantId, the username and password of the tenants.

 the second SpringBoot Application the MultitenantJPA-and-JdbcTemplate which we did in the previous post, the only difference is that its dataSources are not hardcoded any longer, each time the application starts it reads the dataSources through a request done to the first SpringBoot Application DynamicDBManager the one who is the responsible for getting back all tenants details to MultitenantJPA-and-JdbcTemplate.

 Let's gets started creating the first SpringBoot Application the DynamicDBManager 

 Note: Before starting let's create or use a database in which you will have the details about your tenants, for this purpose I am using a database called data and the table to save the tenant settings is

Tenant Details

CREATE TABLE `data`.`tenant_details` ( `id` INT NOT NULL AUTO_INCREMENT ,`driver` VARCHAR(100) NOT NULL , `url` VARCHAR(100) NOT NULL , `username`VARCHAR(100) NOT NULL , `password` VARCHAR(100) NOT NULL , `tenant_id`VARCHAR(100) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;


let's got to https://start.spring.io/ and create a project in Artifact called DynamicDBManager and let's replace in Project Metadata com.example by com.managerdb

 let's create this hierarchy folder
|---AplicationLayer
|------------------------JDBC
|----------------------------Controllers
|----------------------------Dao
|----------------------------Models


JDBC - Model - Database.java


package com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Model;

public class Database{

  
    public String url;
    public String username;
    public String password;
    public String tenantid;
    public String driver;

    public String getDriver() {
        return this.driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getTenantid() {
        return this.tenantid;
    }

    public void setTenantid(String tenantid) {
        this.tenantid = tenantid;
    }

    public String getUrl() {
        return this.url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return this.username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    public Database(String url, String username, String password, String tenantid, String driver) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.tenantid = tenantid;
        this.driver = driver;
    }


}

JDBC - Dao - DatabaseDAO.java


package com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Dao;

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

import com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Model.Database;

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 DatabaseDAO{

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    JdbcTemplate jdbcTemplate;


    public List<Database> getAllUsers(){


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

        String queryTotal = "select password, tenant_id, url, username,driver from tenant_details";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(queryTotal);
            for (Map row : rows) {

                allUsers.add(new Database(row.get("url").toString(),row.get("username").toString(),row.get("password").toString(),row.get("tenant_id").toString(),row.get("driver").toString()));
            }


        return allUsers;
    }



}

JDBC - Dao - DatabaseController


package com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Controller;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;

import com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Dao.DatabaseDAO;
import com.managerdb.DynamicDBManager.ApplicationLayer.JDBC.Model.Database;

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;
import org.springframework.web.client.RestTemplate;


@RestController
@RequestMapping("dynamic")
public class DatabaseController{

    @Autowired
    private DatabaseDAO userDao;


    @GetMapping("/databases")
 public ResponseEntity<List<Database>> getArticleById() {
   
List<Database> user = userDao.getAllUsers();
 return new ResponseEntity<List<Database>>(user, HttpStatus.OK);
    }
   

}


let's fill the tenant_details table

now with Postman let's get the databases with the endPoint localhost:8090/dynamic/databases



 ok, so as you can see now we can our tenants details from this SpringBoot Application called DynamicDBManager, as always you get the whole of both projects in GitHub on this link https://github.com/juandavidmarin368/DYNAMIC-MULTITENANT


the next SpringBoot Application is called MultitenantJPA-and-JdbcTemplate and it is the samething as we did in the previous link, there is just a change in the package package com.tenancy.MultitenantJPAandJdbcTemplate.MultiTenantSettings.TenantDataSources; in the class DataSource.java so that is the one is loading the DataSources but through an HttpGet request to the first SpringBoot Application
package com.tenancy.MultitenantJPAandJdbcTemplate.MultiTenantSettings.TenantDataSources;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JDBC.Dao.UserDAO;
import com.tenancy.MultitenantJPAandJdbcTemplate.AplicationLayer.JDBC.Model.User;

import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;


public class DataSourceMap {
   

    public static String getData()throws ClientProtocolException, IOException{

        HttpClient client = new DefaultHttpClient();
        HttpGet request = new HttpGet("http://localhost:8090/dynamic/databases");
        HttpResponse response = (HttpResponse) client.execute(request);
        String result = EntityUtils.toString(response.getEntity());
  
        return result;

     }


    public static Map<Object, Object> getDataSourceHashMap()  {


        HashMap hashMap = new HashMap();

        try{
            String result = getData();

            // CONVERT RESPONSE STRING TO JSON ARRAY
            JSONArray ja = new JSONArray(result);

            // ITERATE THROUGH AND RETRIEVE CLUB FIELDS
            int n = ja.length();
           
            int temporal = 1;
           
            for (int i = 0; i < n; i++) {
                // GET INDIVIDUAL JSON OBJECT FROM JSON ARRAY
                JSONObject jo = ja.getJSONObject(i);

                System.out.println("-->> "+jo.getString("tenantid"));
      
                DriverManagerDataSource datasource = getata(jo.getString("url"), jo.getString("username"),jo.getString("password"),jo.getString("driver"));
                   
                hashMap.put("tenantId"+jo.getString("tenantid"), datasource);
                temporal++;

            }


        }catch(IOException e){
           
        }
       
 
        return hashMap;
    }

   
    public static DriverManagerDataSource getata(String url, String username, String password, String driver){

        System.out.println("DATA SROUCE "+url);

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);


        return dataSource;

    }



}

and there were added 2 more dependencies to the pom.xml file
<dependency>
   <groupId>org.apache.httpcomponents</groupId>
   <artifactId>httpclient</artifactId>
</dependency>
<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20180813</version>
</dependency>
let's test it now with POSTMAN

endpoint getting data from the tenant or Database db1
http://localhost:8085/multitenant/users/8?tenantId=tenantId1


endpoint getting data from the tenant or Database db2
http://localhost:8085/multitenant/users/8?tenantId=tenantId2

endpoint getting data from the tenant or Database db3
http://localhost:8085/multitenant/users/8?tenantId=tenantId3



you can get the both projects from this link on GitHub https://github.com/juandavidmarin368/DYNAMIC-MULTITENANT next posts will be covering how to do the process to deploy a blueprint database for doing a better automatic deployment, and how to create a service to auto restart the SpringBoot Application to refresh the new added databases to the datasource Map


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 url
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 Operating System
2 comments
Read more

How to do pagination SpringBoot with Jbctemplate, MySQL

We are going to be working on a topic which is a basic need when doing any app, and it is Pagination. let's get started creating a product table at https://mockaroo.com/ create table products ( id INT, name VARCHAR(50), code VARCHAR(50) ); insert into products (id, name, code) values (1, 'Hettinger-Goyette', '42549-680'); insert into products (id, name, code) values (2, 'Konopelski-Klein', '49527-724'); insert into products (id, name, code) values (3, 'Smitham, Kuhlman and Balistreri', '53238-003'); insert into products (id, name, code) values (4, 'Hettinger, Weissnat and Goodwin', '0143-9916'); insert into products (id, name, code) values (5, 'Rowe Inc', '42291-898'); insert into products (id, name, code) values (6, 'Ernser-Hauck', '10544-617'); insert into products (id, name, code) values (7, 'Maggio and Sons', '68788-9087'); insert into products (id, name,
Post a Comment
Read more