Hibernate 4 Multi Tenancy Demo

Hibernate 4 : Multi Tenant Demo by Mahendra C Shinde

I. Prepare database
Using MySQL 5.5, created two schemas ‘db1’ and ‘db2’
Both contains identical table schema:

CREATE TABLE `books` (
bookId int(11) NOT NULL AUTO_INCREMENT,
title varchar(50) DEFAULT NULL,
PRIMARY KEY (`bookId`)
)

Add some data in both tables [make sure both tables contains different set of rows]

II. Prepare Hibernate Demo App
1. Create New Maven project without archetype selection
2. Add following dependencies to project:

        <dependency>
  		<groupId>mysql</groupId>
  		<artifactId>mysql-connector-java</artifactId>
  		<version>5.1.35</version>
  	</dependency>
  	<dependency>
  		<groupId>org.hibernate</groupId>
  		<artifactId>hibernate-core</artifactId>
  		<version>4.3.8.Final</version>
  	</dependency>

III. Prepare hibernate configuration:

	<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">mahendra</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.default_catalog">db1</property>
        <property name="hibernate.default_schema">db1</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    	<!-- multi-tenancy configuration -->    
        <property name="hibernate.multiTenancy">SCHEMA</property>
    	<property name="hibernate.multi_tenant_connection_provider">com.mahendra.MultiTenantConnectionProviderImpl</propertyx>
    	<property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
    	<mapping class="com.mahendra.Book"/>

And the HibernateUtil class to build SessionFactory:

package com.mahendra;

import org.hibernate.*;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

public class HibernateUtil {

private static SessionFactory factory = build();

private static SessionFactory build() {
Configuration configuration = new Configuration().configure();
StandardServiceRegistryBuilder serviceRegistryBuilder = new StandardServiceRegistryBuilder();
serviceRegistryBuilder.applySettings(configuration.getProperties());
ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();
return configuration.buildSessionFactory(serviceRegistry);
}

public static SessionFactory factory() {
return factory;
}
}

IV, Prepare POJO Entity :

package com.mahendra;

import javax.persistence.*;

@Entity
@Table(name = "books")
public class Book {

@Id
@GeneratedValue
private Integer bookId;

@Column(name = "title", length = 50)
private String title;

@Override
public String toString() {
return "[" + getBookId() + "] " + getTitle();
}

public Integer getBookId() {
return bookId;
}

public void setBookId(Integer bookId) {
this.bookId = bookId;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}
}

V. Prepare ConnectionProvider for Multi-Tenancy

package com.mahendra;

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.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.jboss.logging.Logger;

public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, ServiceRegistryAwareService{

private DriverManagerConnectionProviderImpl provider =new DriverManagerConnectionProviderImpl();

private Logger log = Logger.getLogger(MultiTenantConnectionProviderImpl.class.getName());

public boolean isUnwrappableAs(Class arg0) {
return provider.isUnwrappableAs(arg0);
}

	public <T> T unwrap(Class<T> arg0) {
		return provider.unwrap(arg0);
	}


public Connection getAnyConnection() throws SQLException {
return provider.getConnection();
}

public Connection getConnection(String tenantId) throws SQLException {
Connection con = getAnyConnection();
try {
con.createStatement().execute("use "+tenantId);
log.info("Using "+tenantId+" as database schema");
}catch(SQLException ex) {
throw new HibernateException("Could not alter connection for specific schema");
}
return con;
}

public void releaseAnyConnection(Connection con) throws SQLException {
provider.closeConnection(con);

}

public void releaseConnection(String tenantId, Connection con)
throws SQLException {
try {
con.createStatement().execute("USE mysql");
System.out.println("Now, released "+tenantId);
}catch(SQLException ex) {
throw new HibernateException("Unable to reset");
}
provider.closeConnection(con);

}

public boolean supportsAggressiveRelease() {
return false;
}

public void injectServices(ServiceRegistryImplementor registry) {
Map settings = registry.getService(ConfigurationService.class).getSettings();
provider.configure(settings);
provider.injectServices(registry);

}
}

VI. The Main method to test

package com.mahendra;

import java.util.List;
import org.hibernate.*;

public class AppMain {

public static void main(String[] args) {
System.out.println("Books from db1");
loadBooksFrom("db1");
System.out.println("Books from db2");
loadBooksFrom("db2");
}

static void loadBooksFrom(String tenant) {
SessionFactory factory = HibernateUtil.factory();
Session session = factory.withOptions().tenantIdentifier(tenant).openSession();
List<Book> books = session.createQuery("from Book b").list();
for(Book b : books) {
System.out.println(b);
}
session.close();
}
}

Application will run, but need to close forcefully. This application creates a Service for SessionBuilder. Contact me if you have queries.