Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1

Question

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:

1. 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.

2. 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?

Rational

Any luck finding a way to Inject a data source? I'm struggling with this as well (using a c3p0 connection pool) – Spoonbill

Overriding the LocalSessionFactory with another implementation of LocalSessionFactoryBuilder & add multitenant properties? public LocalSessionFactoryBuilder(DataSource dataSource, ResourceLoader resourceLoader) { getProperties().put(Environment.CURRENT_SESSION_CONTEXT_CLASS, SpringSessionContext.class.getName()); if (dataSource != null) { getProperties().put(Environment.DATASOURCE, dataSource); } getProperties().put(AvailableSettings.APP_CLASSLOADER, resourceLoader.getClassLoader()); this.resourcePatternResolver = ResourcePatternUtils.getResourcePatternResolver(resourceLoader); } – Spoonbill

For now I opted for a solution using the properties. I think I should post the solution I have working at the moment. – Rational

Please vote for hibernate.atlassian.net/browse/HHH-8752 :) – Beisel

Answer 1

Ok to wrap this up, here is what I ended up with the following. I use a simple CurrentTenantIdentifierResolver. And Instead of trying to inject the DataSource from somewhere else to my MultiTenantConnectionProviderImpl I create the DataSource (c3p0 ComboPooledDatasource) in the ConnectionProvider and started using only the connections provided by the my ConnectionProvider. So I eliminated the extra DataSource. To make the properties of the DataSource easily configurable I opted to get the configuration data from a properties file.

CurrentTenantIdentifierResolverImpl:

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {


    /**
     * The method returns the RequestServerName as tenantidentifier.
     * If no FacesContext is available null is returned.
     * 
     * @return String tenantIdentifier
     */
    @Override
    public String resolveCurrentTenantIdentifier() {
        if (FacesContext.getCurrentInstance() != null){
            return FacesContext.getCurrentInstance().getExternalContext().getRequestServerName();
        } else {
            return null;
        }
    }

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

}

MultiTenantConnectionProviderImpl:

Note that the PropertyUtil is just a simple local helper class to fetch my properties. Since it is nothing special I won't include it to not clutter the answer.

public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider  {


    private static final long serialVersionUID = 8074002161278796379L;


    private static Logger log = LoggerFactory.getLogger(MultiTenantConnectionProviderImpl.class );

    private ComboPooledDataSource cpds;

    private Properties properties;

    /**
     * 
     * Constructor. Initializes the ComboPooledDataSource based on the config.properties.
     * 
     * @throws PropertyVetoException
     */
    public MultiTenantConnectionProviderImpl() throws PropertyVetoException {
        log.info("Initializing Connection Pool!");
        properties = new Properties();
        try {
            properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("config.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        cpds = new ComboPooledDataSource("Example");
        cpds.setDriverClass(properties.getProperty("jdbc.driver"));
        cpds.setJdbcUrl(properties.getProperty("jdbc.url"));
        cpds.setUser(properties.getProperty("jdbc.user"));
        cpds.setPassword(PropertyUtil.getCredential("jdbc.password"));
        log.info("Connection Pool initialised!");
    }


    @Override
    public Connection getAnyConnection() throws SQLException {
        log.debug("Get Default Connection:::Number of connections (max: busy - idle): {} : {} - {}",new int[]{cpds.getMaxPoolSize(),cpds.getNumBusyConnectionsAllUsers(),cpds.getNumIdleConnectionsAllUsers()});
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()){
            log.warn("Maximum number of connections opened");
        }
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize() && cpds.getNumIdleConnectionsAllUsers()==0){
            log.error("Connection pool empty!");
        }
        return cpds.getConnection();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        log.debug("Get {} Connection:::Number of connections (max: busy - idle): {} : {} - {}",new Object[]{tenantIdentifier, cpds.getMaxPoolSize(),cpds.getNumBusyConnectionsAllUsers(),cpds.getNumIdleConnectionsAllUsers()});
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()){
            log.warn("Maximum number of connections opened");
        }
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize() && cpds.getNumIdleConnectionsAllUsers()==0){
            log.error("Connection pool empty!");
        }
        return cpds.getConnection(tenantIdentifier, PropertyUtil.getCredential(tenantIdentifier));
    }

    @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) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        if ( isUnwrappableAs( unwrapType ) ) {
            return (T) this;
        }
        else {
            throw new UnknownUnwrapTypeException( unwrapType );
        }
    }
}

The c3p0 specific config is taken from the c3p0-config.xml:

<c3p0-config>
    <named-config name="Example">
        <property name="acquireIncrement">3</property>
        <property name="preferredTestQuery">SELECT 1</property>
        <property name="checkoutTimeout">2000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">1</property>
        <property name="maxIdleTime">18000</property>
        <property name="maxPoolSize">30</property>
        <property name="minPoolSize">1</property>
        <property name="maxStatements">50</property>
        <property name="testConnectionOnCheckin">true</property>
    </named-config>
</c3p0-config>

And the db specific properties are provided by a config.properties file:

jdbc.url=<serverUrl>
jdbc.driver=<driverClass>
jdbc.dbName=<dBname>
jdbc.dbowner=<dbo>
jdbc.username=<user>
jdbc.password=<password>

hibernate.dialect=<hibernateDialect>
hibernate.debug=false

The credentials are fetched in a similar fashion from another file.

Any feedback providing improvements is appreciated.

Rational

Answer 2

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.

Interdepartmental

Oh, I just noticed the OP is using plain Hibernate instead of JPA. Anyway, the solution is probably similar, using LocalSessionFactoryBean instead of LocalContainerEntityManagerFactoryBean. – Interdepartmental

Thanks for adding another variant to the answers. Yes the general solution is the same, but it is probably still good to have an outline for folks using JPA. – Rational

@Interdepartmental Thank you for this comment. However I cannot get this working because of one problem - spring JPA is initialized on startup - it means there is no request scope on URI resolver available. I have multiple databases types that's why my dialect is dynamic. Any hint please? I am struggling 2 days with this. – Aitch

@Aitch I can't really tell if you can make the dialect dynamic. Wrt the initialization problem, can't you just return a default tenant identifier if you can't find one (i.e. request.getRequestURI() throws an exception)? – Interdepartmental

Thanks, this helped a lot. One concern I have is that MultiTenantConnectionProvider is Serializable. Having DataSource autowired as a local member breaks the serialization contract. I don't know if this will be one of those hard to find bugs down the road if deployed in a more complex environment. I added a readObjectNoData() method to re-populate from the context, but that feels like a hack. – Discursive

And what about plain Hibernate? They have a setJpaProperties() taking a map of Object values on LocalContainerEntityManagerFactoryBean in JPA way. But I see no analog in LocalSessionFactoryBean in Hibernate way (there's only setHibernateProperties() taking Properties of String values). – Fimbria

@PavelS, have you tried setCurrentTenantIdentifierResolver and setMultiTenantConnectionProvider? – Interdepartmental

@Interdepartmental Thank you! I was using Spring 4.2.5 and somehow it has such methods for Hibernate 4 support, but only one of them for Hibernate 5... Since 4.3 both methods are in place. – Fimbria

Answer 3

The suggestion of using <map> instead of <props> seem to work for me. https://jira.springsource.org/browse/SPR-10823#comment-94855

 <bean id="multiTenantConnectionProvider"
       class="test.MultiTenantConnectionProviderImpl"/>


 <bean id="sessionFactory"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="packagesToScan" value="test.models" />
    <property name="hibernateProperties">
        <map>
            <entry key="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL82Dialect"/>
            <entry key="hibernate.multiTenancy" value="SCHEMA"/>
            <entry key="hibernate.tenant_identifier_resolver" value="test.CurrentTenantIdentifierResolverImpl"/>
            <entry key="hibernate.multi_tenant_connection_provider" value-ref="multiTenantConnectionProvider"/>
        </map>
    </property>
  </bean>

Isobaric

It also works with Java config + JPA: @Bean public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) { Map<String, Object> props = new HashMap<>(); props.put("hibernate.tenant_identifier_resolver", currentTenantIdentifierResolver()); props.put("hibernate.multi_tenant_connection_provider", multiTenantConnectionProvider()); props.put("hibernate.multiTenancy", "SCHEMA"); return builder.dataSource(arizonaDataSource()).packages(new String[] { /* ... */ }).properties(props).build(); } – Interdepartmental

@Interdepartmental - With JPA, I tried specifying the connection provider as a spring bean but I get an NPE (pastebin.com/E3RwNt1y) It seems that the AbstractEntityManagerFactoryBean.getObject() is called before AbstractEntityManagerFactoryBean.afterPropertiesSet(). Any idea what could be wrong? – Yi

Answer 4

As of Spring Framework version 3.2.4 there is no way to have the MultiTenantConnectionProvider and CurrentTenantIdentifierResolver managed by the Spring container. This creates many obstacles such as using an already configured DataSource, WebContext and other Spring managed beans and features. I have tried to find a cleaner solutions but came up with only one:

Extend org.springframework.orm.hibernate4.LocalSessionFactoryBuilder and write a custom LocalSessionFactoryBean (can't subclass and provide a LocalSessionFactoryBuilder, its basically a copy of the original with a tiny change)

Here goes:

package com.levitech.hibernate;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.core.io.ResourceLoader;

public class CustomLocalSessionFactoryBuilder extends org.springframework.orm.hibernate4.LocalSessionFactoryBuilder {


    public CustomLocalSessionFactoryBuilder(DataSource dataSource,ResourceLoader resourceLoader, MultiTenantConnectionProvider connectionProvider, 
            CurrentTenantIdentifierResolver tenantIdResolver) {
        super(dataSource, resourceLoader);
        getProperties().put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, connectionProvider);
        getProperties().put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, tenantIdResolver);

    }



}

The LocalSessionFactoryBean replacement (The only change is in the afterPropertiesSet() method to use the custom LocalSessionFactoryBuilder):

package com.levitech.hibernate;

/*
 * Copyright 2002-2013 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */



import java.io.File;
import java.io.IOException;
import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.Interceptor;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.NamingStrategy;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.DisposableBean;
import org.springframework.beans.factory.FactoryBean;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.ResourceLoaderAware;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternUtils;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.hibernate4.LocalSessionFactoryBuilder;

/**
 * {@link org.springframework.beans.factory.FactoryBean} that creates a Hibernate
 * {@link org.hibernate.SessionFactory}. This is the usual way to set up a shared
 * Hibernate SessionFactory in a Spring application context; the SessionFactory can
 * then be passed to Hibernate-based data access objects via dependency injection.
 *
 * <p><b>NOTE:</b> This variant of LocalSessionFactoryBean requires Hibernate 4.0 or higher.
 * It is similar in role to the same-named class in the {@code orm.hibernate3} package.
 * However, in practice, it is closer to {@code AnnotationSessionFactoryBean} since
 * its core purpose is to bootstrap a {@code SessionFactory} from annotation scanning.
 *
 * <p><b>NOTE:</b> To set up Hibernate 4 for Spring-driven JTA transactions, make
 * sure to either specify the {@link #setJtaTransactionManager "jtaTransactionManager"}
 * bean property or to set the "hibernate.transaction.factory_class" property to
 * {@link org.hibernate.engine.transaction.internal.jta.CMTTransactionFactory}.
 * Otherwise, Hibernate's smart flushing mechanism won't work properly.
 *
 * @author Juergen Hoeller
 * @since 3.1
 * @see #setDataSource
 * @see #setPackagesToScan
 * @see LocalSessionFactoryBuilder
 */
public class CustomLocalSessionFactoryBean extends HibernateExceptionTranslator
        implements FactoryBean<SessionFactory>, ResourceLoaderAware, InitializingBean, DisposableBean {


    private MultiTenantConnectionProvider multiTenantConnectionProvider;

    private CurrentTenantIdentifierResolver tenantIdResolver;

    private DataSource dataSource;

    private Resource[] configLocations;

    private String[] mappingResources;

    private Resource[] mappingLocations;

    private Resource[] cacheableMappingLocations;

    private Resource[] mappingJarLocations;

    private Resource[] mappingDirectoryLocations;

    private Interceptor entityInterceptor;

    private NamingStrategy namingStrategy;

    private Properties hibernateProperties;

    private Class<?>[] annotatedClasses;

    private String[] annotatedPackages;

    private String[] packagesToScan;

    private Object jtaTransactionManager;

    private ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();

    private Configuration configuration;

    private SessionFactory sessionFactory;





    public MultiTenantConnectionProvider getMultiTenantConnectionProvider() {
        return multiTenantConnectionProvider;
    }

    public void setMultiTenantConnectionProvider(
            MultiTenantConnectionProvider multiTenantConnectionProvider) {
        this.multiTenantConnectionProvider = multiTenantConnectionProvider;
    }

    public CurrentTenantIdentifierResolver getTenantIdResolver() {
        return tenantIdResolver;
    }

    public void setTenantIdResolver(CurrentTenantIdentifierResolver tenantIdResolver) {
        this.tenantIdResolver = tenantIdResolver;
    }

    /**
     * Set the DataSource to be used by the SessionFactory.
     * If set, this will override corresponding settings in Hibernate properties.
     * <p>If this is set, the Hibernate settings should not define
     * a connection provider to avoid meaningless double configuration.
     */
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * Set the location of a single Hibernate XML config file, for example as
     * classpath resource "classpath:hibernate.cfg.xml".
     * <p>Note: Can be omitted when all necessary properties and mapping
     * resources are specified locally via this bean.
     * @see org.hibernate.cfg.Configuration#configure(java.net.URL)
     */
    public void setConfigLocation(Resource configLocation) {
        this.configLocations = new Resource[] {configLocation};
    }

    /**
     * Set the locations of multiple Hibernate XML config files, for example as
     * classpath resources "classpath:hibernate.cfg.xml,classpath:extension.cfg.xml".
     * <p>Note: Can be omitted when all necessary properties and mapping
     * resources are specified locally via this bean.
     * @see org.hibernate.cfg.Configuration#configure(java.net.URL)
     */
    public void setConfigLocations(Resource[] configLocations) {
        this.configLocations = configLocations;
    }

    /**
     * Set Hibernate mapping resources to be found in the class path,
     * like "example.hbm.xml" or "mypackage/example.hbm.xml".
     * Analogous to mapping entries in a Hibernate XML config file.
     * Alternative to the more generic setMappingLocations method.
     * <p>Can be used to add to mappings from a Hibernate XML config file,
     * or to specify all mappings locally.
     * @see #setMappingLocations
     * @see org.hibernate.cfg.Configuration#addResource
     */
    public void setMappingResources(String[] mappingResources) {
        this.mappingResources = mappingResources;
    }

    /**
     * Set locations of Hibernate mapping files, for example as classpath
     * resource "classpath:example.hbm.xml". Supports any resource location
     * via Spring's resource abstraction, for example relative paths like
     * "WEB-INF/mappings/example.hbm.xml" when running in an application context.
     * <p>Can be used to add to mappings from a Hibernate XML config file,
     * or to specify all mappings locally.
     * @see org.hibernate.cfg.Configuration#addInputStream
     */
    public void setMappingLocations(Resource[] mappingLocations) {
        this.mappingLocations = mappingLocations;
    }

    /**
     * Set locations of cacheable Hibernate mapping files, for example as web app
     * resource "/WEB-INF/mapping/example.hbm.xml". Supports any resource location
     * via Spring's resource abstraction, as long as the resource can be resolved
     * in the file system.
     * <p>Can be used to add to mappings from a Hibernate XML config file,
     * or to specify all mappings locally.
     * @see org.hibernate.cfg.Configuration#addCacheableFile(java.io.File)
     */
    public void setCacheableMappingLocations(Resource[] cacheableMappingLocations) {
        this.cacheableMappingLocations = cacheableMappingLocations;
    }

    /**
     * Set locations of jar files that contain Hibernate mapping resources,
     * like "WEB-INF/lib/example.hbm.jar".
     * <p>Can be used to add to mappings from a Hibernate XML config file,
     * or to specify all mappings locally.
     * @see org.hibernate.cfg.Configuration#addJar(java.io.File)
     */
    public void setMappingJarLocations(Resource[] mappingJarLocations) {
        this.mappingJarLocations = mappingJarLocations;
    }

    /**
     * Set locations of directories that contain Hibernate mapping resources,
     * like "WEB-INF/mappings".
     * <p>Can be used to add to mappings from a Hibernate XML config file,
     * or to specify all mappings locally.
     * @see org.hibernate.cfg.Configuration#addDirectory(java.io.File)
     */
    public void setMappingDirectoryLocations(Resource[] mappingDirectoryLocations) {
        this.mappingDirectoryLocations = mappingDirectoryLocations;
    }

    /**
     * Set a Hibernate entity interceptor that allows to inspect and change
     * property values before writing to and reading from the database.
     * Will get applied to any new Session created by this factory.
     * @see org.hibernate.cfg.Configuration#setInterceptor
     */
    public void setEntityInterceptor(Interceptor entityInterceptor) {
        this.entityInterceptor = entityInterceptor;
    }

    /**
     * Set a Hibernate NamingStrategy for the SessionFactory, determining the
     * physical column and table names given the info in the mapping document.
     * @see org.hibernate.cfg.Configuration#setNamingStrategy
     */
    public void setNamingStrategy(NamingStrategy namingStrategy) {
        this.namingStrategy = namingStrategy;
    }

    /**
     * Set Hibernate properties, such as "hibernate.dialect".
     * <p>Note: Do not specify a transaction provider here when using
     * Spring-driven transactions. It is also advisable to omit connection
     * provider settings and use a Spring-set DataSource instead.
     * @see #setDataSource
     */
    public void setHibernateProperties(Properties hibernateProperties) {
        this.hibernateProperties = hibernateProperties;
    }

    /**
     * Return the Hibernate properties, if any. Mainly available for
     * configuration through property paths that specify individual keys.
     */
    public Properties getHibernateProperties() {
        if (this.hibernateProperties == null) {
            this.hibernateProperties = new Properties();
        }
        return this.hibernateProperties;
    }

    /**
     * Specify annotated entity classes to register with this Hibernate SessionFactory.
     * @see org.hibernate.cfg.Configuration#addAnnotatedClass(Class)
     */
    public void setAnnotatedClasses(Class<?>[] annotatedClasses) {
        this.annotatedClasses = annotatedClasses;
    }

    /**
     * Specify the names of annotated packages, for which package-level
     * annotation metadata will be read.
     * @see org.hibernate.cfg.Configuration#addPackage(String)
     */
    public void setAnnotatedPackages(String[] annotatedPackages) {
        this.annotatedPackages = annotatedPackages;
    }

    /**
     * Specify packages to search for autodetection of your entity classes in the
     * classpath. This is analogous to Spring's component-scan feature
     * ({@link org.springframework.context.annotation.ClassPathBeanDefinitionScanner}).
     */
    public void setPackagesToScan(String... packagesToScan) {
        this.packagesToScan = packagesToScan;
    }

    /**
     * Set the Spring {@link org.springframework.transaction.jta.JtaTransactionManager}
     * or the JTA {@link javax.transaction.TransactionManager} to be used with Hibernate,
     * if any.
     * @see LocalSessionFactoryBuilder#setJtaTransactionManager
     */
    public void setJtaTransactionManager(Object jtaTransactionManager) {
        this.jtaTransactionManager = jtaTransactionManager;
    }

    public void setResourceLoader(ResourceLoader resourceLoader) {
        this.resourcePatternResolver = ResourcePatternUtils.getResourcePatternResolver(resourceLoader);
    }


    public void afterPropertiesSet() throws IOException {
        LocalSessionFactoryBuilder sfb = new CustomLocalSessionFactoryBuilder(this.dataSource, this.resourcePatternResolver, multiTenantConnectionProvider, tenantIdResolver);

        if (this.configLocations != null) {
            for (Resource resource : this.configLocations) {
                // Load Hibernate configuration from given location.
                sfb.configure(resource.getURL());
            }
        }

        if (this.mappingResources != null) {
            // Register given Hibernate mapping definitions, contained in resource files.
            for (String mapping : this.mappingResources) {
                Resource mr = new ClassPathResource(mapping.trim(), this.resourcePatternResolver.getClassLoader());
                sfb.addInputStream(mr.getInputStream());
            }
        }

        if (this.mappingLocations != null) {
            // Register given Hibernate mapping definitions, contained in resource files.
            for (Resource resource : this.mappingLocations) {
                sfb.addInputStream(resource.getInputStream());
            }
        }

        if (this.cacheableMappingLocations != null) {
            // Register given cacheable Hibernate mapping definitions, read from the file system.
            for (Resource resource : this.cacheableMappingLocations) {
                sfb.addCacheableFile(resource.getFile());
            }
        }

        if (this.mappingJarLocations != null) {
            // Register given Hibernate mapping definitions, contained in jar files.
            for (Resource resource : this.mappingJarLocations) {
                sfb.addJar(resource.getFile());
            }
        }

        if (this.mappingDirectoryLocations != null) {
            // Register all Hibernate mapping definitions in the given directories.
            for (Resource resource : this.mappingDirectoryLocations) {
                File file = resource.getFile();
                if (!file.isDirectory()) {
                    throw new IllegalArgumentException(
                            "Mapping directory location [" + resource + "] does not denote a directory");
                }
                sfb.addDirectory(file);
            }
        }

        if (this.entityInterceptor != null) {
            sfb.setInterceptor(this.entityInterceptor);
        }

        if (this.namingStrategy != null) {
            sfb.setNamingStrategy(this.namingStrategy);
        }

        if (this.hibernateProperties != null) {
            sfb.addProperties(this.hibernateProperties);
        }

        if (this.annotatedClasses != null) {
            sfb.addAnnotatedClasses(this.annotatedClasses);
        }

        if (this.annotatedPackages != null) {
            sfb.addPackages(this.annotatedPackages);
        }

        if (this.packagesToScan != null) {
            sfb.scanPackages(this.packagesToScan);
        }

        if (this.jtaTransactionManager != null) {
            sfb.setJtaTransactionManager(this.jtaTransactionManager);
        }

        // Build SessionFactory instance.
        this.configuration = sfb;
        this.sessionFactory = buildSessionFactory(sfb);
    }

    /**
     * Subclasses can override this method to perform custom initialization
     * of the SessionFactory instance, creating it via the given Configuration
     * object that got prepared by this LocalSessionFactoryBean.
     * <p>The default implementation invokes LocalSessionFactoryBuilder's buildSessionFactory.
     * A custom implementation could prepare the instance in a specific way (e.g. applying
     * a custom ServiceRegistry) or use a custom SessionFactoryImpl subclass.
     * @param sfb LocalSessionFactoryBuilder prepared by this LocalSessionFactoryBean
     * @return the SessionFactory instance
     * @see LocalSessionFactoryBuilder#buildSessionFactory
     */
    protected SessionFactory buildSessionFactory(LocalSessionFactoryBuilder sfb) {
        return sfb.buildSessionFactory();
    }

    /**
     * Return the Hibernate Configuration object used to build the SessionFactory.
     * Allows for access to configuration metadata stored there (rarely needed).
     * @throws IllegalStateException if the Configuration object has not been initialized yet
     */
    public final Configuration getConfiguration() {
        if (this.configuration == null) {
            throw new IllegalStateException("Configuration not initialized yet");
        }
        return this.configuration;
    }


    public SessionFactory getObject() {
        return this.sessionFactory;
    }

    public Class<?> getObjectType() {
        return (this.sessionFactory != null ? this.sessionFactory.getClass() : SessionFactory.class);
    }

    public boolean isSingleton() {
        return true;
    }


    public void destroy() {
        this.sessionFactory.close();
    }

}

In your application context define the beans:

<bean id="multiTenantProvider" class="com.levitech.hibernate.MultiTenantConnectionProviderImpl" depends-on="myDataSource" lazy-init="false"></bean>
<bean id="tenantIdResolver" class="com.levitech.hibernate.TenantIdResolver"></bean>

<bean id="sessionFactory" class="com.levitech.hibernate.CustomLocalSessionFactoryBean" depends-on="liquibase, myDataSource, multiTenantProvider">
        <property name="dataSource" ref="myDataSource"></property>
        <property name="multiTenantConnectionProvider" ref="multiTenantProvider"></property>
        <property name="tenantIdResolver" ref="tenantIdResolver"></property>

         <property name="mappingLocations" value="classpath*:hibernate/**/*.hbm.xml" />

    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
        hibernate.show_sql=true
        hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
        hibernate.cache.use_query_cache=true
        hibernate.cache.use_second_level_cache=true
        hibernate.multiTenancy=SCHEMA
          </value>
    </property>
      </bean>

Do NOT provide values for the following in your hibernate properties: hibernate.tenant_identifier_resolver and hibernate.multi_tenant_connection_provider

You are all set and all your beans are Spring managed. You are free to use DI again! Hope this helps someone. I did put in a Jira request for the feature..

Spoonbill

Could you provide your implementation of MultiTenantConnectionProvider and TenantIdResolver? I see you reference it on spring configuration. I'm trying to make it work with oracle, in a proof of concept, will post all code later on. Multi-tenancy is still a bit scary with spring+hibernate. – Graces

Answer 5

Using these guys' responses and this link, I put this together without Spring or anything else but C3P0.

I had to add these 2 properties to my hibernate config

properties.setProperty("hibernate.multiTenancy", "SCHEMA");
properties.setProperty("hibernate.multi_tenant_connection_provider", MultiTenantConnectionProviderImpl.class.getName());

HibernateUtils.java

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Alex
 */
public class HibernateUtils {
    private static final Logger logger = LoggerFactory.getLogger(HibernateUtils.class);
    private static SessionFactory sessionFactory;

    static{
        init();
    }

    public static void init(){
        try {
            Configuration configuration = new Configuration()
                    .setProperties(ConnectionPropertiesUtils.getProperties());

            ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
            sessionFactory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }

    public static Session getTenantSession(String tenant){
        return getSession(tenant);
    }

    public static Session getAuthSession(){
        return getSession("AUTH");
    }

    public static Session getLogSession(){
        return getSession("LOG");
    }

    public static Session getConfigSession(){
        return getSession("CONFIG");
    }

    public static Session getSession(String tenant)
            throws HibernateException {
        if(sessionFactory == null){
            init();
        }
        return sessionFactory.withOptions().tenantIdentifier(tenant).openSession();
    }

    @Deprecated
    public static Session getSession()
            throws HibernateException {
        if(sessionFactory == null){
            init();
        }
        return sessionFactory.openSession();
    }
}

And MultiTenantConnectionProviderImpl.java

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import org.hibernate.HibernateException;
import org.hibernate.service.UnknownUnwrapTypeException;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.Stoppable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Simplistic implementation for illustration purposes showing a single
 * connection pool used to serve multiple schemas using "connection altering".
 * Here we use the T-SQL specific USE command; Oracle users might use the ALTER
 * SESSION SET SCHEMA command; etc.
 */
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, Stoppable {

    private static Logger log = LoggerFactory.getLogger(MultiTenantConnectionProviderImpl.class);
    private ComboPooledDataSource cpds;

    public MultiTenantConnectionProviderImpl() throws PropertyVetoException {
        log.info("Initializing Connection Pool!");

        cpds = new ComboPooledDataSource("Example");
        cpds.setDriverClass(ConnectionPropertiesUtils.getProperty("hibernate.connection.driver_class"));
        cpds.setJdbcUrl(ConnectionPropertiesUtils.getProperty("hibernate.connection.url"));
        cpds.setUser(ConnectionPropertiesUtils.getProperty("hibernate.connection.username"));
        cpds.setPassword(ConnectionPropertiesUtils.getProperty("hibernate.connection.password"));

        log.info("Connection Pool initialised!");
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        log.debug("Get Default Connection:::Number of connections (max: busy - idle): {} : {} - {}", new int[]{cpds.getMaxPoolSize(), cpds.getNumBusyConnectionsAllUsers(), cpds.getNumIdleConnectionsAllUsers()});
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()) {
            log.warn("Maximum number of connections opened");
        }
        if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize() && cpds.getNumIdleConnectionsAllUsers() == 0) {
            log.error("Connection pool empty!");
        }
        return cpds.getConnection();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        final Connection connection = getAnyConnection();
        try {
            //This is DB specific syntax. This work for MSSQL and MySQL
            //Oracle uses the ALTER SESSION SET SCHEMA command
            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 releaseAnyConnection(Connection connection) throws SQLException {
        connection.close();
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) {
        try {
            this.releaseAnyConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return ConnectionProvider.class.equals(unwrapType) || MultiTenantConnectionProvider.class.equals(unwrapType) || MultiTenantConnectionProviderImpl.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 void stop() {
        cpds.close();
    }
}

Sirree

Alex what is ConnectionPropertiesUtils? – Diluent

That's just a helper class I have to get my Hibernate properties. When I call getProperties(), its just returning a properties instance. When I call getProperty(), its returning a string – Sirree