I am trying to get a grasp of how to accomplish multi-tenancy with Hibernate, Spring and MySQL. For a first playground example I chose the separate-databases approach: every tenant gets his own database which is named accodingly. Furthermore, another database is used to manage the tenants. The tenant-specific databases hold some staff data. For a first approach, I do not enforce authentication by the user.
I found it really hard to get a comprehensive tutorial on the topic which is why I am currently a bit lost. When I try to deploy Tomcat I get the following messages:
WARN : org.hibernate.engine.jdbc.connections.internal.MultiTenantConnectionProviderInitiator - Unable to instantiate specified class [sdb.persistence.TenantResolverImpl]
java.lang.ClassCastException: sdb.persistence.TenantResolverImpl cannot be cast to org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider
at org.hibernate.engine.jdbc.connections.internal.MultiTenantConnectionProviderInitiator.initiateService(MultiTenantConnectionProviderInitiator.java:100)
at org.hibernate.engine.jdbc.connections.internal.MultiTenantConnectionProviderInitiator.initiateService(MultiTenantConnectionProviderInitiator.java:45)
...
ERROR: org.springframework.web.servlet.DispatcherServlet - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'staffSessionFactory' defined in ServletContext resource [/WEB-INF/spring/appServlet/spring-data.xml]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to instantiate specified multi-tenant connection provider [sdb.persistence.TenantResolverImpl]
...
Caused by: org.hibernate.service.spi.ServiceException: Unable to instantiate specified multi-tenant connection provider [sdb.persistence.TenantResolverImpl]
at org.hibernate.engine.jdbc.connections.internal.MultiTenantConnectionProviderInitiator.initiateService(MultiTenantConnectionProviderInitiator.java:104)
at org.hibernate.engine.jdbc.connections.internal.MultiTenantConnectionProviderInitiator.initiateService(MultiTenantConnectionProviderInitiator.java:45)
This is the structure of my Java classes:
The xml configuration is split across three files. 1. servlet-context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:beans="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:annotation-config />
<beans:import resource="spring-data.xml" />
<beans:import resource="spring-mvc.xml" />
<context:component-scan base-package="sdb.controller" />
<context:component-scan base-package="sdb.data" />
<context:component-scan base-package="sdb.persistence" />
</beans:beans>
spring-data.xml:
<?xml version="1.0" encoding="UTF-8"?>
<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:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
<!-- connection to the database which holds tenant-management information -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/allTenants" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean id="tenantsSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
<property name="annotatedClasses">
<list>
<value>sdb.domain.Tenant</value>
</list>
</property>
</bean>
<bean id="tenantTransactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="tenantsSessionFactory" />
</bean>
<!-- not quite sure if this is right -->
<bean id="staffSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="packagesToScan" value="sdb.domain" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.default_schema">public</prop>
<prop key="hibernate.multiTenancy">SCHEMA</prop>
<prop key="hibernate.tenant_identifier_resolver">sdb.persistence.ConnectionProviderImpl</prop>
<prop key="hibernate.multi_tenant_connection_provider">sdb.persistence.TenantResolverImpl</prop>
</props>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="staffSessionFactory" />
</bean>
</beans>
spring-mvc.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
<mvc:default-servlet-handler />
<mvc:annotation-driven />
<mvc:resources mapping="/resources/**" location="/resources/" />
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix"><value>/WEB-INF/pages/</value></property>
<property name="suffix"><value>.jsp</value></property>
</bean>
</beans>
Now to the Java classes. My DAOs each have a session factory field:
@Repository
public class TenantDaoImpl implements TenantDao {
@Autowired
private SessionFactory tenantsSessionFactory;
...
@Repository
public class StaffDaoImpl implements StaffDao {
@Autowired
private SessionFactory staffSessionFactory;
The controller:
@Controller
public class HomeController {
@Autowired
StaffDao staffDao;
@RequestMapping(value = "/{companyName}/{staffId}", method = RequestMethod.GET)
public String google(Model model, @PathVariable String companyName, @PathVariable String staffName) {
List<Staff> staffs = staffDao.getStaff();
// Yeah, I know that this functionality should actually reside in the Dao or a service layer
Optional<Staff> foundStaff = staffs.stream().filter(staff -> staff.getSurName().equals(staffName)).findFirst();
if (foundStaff.isPresent()) {
model.addAttribute("foreName", foundStaff.get().getForeName());
model.addAttribute("surName", foundStaff.get().getSurName());
}
model.addAttribute("companyName", companyName);
return "staff";
}
}
Finally, the multi-tenancy related source code. TenantResolverImpl.java:
public class TenantResolverImpl implements CurrentTenantIdentifierResolver {
@Override
public String resolveCurrentTenantIdentifier() {
if (FacesContext.getCurrentInstance() != null) {
// I don't know if this will work - the line was copied from somewhere else
return FacesContext.getCurrentInstance().getExternalContext().getRequestServerName();
}
return null;
}
// Not sure what to do with this method...
@Override
public boolean validateExistingCurrentSessions() { return true; }
}
ConnectionProviderImpl.java:
public class ConnectionProviderImpl implements MultiTenantConnectionProvider {
private Map<String, ComboPooledDataSource> dataSources;
@Autowired
TenantDao tenantDao;
@Override
public Connection getAnyConnection() throws SQLException {
// should probably return the connection for 'dummy' or so
return getConnection("google");
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
if (tenantDao.containsTenantWithName(tenantIdentifier)) {
if (!dataSources.containsKey(tenantIdentifier)) {
ComboPooledDataSource dataSource = new ComboPooledDataSource("Example");
try {
dataSource.setDriverClass("com.mysql.jdbc.Driver");
} catch (PropertyVetoException e) {
e.printStackTrace();
return null;
}
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/" + tenantIdentifier);
dataSource.setUser("root");
dataSource.setPassword("root");
dataSources.put(tenantIdentifier, dataSource);
}
return dataSources.get(tenantIdentifier).getConnection();
}
return null;
}
...
}
Due to my (yet) superficial understanding of the technologies, I guess there are plenty of errors/shortcomings in the code. Feel free to criticize anything noteworthy.
