Database multi-tenancy using Hibernate 4

With many business applications being written, with intent to be deployed on cloud, there is increasingly a need to design multi-tenancy into your application.


For persistence layer multi-tenant design offers a few options:
http://msdn.microsoft.com/en-us/library/aa479086.aspx

1. separate database per tenant
2. separate schema, same database per tenant
3. same schema, same database for all tenant with discriminator column for tenant rows in same table

In this article, I am going to talk more about implementing the options 1 and 2 using the support in hibernate 4, for multi-tenancy.

Also for the database connections, we have choice of having a common database connection pool, from which connections are allocated to each tenant request, or separate connection pools for each tenant.
For the common connection pool approach, each connection before being used for data access, needs to be "primed" for usage against a tenant database or schema, using statements like "use <tenant_schema>" or "use <tenant_database>"

Multi-tenancy in Hibernate

For the "separate connection pool per tenant approach"

First we need to implement a custom connection provider as follows

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;

public class ConnectionProviderImpl implements ConnectionProvider {
 
 private final BasicDataSource basicDataSource = new BasicDataSource();
 
 public ConnectionProviderImpl(String database){

                //this should be read from properties file
  basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
  basicDataSource.setUrl("jdbc:mysql://localhost:3306/"+database);
  basicDataSource.setUsername("myuser");
  basicDataSource.setPassword("mypassword");
  basicDataSource.setInitialSize(2);
  basicDataSource.setMaxTotal(10);
 }

 @Override
 public boolean isUnwrappableAs(Class arg0) {
  return false;
 }

 @Override
 public  T unwrap(Class arg0) {
  return null;
 }

 @Override
 public void closeConnection(Connection arg0) throws SQLException {
  arg0.close();
 }

 @Override
 public Connection getConnection() throws SQLException {
  return basicDataSource.getConnection();
 }

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

}

Next we need to implement AbstractMultiTenantConnectionProvider of hibernate, as follows
Here I am maintaining a map of database identifiers against, connection providers.
When hibernate invokes the selectConnectionProvider( ) method with the tenant identifier, we use the tenant identifier to return the "appropriate" connection provider from the map.
It is necessary to implement a method like getAnyConnectionProvider( ), which should return, a sensible - default connection provider.

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;

public class MultiTenantConnectionProvider extends AbstractMultiTenantConnectionProvider {
 
 private HashMap connProviderMap = new HashMap();
 
 public MultiTenantConnectionProvider(){
  

  List providerNames = new ArrayList();
  providerNames.add("default_db");
  providerNames.add("db1");
  providerNames.add("db2");
  //need to get above from properties file
  
    for (String providerName : providerNames) {
       connProviderMap.put(providerName, new ConnectionProviderImpl(providerName));
    }
  
 }
 
 @Override
 protected ConnectionProvider getAnyConnectionProvider() {
  System.out.println("inside MultiTenantConnectionProvider::getAnyConnectionProvider");
  return connProviderMap.get("default_db");
 }

 @Override
 protected ConnectionProvider selectConnectionProvider(String tenantId) {
  ConnectionProvider connectionProvider = connProviderMap.get(tenantId);
  if(connectionProvider == null)
   connectionProvider =  new ConnectionProviderImpl("default_db");
  
  return connectionProvider;
 } 

}

For common connection pool for all tenants.

We need to implement MultiTenantConnectionProvider of hibernate, as follows
Since we are using the same connection pool, in getConnection(), we need to prime the connection using a SQL statement like 'use <database>', so that the connection's further use will be in database as per tenant_id.
Also in releaseConnection( ), we have a 'use <default_db>' as a fallback.

import java.sql.Connection;
import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;

public class MultiTenantConnectionProviderWithSingleDBPool implements
  MultiTenantConnectionProvider {
 
 private final ConnectionProvider connectionProvider = new ConnectionProviderImpl(CurrentTenantIdentifierResolver.DEFAULT_TENANT_ID); 
 

 @Override
 public boolean isUnwrappableAs(Class arg0) {
  return false;
 }

 @Override
 public  T unwrap(Class arg0) {
  return null;
 }

 @Override
 public Connection getAnyConnection() throws SQLException {
  System.out.println("inside MultiTenantConnectionProvider::getAnyConnection");
  return connectionProvider.getConnection();
 }

 @Override
 public void releaseAnyConnection(Connection connection) throws SQLException {
  connectionProvider.closeConnection( connection );
 }

 @Override
 public Connection getConnection(String tenantIdentifier) throws SQLException {
  final Connection connection = getAnyConnection();
  try {
     connection.createStatement().execute( "USE " + tenantIdentifier );
  }
  catch ( SQLException e ) {
     throw new HibernateException(
       "MultiTenantConnectionProvider::Could not alter JDBC connection to specified schema [" +tenantIdentifier + "]",e);
  }
  return connection;
 }

 @Override
 public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
  try {
   connection.createStatement().execute( "USE default_db" );
  }
  catch ( SQLException e ) {
     throw new HibernateException(
     "Could not alter JDBC connection to specified schema [" +
   tenantIdentifier + "]",e);
  }
  connectionProvider.closeConnection( connection );
 }

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

}

refer hibernate docs
http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html



Now, to persuade hibernate to use tenant identifier before any database access, we can

1. manually set the tenant identifier on hibernate session factory
2. we can implement hibernate tenant identifier resolver

Manually set the tenant identifier on hibernate session factory
This can be done, in a web application in any suitable interceptor where hibernate session is available

Session session = sessionFactory.withOptions()
        .tenantIdentifier( yourTenantIdentifier )
        ...
        .openSession();

Hibernate tenant identifier resolver can be implemented as follows.
Here I am using a ThreadLocal to get the tenant identifier. The threadlocal itself can be set early on in the http request thread in say, a servlet filter.

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;

public class CurrentTenantIdentifierResolver implements
  CurrentTenantIdentifierResolver {
 
 public static ThreadLocal _tenantIdentifier = new ThreadLocal();
 public static String DEFAULT_TENANT_ID = "default_db";

 @Override
 public String resolveCurrentTenantIdentifier() {
  System.out.println("from inside resolveCurrentTenantIdentifier....");
  String tenantId = _tenantIdentifier.get();
  if(tenantId == null)
   tenantId = DEFAULT_TENANT_ID;
  
  System.out.println("threadlocal tenant id ="+tenantId);
  return tenantId;
 }

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

}

Finally, the hibernate configuration for specifying that we are using hibernate mutli-tenancy, in hibernate.cfg.xml

<property name="hibernate.multiTenancy">DATABASE</property> <property name="hibernate.multi_tenant_connection_provider"> com.some.MyMultiTenantConnectionProvider </property> <property name="hibernate.tenant_identifier_resolver"> com.some.MyCurrentTenantIdentifierResolver </property>

That's it people,
Happy multi-tenanting :-)

Cheers!