For persistence layer multi-tenant design offers a few options:
http://msdn.microsoft.com/en-us/library/aa479086.aspx
- separate database per tenant
- separate schema, same database per tenant
- 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
- manually set the tenant identifier on hibernate session factory
- 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
That's it people,
Happy multi-tenanting :-)
Cheers!
