Hibernate multi-tenancy create schema during runtime

Question

I am setting up multi-tenant support for a java web application using hibernate 4 and spring 4. The default schema is created and set when the application starts. This schema works fine when not trying to support multi-tenancy.

What I need to do now is to create a schema for each new tenant that creates an account. This schema can simply be a copy of the common schema because it will adhere to the same format.

How can I create new schemas that follow the same format as the default schema during runtime? It appears that the default schema is created when the LocalSessionFactoryBean is instantiated since that is where I specify the mapping resources.

Answer 1

I came up with a solution that solves my problem. I hope it is useful to someone out there.

So the main problem came down to the Hibernate restriction of creating a schema for a new client during runtime while in multi-tenant configuration.

"Hibernate does not support automatic schema export in a multi-tenancy environment."

My solution to get around this restriction (using Spring) was to create a new LocalSessionFactoryBean that is configured to NOT to support multi-tenancy. So basically I have two LocalSessionFactoryBeans.

1. Multi-Tenant LocalSessionFactoryBean that is used for multi-tenant sessions
2. Non Multi-Tenant LocalSessionFactoryBean that is used to create schemas for tenants using the configuration set in the spring file.

Spring configuration

<!-- Multi-tenant SessionFactory -->
<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <map>
            <entry key="hibernate.dialect" value="${hibernate.dialect}" />
            <entry key="hibernate.hbm2ddl.auto" value="NONE" />
            <!-- Multi-tenancy support -->
            <entry key="hibernate.multiTenancy" value="SCHEMA" />
            <entry key="hibernate.tenant_identifier_resolver" value="${hibernate.tenant_identifier_resolver}" />
            <entry key="hibernate.multi_tenant_connection_provider" value-ref="multiTenantConnectionProvider" />
        </map>
    </property>
    <property name="mappingResources">
        <list>
            <COMMON SCHEMA MAPPING RESOURCES />
        </list>
    </property>
</bean>

<!-- SessionFactory capable of managing multi-tenant schemas -->
<bean id="sessionFactorySchemaManager"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <map>
            <entry key="hibernate.dialect" value="${hibernate.dialect}" />
            <entry key="hibernate.hbm2ddl.auto" value="CREATE" />
            <!-- Multi-tenancy support -->
            <entry key="hibernate.multiTenancy" value="NONE" />
        </map>
    </property>
    <property name="mappingResources">
        <list>
            <TENANT SPECIFIC SCHEMA MAPPING RESOURCES />
        </list>
    </property>
</bean>

Code used to create the schema

public boolean createSchema(final String tenantId) throws SQLException {
    boolean result = false;

    if(_configuration != null && _dataSource != null) {

        // Get a local configuration to configure
        final Configuration tenantConfig = _configuration;

        // Set the properties for this configuration
        Properties props = new Properties();
        props.put(Environment.DEFAULT_SCHEMA, tenantId);
        tenantConfig.addProperties(props);

        // Get connection
        Connection connection = DriverManager.getConnection(_dataSource.getUrl(), 
                _dataSource.getUsername(), _dataSource.getPassword());

        // Create the schema
        connection.createStatement().execute("CREATE SCHEMA " + tenantId + "");

        // Run the schema update from configuration
        SchemaUpdate schemaUpdate = new SchemaUpdate(tenantConfig);
        schemaUpdate.execute(true, true);

        // Set the schema
        connection.createStatement().execute("SET SCHEMA " + tenantId + "");

        // Set the result
        result = true;

    } else if(_configuration == null) {
        if(_LOGGER.isWarnEnabled()) {
            _LOGGER.warn("No configuration was specified for " + getClass().getSimpleName());
        }
    } else if(_dataSource == null) {
        if(_LOGGER.isWarnEnabled()) {
            _LOGGER.warn("No dataSource was specified for " + getClass().getSimpleName());
        }
    }

    return result;
}

Note that the _configuration in this code came from the Non Multi-Tenant LocalSessionFactoryBean

Answer 2

you can sole your problem by using eclipseLink. here's some util links: https://wiki.eclipse.org/EclipseLink/Examples/JPA/Multitenant and http://www.javacodegeeks.com/2012/02/sneak-peak-at-java-ee-7-multitenant.html or if you want to use hibernate you can take a look at this: http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html and this example on multi tenancy on hibernate : https://gist.github.com/dipold/5700724 and http://www.devx.com/Java/Article/47817