Multitenancy in Hibernate (with Hibernate 4.3.7 and PostgreSql 9.3.1)

hibernate.cfg.xml

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

	<session-factory>

		<!-- Database connection settings -->
		<property name="connection.url">jdbc:postgresql://localhost:5432/sampledatabase</property>
		<property name="connection.driver_class">org.postgresql.Driver</property>
		<property name="connection.username">postgres</property>
		<property name="connection.password"></property>
		<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
		
		<!-- SQL dialect -->
		<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

		<!-- Echo all executed SQL to stdout -->
		<property name="show_sql">true</property>

		<!-- Enable Hibernate's automatic session context management -->
		<property name="current_session_context_class">thread</property>

		<!-- Drop and re-create the database schema on startup 
		<property name="hbm2ddl.auto">none</property>-->
		
		<property name="hibernate.multiTenancy">SCHEMA</property>
		<property name="hibernate.tenant_identifier_resolver">com.mt.SchemaResolver</property>
		<property name="hibernate.multi_tenant_connection_provider">com.mt.MultiTenantProvider</property>
 
		
		<mapping resource="com/mt/sampleapp.hbm.xml"/>

	</session-factory>

</hibernate-configuration>        

HibernateUtil.java

package com.mt;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

	private static final SessionFactory sessionFactory;

	static {
		try {
			// Create the SessionFactory from hibernate.cfg.xml
			sessionFactory = new Configuration().configure().buildSessionFactory();
		} catch (Throwable ex) {
			// Make sure you log the exception, as it might be swallowed
			System.err.println("Initial SessionFactory creation failed." + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}

	public static SessionFactory getSessionFactory() {
		return sessionFactory;
	}

}

MultiTenantProvider.java

package com.mt;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
 
import org.hibernate.HibernateException;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.c3p0.internal.C3P0ConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
 
public class MultiTenantProvider implements MultiTenantConnectionProvider, ServiceRegistryAwareService  {
 
	private static final long serialVersionUID = 4368575201221677384L;
	
	private C3P0ConnectionProvider connectionProvider = null;
 
	@Override
	public boolean supportsAggressiveRelease() {
		return false;
	}
 
	@Override
	public void injectServices(ServiceRegistryImplementor serviceRegistry) {
		Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
		
		connectionProvider = new C3P0ConnectionProvider();
		connectionProvider.injectServices(serviceRegistry);
		connectionProvider.configure(lSettings);
	}
 
	@Override
	public boolean isUnwrappableAs(Class clazz) {
		return false;
	}
 
	@Override
	public <T> T unwrap(Class<T> clazz) {
		return null;
	}
 
	@Override
	public Connection getAnyConnection() throws SQLException {
		final Connection connection = connectionProvider.getConnection();
		return connection;
	}
 
	@Override
	public Connection getConnection(String tenantIdentifier) throws SQLException {
		final Connection connection = getAnyConnection();
		try {
			connection.createStatement().execute("SET SCHEMA '" + 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 {
		try {
			connection.createStatement().execute("SET SCHEMA 'public'");
		}
		catch (SQLException e) {
			throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
		}
		connectionProvider.closeConnection(connection);
	}
 
	@Override
	public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
		releaseAnyConnection(connection);
	}
}

sampleapp.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.mt">
	<class name="com.mt.SampleDetails" table="sampletable">
		<id name="id" column="id">
			<generator class="increment"/>
		</id>
		<property name="name" type="string" column="name"/>
	</class>
</hibernate-mapping>

SampleApp.java

package com.mt;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Session;
import org.hibernate.Query;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
public class SampleApp{
	public static void main(String args[]){
		SampleAppMethods bm = new SampleAppMethods();

		//Listing 
		//bm.listBooks();
		bm.getSampleDetails("sampleschema1");
		bm.getSampleDetails("sampleschema2");
	}
}

class SampleAppMethods{
	
	
	public void getSampleDetails(String schemaname){
		//Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		Session session = HibernateUtil.getSessionFactory().withOptions().tenantIdentifier( schemaname ).openSession();
		
		
		session.beginTransaction();
		Query q = session.createQuery("from SampleDetails");
		List list = q.list();
		System.out.println("Getting 1. Details using HQL. \n"+list);

		//The above query can also be achieved with Criteria & Restrictions API.
		Criteria cri = session.createCriteria(SampleDetails.class);
		list = cri.list();
		System.out.println("Getting 2. Details using HQL. \n"+list);

		session.getTransaction().commit();
		System.out.println("End of Result");
		session.close();
	}

	
	//Native SQL Query
	public void listBooks(){
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		List list = session.createSQLQuery("select * from sampleschema1.sampletable").addEntity(SampleDetails.class).list();
		Iterator itr = list.iterator();
		while(itr.hasNext()){
			SampleDetails sd = (SampleDetails)itr.next();
			System.out.println(sd);
		}
		session.getTransaction().commit();

	}
}

SampleDetails.java

package com.mt;

public class SampleDetails{
	private int id;
	private String name;
	
	SampleDetails(){
	}

	
	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}


	public String getName() {
		return name;
	}


	public void setName(String name) {
		this.name = name;
	}


	public String toString(){
		StringBuffer sb = new StringBuffer();
		sb.append("name : ").append(name);
		sb.append(" ,id : ").append(id);
		return sb.toString();
	}
}

SchemaResolver.java

package com.mt;

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;

public class SchemaResolver implements CurrentTenantIdentifierResolver {
 
	@Override
	public String resolveCurrentTenantIdentifier() {
		return "sampleschema1"; //TODO: Implement service to identify tenant like: userService.getCurrentlyAuthUser().getTenantId();
	}
 
	@Override
	public boolean validateExistingCurrentSessions() {
		return false;
	}
}

Solution Summery

This is a complete working sample for Multitenancy in Hibernate and PostGreSql.

Our approach towards multitenancy is - to have separate schema for each tenant in same database.

Version used -
==============
Hibernate 4.3.7 
PostGreSql 9.3.1

To show the proof of concept -

We had made 2 separate schema in same database and inserted separate dummy data in the tables.
We had called the method to show the data from the table, where the schemaname is given as input parameter.

Below are the attached files where all the source codes are given.

Does it required to close the statement after calling, connection.createStatement().execute("SET SCHEMA 'public'");