This is an example of SAAS based application in spring. Now a days trend of Software As A Service based applications are increasing day by day. An organization has more than one client and we provide a single solution to all client.

One Application connected with different database of same schema. Means each client has a different database and but schema(Table, Score Procedure) are same in each database, this is main concept of SAAS based application.

To identify each client we have TenantId each database have unique TenantId based on that database will be selected at runtime. We can store that TenantId in user session scope once user login so after login at every database operation we can identify the database.

Technology Used:

  1. String 4.3.0
  2. Maven 2.3.2
  3. Tomcat 7
  4. Java 8
  5. IntelljIDEA 14

Project Structure:

Dependency:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.springapp</groupId>
    <artifactId>spring-hibernate-xml-multi-tenancy-configuration</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>spring-hibernate-xml-multi-tenancy-configuration</name>
    <properties>
        <spring.version>4.3.1.RELEASE</spring.version>
        <hibernate.version>4.2.11.Final</hibernate.version>
        <mysql.connector.version>5.1.31</mysql.connector.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--Transaction API-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.1</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.connector.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>
    <build>
        <finalName>spring-hibernate-xml-multi-tenancy-configuration</finalName>
        <plugins>
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.4</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Java Code:

EmployeeController.java

package com.springdemo.controller;
import com.springdemo.model.Employee;
import com.springdemo.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
@Controller
@RequestMapping("/")
public class EmployeeController {
    @Autowired
    private EmployeeService employeeService;
    @RequestMapping(method = RequestMethod.GET)
    public String createEmployee(ModelMap model) {
        return "createEmployee";
    }
    @RequestMapping(value = "viewEmployee", method = RequestMethod.GET)
    public String viewEmployee(ModelMap model) {
        model.addAttribute("employees", employeeService.list());
        return "viewEmployee";
    }
    @RequestMapping(value = "saveEmployee", method = RequestMethod.POST)
    public String saveEmployee(Employee employee) {
        employeeService.save(employee);
        return "redirect:viewEmployee";
    }
}

EmployeeDAO.java

package com.springdemo.dao;
import com.springdemo.model.Employee;
import java.util.List;

public interface EmployeeDAO {
    public void save(Employee employee);
    public List<Employee> list();
}

EmployeeDAOImpl.java

package com.springdemo.dao;
import com.springdemo.model.Employee;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO {
    @Autowired
    SessionFactory sessionFactory;
    @Override
    public void save(Employee employee) {
        sessionFactory.getCurrentSession().save(employee);
    }
    @Override
    public List<Employee> list() {
        return sessionFactory.getCurrentSession().createCriteria(Employee.class).list();
    }
}

MasterService.java

Master service will return MAP which contains database sources, Key is tenentId and value is database source. we can also fatch all database connection from master database. Here we have set static two database only.

package com.springdemo.master;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
import java.util.HashMap;

public class MasterService {
    public static HashMap<String, DataSource> getDataSourceHashMap() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/demo_database");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/demo_database_1");
        dataSource1.setUsername("root");
        dataSource1.setPassword("");
        HashMap hashMap = new HashMap();
        hashMap.put("tenantId1", dataSource);
        hashMap.put("tenantId2", dataSource1);
        return hashMap;
    }
}

Employee.java

package com.springdemo.model;
import javax.persistence.*;

@Entity
@Table
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int employeeId;
    @Column
    private String employeeName;
    public String getEmployeeName() {
        return employeeName;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public int getEmployeeId() {
        return employeeId;
    }
    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }
}

CurrentTenantIdentifierResolverimpl.java

resolveCurrentTenantIdentifier method will be called before any database operation will perform. This method is used full to identify current TenentId. In this example we are getting current TenentId in URL but we can also take from session, Once user login we can store respective user tenentId in session and retrieve from session.

We can also store TenentId in ThreadLocal, This is usefull when we are performing any database operation from thread, because in thread content it difficult to get current request or session so it better option is that once thread start we can store TenentId in thread and access here.

package com.springdemo.multitenancy;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
    @Override
    public String resolveCurrentTenantIdentifier() {
        ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        String tenantId = attr.getRequest().getParameter("tenantId");
        return tenantId;
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

MultiTenantConnectionprovideImpl.java

After resolveCurrentTenantIdentifier method return TenentId spring will call selectDataSource method, Here we can access TenentId which is return by resolveCurrentTenantIdentifier method. selectDataSource method will return object of javax.sql.DataSource which contains information about database connection.

Now database operation will perform on return database source.

package com.springdemo.multitenancy;
import com.springdemo.master.MasterService;
import org.hibernate.service.jdbc.connections.spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import javax.sql.DataSource;

public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
    @Override
    protected DataSource selectAnyDataSource() {
        return MasterService.getDataSourceHashMap().get("tenantId1");
    }
    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return MasterService.getDataSourceHashMap().get(tenantIdentifier);
    }
}

EmployeeService.java

package com.springdemo.service;
import com.springdemo.model.Employee;
import java.util.List;

public interface EmployeeService {
    public void save(Employee employee);
    public List<Employee> list();
}

EmployeeServiceImpl.java

package com.springdemo.service;
import com.springdemo.dao.EmployeeDAO;
import com.springdemo.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class EmployeeServiceImpl implements EmployeeService {
    @Autowired
    private EmployeeDAO employeeDAO;
    @Override
    public void save(Employee employee) {
        employeeDAO.save(employee);
    }
    @Override
    public List<Employee> list() {
        return employeeDAO.list();
    }
}

resource\application.properties

jdbc.driverClassName = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/demo_database_1
jdbc.username = root
jdbc.password =
hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = false
hibernate.format_sql = false

pages\createEmployee.jsp

<html>
<body>
  <form action="saveEmployee" method="post">
        Name: <input type="text" name="employeeName">
        <input type="submit" value="Save">
    </form>
</body>
</html>

pages\viewEmployee.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<body>
    <h1>Employee List</h1>
    <table border="1">
        <tr>
            <th> Id
            <th> Name
        </tr>
  <c:forEach var="employee" items="${employees}">
        <tr>
            <td> <c:out value="${employee.employeeId}" />
            <td> <c:out value="${employee.employeeName}" />
        </tr>
    </c:forEach>
    </table>
</body>
</html>

Configurations:

mvc-dispatcher-servlet.xml

hibernate.multiTenancy

DATABASE indicate connection based on multiple database.

hibernate.tenant_identifier_resolver

CurrentTenantIdentifierResolverImpl class used of find current TenentId.

hibernate.multi_tenant_connection_provider

MultiTenantConnectionProviderImpl class return database connection based on current TenentId.

 

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
         http://www.springframework.org/schema/tx  http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="com.springdemo"/>
    <mvc:annotation-driven/>
    <context:property-placeholder location="classpath:application.properties"/>
    <tx:annotation-driven transaction-manager="transactionManager"/>
    <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" >
        <property name="packagesToScan">
            <list>
                <value>com.springdemo.model</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.show_sql">${hibernate.show_sql:false}</prop>
                <prop key="hibernate.format_sql">${hibernate.format_sql:false}</prop>
                <prop key="hibernate.multiTenancy">DATABASE</prop>
                <prop key="hibernate.tenant_identifier_resolver">com.springdemo.multitenancy.CurrentTenantIdentifierResolverImpl</prop>
                <prop key="hibernate.multi_tenant_connection_provider">com.springdemo.multitenancy.MultiTenantConnectionProviderImpl</prop>
            </props>
        </property>
    </bean>
    <bean id="transactionManager"  class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/pages/"/>
        <property name="suffix" value=".jsp"/>
    </bean>
</beans>

web.xml

<web-app version="2.4"
  xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
  http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
  <display-name>Spring MVC Application</display-name>
    <servlet>
    <servlet-name>mvc-dispatcher</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>mvc-dispatcher</servlet-name>
    <url-pattern>/</url-pattern>
  </servlet-mapping>
</web-app>

Output:

Passed : TenentId=tenent1

spring-hibernate-xml-multi-tenancy-configuration-2

spring-hibernate-xml-multi-tenancy-configuration-2

Passed : TenentId=tenent2

spring-hibernate-xml-multi-tenancy-configuration-2

spring-hibernate-xml-multi-tenancy-configuration-2

Download source code

spring-hibernate-xml-multi-tenancy-configuration

Was this post helpful?

14 comments. Leave new

Trying to do same but getting exception —
SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager’ defined in ServletContext resource [/WEB-INF/welcome-servlet.xml]: Invocation of init method failed; nested exception is java.lang.NullPointerException
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:703)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:403)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5118)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5634)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1571)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1561)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
at org.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:105)
at org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:335)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1612)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1549)
… 21 more
Please give some suggestion.

I think that you have problem with data source. Please check database bean properties.

404 not found i cant download the project

Hi sundar,

thank you for your message. We have updated our link. We hope that this blog will be too much helpfull to you.

William Hunt
March 4, 2018 3:32 pm

link to software download is not working

The source code link is broken..

cannot download the sourse 404 error. please send content folder link download link not working

Not work, error:
org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.HibernateException: SessionFactory configured for multi-tenancy, but no tenant identifier specified

The Save Employee form has an action ‘saveEmployee’ which does not add the ‘tenantId’ parameter as shown in the URL in the screenshots for the Employee lists view above. For this to work on save Employee, add a request parameter ‘tenantId=tenantId1’ to the form action and also add a RedirectAttribute to the saveEmployee method in the EmployeeController, before the redirect to the viewEmployee. In any case this is a basic example to show you how to do multi-tenancy. Actual implementations should use tenant IDs stored in HTTP session or ThreadLocal and get it form there, and not from the request parameters.

Yap, you are right, It’s a mistake.

For Tenant Id 1 Its working Fine, But When I am Trying it with tenantId2 then there is exception

Output images are not visible :(
Help me to find out, how URL should looks like.

Thank you. Now we correct it. looking fine now.

Oh great, working!!
thank you mate.

Notes:
– Change all databases credentials in MasterService.java and in application.properties.
– I have faced 2 problems:
1- Mysql version was not 5.1.31,
I fixed it by changing the version in pom.xml 5.1.31 I added my mysql version

2- after fixing the first problem i faced a new problem with my timezone I fixed it by adding some params (?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
) to the database url
jdbc:mysql://127.0.0.1:3306/yourDBName?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Leave a Reply

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