Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1
I am currently trying to set up Hibernate for multi tenancy using the seperate Schema aproach.
After working on it for about 2 days now and browsing nearly every source I could find via Google I am starting to get quite frustrated.
Basicaly I am trying to follow the guide provided in the Hibernate devguide http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html_single/#d5e4691
But unfortunately I am not able to find the ConnectionProviderUtils to build the ConnectionProvider.
Currently I am trying to figure out 2 Points:
Why the configure(Properties props) method of my MSSQLMultiTenantConnectionProvider is never called. From what I interpreted from the source of and description of different other ConnectionProvider implementions I am assuming this method is going to be called to initialize the ConnectionProvider.
Since I am not able to work with the configure(Properties props) I tried out other approaches of somehow obtaining the hibernate properties and DataSource specified in the application Context and the hibernate.cfg.xml. (Like injecting the datasource directly into the ConnectionProvider)
Any pointers to possible ways to solve this (Methods, Classes, Tutorials)
So here are the relevant parts of my implementation:
Data Source and Hibernate.cfg.xml:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="url" value="jdbc:sqlserver://<host>:<port>;databaseName=<DbName>;" />
<property name="username" value=<username> />
<property name="password" value=<password> />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<!-- property name="dataSource" ref="dataSource" /-->
<property name="annotatedClasses">
<list>
<value>c.h.utils.hibernate.User</value>
<value>c.h.utils.hibernate.Role</value>
<value>c.h.utils.hibernate.Tenant</value>
</list>
</property>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.show_sql=true
hibernate.multiTenancy=SCHEMA
hibernate.tenant_identifier_resolver=c.h.utils.hibernate.CurrentTenantIdentifierResolver
hibernate.multi_tenant_connection_provider=c.h.utils.hibernate.MSSQLMultiTenantConnectionProviderImpl
</value>
</property>
</bean>
MSSQLMultiTenantConnectionProviderImpl:
package c.hoell.utils.hibernate;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.hibernate.service.UnknownUnwrapTypeException;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class MSSQLMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
private static final long serialVersionUID = 8074002161278796379L;
@Autowired
private DataSource dataSource;
public void configure(Properties props) throws HibernateException {
}
@Override
public Connection getAnyConnection() throws SQLException {
Properties properties = getConnectionProperties(); //method which sets the hibernate properties
DriverManagerConnectionProviderImpl defaultProvider = new DriverManagerConnectionProviderImpl();
defaultProvider.configure(properties);
Connection con = defaultProvider.getConnection();
ResultSet rs = con.createStatement().executeQuery("SELECT * FROM [schema].table");
rs.close(); //the statement and sql is just to test the connection
return defaultProvider.getConnection();
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
<--not sure how to implement this-->
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
connection.close();
}
@Override
public void releaseConnection(String tenantIdentifier, Connection connection){
try {
this.releaseAnyConnection(connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public boolean supportsAggressiveRelease() {
return false;
}
@Override
public boolean isUnwrappableAs(Class unwrapType) {
return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MSSQLMultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
}
@SuppressWarnings("unchecked")
@Override
public <T> T unwrap(Class<T> unwrapType) {
if ( isUnwrappableAs( unwrapType ) ) {
return (T) this;
}
else {
throw new UnknownUnwrapTypeException( unwrapType );
}
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
Right now there are 2 possible approaches I see to obtaint the configurations i need from the config files. Either get the configure() method to run or somehow make the injection of the DataSource possible. I guess the first one would be the better way.
An important thing to mention is that I had Hibernate up and running for only one tenant (means without using the MultiTenantConnectionProvider, using the standard ConnectionProvider used by Hibernate)
Already a big thanks to anyone who is reading this post. Looking forward to the answers.
Best regards
Update 1:
I have played around with this a bit and hardcoded the connectiondetails into my MultiTenantConnectionProvider (updated the Code above). This is working fine in regards to the MultiTenantConnectionProvider. But this is still not solving my problems. Now my Application fails at initializing the Transaction Manager:
<tx:annotation-driven transaction-manager="txManager" proxy-target-class="true"/>
<bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
This is the top of the exception stacktrace:
Caused by: java.lang.NullPointerException at org.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:101) at org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:264) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1514) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452)
I traced this issue down in debug mode and found out that the problem is that my SessionFactory is somehow not getting hold of the DataSource. (It makes no difference whether I specify the DataSource in the hibernate.cfg.xml or not) But when initializing the TransactionManager it tries to get the DataSource from the SessionFactory and fails with a NullPointerException as a result. Does anyone have an hint at what point of the inner workings of hibernate this is failing? In all the documentation and posts I have seen there was no indication that I need to handle the injection of the DataSource into the SessionFactory. For now I just guess I try to figure out how to get a DataSource into the needed place or how to change the initializing flow. If anyone has a better idea I would be really happy.
Edit: Also posted this in the Hibernate Forums now:
Update 2:
So I managed to get around this issue by setting the autodetectDataSource property in the TransactionManager to false:
<property name="autodetectDataSource" value="false"/>
I got this hint from the following post http://forum.springsource.org/showthread.php?123478-SessionFactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified. Unfortunately I am now stuck at exactly that issue. ^^" But this is a problem for another topic. (Edit: Turns out this was only misconfiguration from earlier testing + one old dependency)
As for this topic the problem remains that I want to somehow be able to reuse the DataSource, which I already have in the configuration for the use of Spring Security anyway, for Hibernate to avoid the need for having to configure the DataSource in two places. So the question still stands how to integrate the use of the DataSource in my MultiTenantConnectionProvider. Does anyone have an idea on where to find any hints on that?
Answer
According to Steve Ebersole's comments on the JIRA issue referred by one of this question's commenters (HHH-8752):
Well first, it is simply not true that Hibernate "instantiates the classes referred by ... MULTI_TENANT_CONNECTION_PROVIDER and MULTI_TENANT_IDENTIFIER_RESOLVER". Hibernate first tries to treat these settings as objects of their intended types, (MultiTenantConnectionProvider for MULTI_TENANT_CONNECTION_PROVIDER and CurrentTenantIdentifierResolver for MULTI_TENANT_IDENTIFIER_RESOLVER.
So just pass your beans in directly, configured however you want.
I just followed his suggestion and managed to make it work.
This is a CurrentTenantIdentifierResolver defined as a Spring Bean:
@Component
@Scope(value = "request", proxyMode = ScopedProxyMode.TARGET_CLASS)
public class RequestURITenantIdentifierResolver implements CurrentTenantIdentifierResolver {
@Autowired
private HttpServletRequest request;
@Override
public String resolveCurrentTenantIdentifier() {
String[] pathElements = request.getRequestURI().split("/");
String tenant = pathElements[1];
return tenant;
}
@Override
public boolean validateExistingCurrentSessions() {
return true;
}
}
This is a MultiTenantConnectionProvider defined as a Spring Bean:
@Component
public class SchemaPerTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
@Autowired
private DataSource dataSource;
@Override
public Connection getAnyConnection() throws SQLException {
return dataSource.getConnection();
}
@Override
public void releaseAnyConnection(final Connection connection) throws SQLException {
connection.close();
}
@Override
public Connection getConnection(final String tenantIdentifier) throws SQLException {
final Connection connection = getAnyConnection();
try {
connection.createStatement().execute("USE " + tenantIdentifier);
} catch (SQLException e) {
throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",
e);
}
return connection;
}
@Override
public void releaseConnection(final String tenantIdentifier, final Connection connection) throws SQLException {
try {
connection.createStatement().execute("USE dummy");
} catch (SQLException e) {
// on error, throw an exception to make sure the connection is not returned to the pool.
// your requirements may differ
throw new HibernateException(
"Could not alter JDBC connection to specified schema [" +
tenantIdentifier + "]",
e
);
} finally {
connection.close();
}
}
@Override
public boolean supportsAggressiveRelease() {
return true;
}
@Override
public boolean isUnwrappableAs(Class aClass) {
return false;
}
@Override
public <T> T unwrap(Class<T> aClass) {
return null;
}
}
And finally, this is a LocalContainerEntityManagerFactoryBean wired to make use of the two components above:
@Configuration
public class HibernateConfig {
@Bean
public JpaVendorAdapter jpaVendorAdapter() {
return new HibernateJpaVendorAdapter();
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
MultiTenantConnectionProvider multiTenantConnectionProvider,
CurrentTenantIdentifierResolver tenantIdentifierResolver) {
LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
emfBean.setDataSource(dataSource);
emfBean.setPackagesToScan(VistoJobsApplication.class.getPackage().getName());
emfBean.setJpaVendorAdapter(jpaVendorAdapter());
Map<String, Object> jpaProperties = new HashMap<>();
jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT,
MultiTenancyStrategy.SCHEMA);
jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER,
multiTenantConnectionProvider);
jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER,
tenantIdentifierResolver);
emfBean.setJpaPropertyMap(jpaProperties);
return emfBean;
}
}
The data source I'm using is made available automatically by Spring Boot.
I hope this helps.