how can we build a spring mvc multitenant application using hibernate as orm separate database and schema

Question

from spring io u shifted to stack overflow but nowhere u have explained about multitenant complete demo application which can be seen as a problem solver infact no one seen satisfied with ur answers can u please now share a working demo of multitenant schema and separate database complete code anywhere where people can use it kindly

package com.domain.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;
    }
}

package com.domain.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;
    }
}



package com.domain.multitenancy;

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

public class MultiTenantConnectionprovideImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    @Override
    protected DataSource selectAnyDataSource() {
        return MasterService.getDataSourceHashMap().get("tenantId1");
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return MasterService.getDataSourceHashMap().get(tenantIdentifier);
    }
}

package com.domain.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/multiten");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/multiten_1");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");

        HashMap hashMap = new HashMap();
        hashMap.put("tenantId1", dataSource);
        hashMap.put("tenantId2", dataSource1);
        return hashMap;
    }
}

<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.domain"/>
    <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.domain.model</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
                <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.domain.multitenancy.CurrentTenantIdentifierResolverimpl</prop>
                <prop key="hibernate.multi_tenant_connection_provider">com.domain.multitenancy.MultiTenantConnectionprovideImpl</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/views/"/>
        <property name="suffix" value=".jsp"/>
    </bean>
</beans>

 #Application.properties file in classpath
jdbc.driverClassName = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/multiten
jdbc.username = root
jdbc.password = root
hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
hibernate.show_sql = true
hibernate.format_sql = false

I have taken this example

these are the errors i got:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is 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 org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979) org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869) javax.servlet.http.HttpServlet.service(HttpServlet.java:660) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

Root Cause 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 org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:544) org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) com.sun.proxy.$Proxy22.save(Unknown Source) com.domain.controller.EmployeeController.saveEmployee(EmployeeController.java:35) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) java.lang.reflect.Method.invoke(Unknown Source) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869) javax.servlet.http.HttpServlet.service(HttpServlet.java:660) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

Root Cause org.hibernate.HibernateException: SessionFactory configured for multi-tenancy, but no tenant identifier specified org.hibernate.internal.AbstractSessionImpl.(AbstractSessionImpl.java:85) org.hibernate.internal.SessionImpl.(SessionImpl.java:239) org.hibernate.internal.SessionFactoryImpl$SessionBuilderImpl.openSession(SessionFactoryImpl.java:1618) org.hibernate.internal.SessionFactoryImpl.openSession(SessionFactoryImpl.java:978) org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:436) org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) com.sun.proxy.$Proxy22.save(Unknown Source) com.domain.controller.EmployeeController.saveEmployee(EmployeeController.java:35) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) java.lang.reflect.Method.invoke(Unknown Source) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869) javax.servlet.http.HttpServlet.service(HttpServlet.java:660) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

Note The full stack trace of the root cause is available in the server logs.

Answer 1

Session for transaction; nested exception is org.hibernate.HibernateException: SessionFactory configured for multi-tenancy, but no tenant identifier specified

The above error suggests that tenant identifier is not specified. Please double check your configuration and the identifier class implementation name.

The reason you are getting this error is that your tenant identifier resolver needs to return something default in case it cannot find appropriate tenant id from the strategy you have chosen ( in your case you are trying to get the tenant id from request params). I have used the updated code below and it is no longer showing the error.

public class CurrentTenantIdentifierResolverimpl implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        String tenantId = attr.getRequest().getParameter("tenantId");
        if (tenantId==null) {
            //return default tenant
            return "tenantId1";
        }
        return tenantId;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

Answer 2

Shailendra sir how can we set a tenant id to it without setting default id and get the username from spring security and pass it to the tenant id