MultiTenancy with Hibernate 4.0 with Separate Schema approach

Question

I am using EJB 3.0 and Hibernate 4 with PostgreSQL as my database server to create a multitenant system where each tenant will have separate but identical schema. I am still in the trial stage where I have 3 schemes public, company1, company2 all having a single table person. Now what i want to do is change the schema in runtime as per the user so that he can view the data of his/her company only.

Here is my sample code: Entity Object:

    package com.neebal.domain;

        import java.io.Serializable;
        import java.lang.Long;
        import java.lang.String;

        import javax.persistence.*;
        import org.eclipse.persistence.annotations.Multitenant;
        import org.eclipse.persistence.annotations.MultitenantType;

        @Entity

        //@Table(schema = "company1")
        public class Person implements Serializable {


    @Id
    private Long id;
    private String name;
    private static final long serialVersionUID = 1L;

    public Person() {
        super();
    }   
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }   
    public String getName() {
        return this.name;
    }

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

The MultiTenantConnectionProvider class:

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.service.config.spi.ConfigurationService;
import org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider;
import org.hibernate.service.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);
    }
}

The CurrentTenantIdentifierResolver class:

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;

public class SchemaResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        System.out.println("company1");
        return "company1"; //TODO: Implement service to identify tenant like: userService.getCurrentlyAuthUser().getTenantId();
    }

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

The persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="testEJB">
        <jta-data-source>jdbc/testpgsql</jta-data-source>
        <properties>
            <property name="javax.persistence.provider" value="org.hibernate.ejb.HibernatePersistence" />

            <property name="hibernate.connection.username" value="postgres" />
            <property name="hibernate.connection.password" value="root" />
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/test" />
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />

            <property name="hibernate.multiTenancy" value="SCHEMA" />
            <property name="hibernate.tenant_identifier_resolver" value="com.neebal.util.multitenancy.SchemaResolver" />
            <property name="hibernate.multi_tenant_connection_provider"
                value="com.neebal.util.multitenancy.MultiTenantProvider" />

            <property name="hibernate.hbm2ddl.auto" value="create-drop" />
        </properties>
    </persistence-unit>
</persistence>

And finally the DAO class:

import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import com.neebal.domain.Person;

/**
 * Session Bean implementation class PersonDAO
 */
@Stateless
public class PersonDAO implements PersonDAOLocal {

    @PersistenceContext
    EntityManager entityManager;
    /**
     * Default constructor. 
     */
    public PersonDAO() {
        // TODO Auto-generated constructor stub
    }

    @Override
    public void save(Person person) {
        entityManager.persist(person);
    }

    @Override
    public List<Person> getAll() {

        Person person = entityManager.find(Person.class, 2L);
        System.out.println(person.getName());
        return null;
    }

}

In this example I have hardcoded the schema as company1 but it still persists or retrieves the data from public schema. So where am I wrong in this example.

Answer 1

The question is already 1 year old, but I think the problem of using different schemas depending on some runtime condition is common one, so I'll answer anyway. If I understand you right and the set of tenants is small, then I think the easiest way to do what you're trying to achieve is to define a separate persistence units for each tenant in your persistence.xml

<persistence-unit name="public">
.. settings for first schema        
</persistence-unit>
<persistence-unit name="company1">
.. settings for first schema        
</persistence-unit>
<persistence-unit name="company2">
.. settings for first schema        
</persistence-unit>

Then have for each one a separate entityManager:

@PersistenceContext(unitName = "public")
private EntityManager emPublic;

@PersistenceContext(unitName = "company1")
private EntityManager emComp1;

@PersistenceContext(unitName = "company2")
private EntityManager emComp1;

Now you can switch between entity managers, given the currently authorized user.

Depending on your exact infrastructure etc, there may be other approaches, too. For instance, if all your schemas are on the same server, then you could also try to pass the schema names directly to your queries.

This is pure JPA and thus portable and not depending on any persistence provider like hibernate nor on your DBMS.