How to configure multiple schemas with Hibernate

Question

We have got a requirement for multiple schemas in Hibernate.

In our project, we need to connect to multiple schemas based on the username and password. But how to configure multiple schemas in Hibernate?

Please let me know if there's a way.

Answer 1

Thanks to Hibernate Multitenancy support, you can easily do that as follows:

The following examples can be found in the Hibernate ORM documentation folder.

Each schema can be a tenant, so you only need to provide a tenant identifier to the Hibernate Session, and Hibernate will know what database schema to connect to:

private void doInSession(String tenant, Consumer<Session> function) {
    Session session = null;
    Transaction txn = null;
    try {
        session = sessionFactory
            .withOptions()
            .tenantIdentifier( tenant )
            .openSession();
        txn = session.getTransaction();
        txn.begin();
        function.accept(session);
        txn.commit();
    } catch (Throwable e) {
        if ( txn != null ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

You need to provide a MultiTenantConnectionProvider implementation as well:

public class ConfigurableMultiTenantConnectionProvider
        extends AbstractMultiTenantConnectionProvider {

    private final Map<String, ConnectionProvider> connectionProviderMap =
        new HashMap<>(  );

    public ConfigurableMultiTenantConnectionProvider(
            Map<String, ConnectionProvider> connectionProviderMap) {
        this.connectionProviderMap.putAll( connectionProviderMap );
    }

    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        return connectionProviderMap.values().iterator().next();
    }

    @Override
    protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
        return connectionProviderMap.get( tenantIdentifier );
    }
}

And you can initialize it as follows:

private void init() {
    registerConnectionProvider( FRONT_END_TENANT );
    registerConnectionProvider( BACK_END_TENANT );

    Map<String, Object> settings = new HashMap<>(  );

    settings.put( AvailableSettings.MULTI_TENANT, multiTenancyStrategy() );
    settings.put( AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER,
        new ConfigurableMultiTenantConnectionProvider( connectionProviderMap ) );

    sessionFactory = sessionFactory(settings);
}

protected void registerConnectionProvider(String tenantIdentifier) {
    Properties properties = properties();
    properties.put( Environment.URL,
        tenantUrl(properties.getProperty( Environment.URL ), tenantIdentifier) );

    DriverManagerConnectionProviderImpl connectionProvider =
        new DriverManagerConnectionProviderImpl();
    connectionProvider.configure( properties );
    connectionProviderMap.put( tenantIdentifier, connectionProvider );
}

Since this example uses H2, the tenantUrl is defined like this:

public static final String SCHEMA_TOKEN = ";INIT=CREATE SCHEMA IF NOT EXISTS %1$s\\;SET SCHEMA %1$s";

@Override
protected String tenantUrl(String originalUrl, String tenantIdentifier) {
    return originalUrl + String.format( SCHEMA_TOKEN, tenantIdentifier );
}

Now you can just use separate tenants and schemas from the same SessionFactory:

doInSession( FRONT_END_TENANT, session -> {
    Person person = new Person(  );
    person.setId( 1L );
    person.setName( "John Doe" );
    session.persist( person );
} );

doInSession( BACK_END_TENANT, session -> {
    Person person = new Person(  );
    person.setId( 1L );
    person.setName( "John Doe" );
    session.persist( person );
} );

Since the MultiTenantConnectionProvider acts like any other ConnectionProvider, you can configure each tenant to use a separate DataSource which hides the user/password credentials.