How to configure multiple DataSources for multitenancy in Hibernate

I am trying to add multitenancy to a java application using the separate schema approach as outlined in this webinar

I wanted to know how would I configure multiple data sources via spring perhaps by using properties files and get the data sources from the spring context based on tenant id.

More importantly, though I want to be able to configure my custom connection provider implementation that supports this开发者_开发知识库 multitenancy feature to be used by Hibernate instead of the injected ConnectionProvider that it uses by default.

How can i achieve this.

Use the AbstractRoutingDataSource. See my answer located @ Multiple Entity Manager issue in Spring when using more than one datasource.

This post demonstrate how to use spring security and AbstractRoutingDataSource to build up Multi-Tenancy SaaS applications. Extend Spring Security to Protect Multi-tenant SaaS Applications

The routing can be done by Hibernate via its MultiTenancyConnectionProvider which you can implement like this:

public class MultiTenantConnectionProvider
        extends AbstractMultiTenantConnectionProvider {
 
    public static final MultiTenantConnectionProvider INSTANCE =
            new MultiTenantConnectionProvider();
 
    private final Map<String, ConnectionProvider> connectionProviderMap = 
            new HashMap<>();
 
    Map<String, ConnectionProvider> getConnectionProviderMap() {
        return connectionProviderMap;
    }
 
    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        return connectionProviderMap.get(
            TenantContext.DEFAULT_TENANT_IDENTIFIER
        );
    }
 
    @Override
    protected ConnectionProvider selectConnectionProvider(
            String tenantIdentifier) {
        return connectionProviderMap.get(
            tenantIdentifier
        );
    }
}

Assuming each tenant uses its own dedicated DataSource, you can register the individual ConnectionProvider(s) using a utility method like this one:

private void addTenantConnectionProvider(
        String tenantId, 
        DataSource tenantDataSource, 
        Properties properties) {
         
    DatasourceConnectionProviderImpl connectionProvider = 
        new DatasourceConnectionProviderImpl();
    connectionProvider.setDataSource(tenantDataSource);
    connectionProvider.configure(properties);
     
    MultiTenantConnectionProvider.INSTANCE
    .getConnectionProviderMap()
    .put(
        tenantId, 
        connectionProvider
    );
}

You can register a default tenant for admin-related tasks:

addTenantConnectionProvider(
    TenantContext.DEFAULT_TENANT_IDENTIFIER, 
    defaultDataSource, 
    properties()
);

And for the actual tenants, you could use a method like this one:

private void addTenantConnectionProvider(
        String tenantId) {
     
    DataSourceProvider dataSourceProvider = database()
    .dataSourceProvider();
 
    Properties properties = properties();
 
    MysqlDataSource tenantDataSource = new MysqlDataSource();
    tenantDataSource.setDatabaseName(tenantId);
    tenantDataSource.setUser(dataSourceProvider.username());
    tenantDataSource.setPassword(dataSourceProvider.password());
 
    properties.put(
        Environment.DATASOURCE,
        dataSourceProxyType().dataSource(tenantDataSource)
    );
 
    addTenantConnectionProvider(
        tenantId, 
        tenantDataSource, 
        properties
    );
}

You can change the MysqlDataSource to whatever database you are using.

Then, registering the tenants is as simple as that:

addTenantConnectionProvider("asia");
addTenantConnectionProvider("europe");

The last thing to take into consideration is to provide the MultiTenancyConnectionProvider implementation to Hibernate via the hibernate.multi_tenant_connection_provider configuration property.

properties.put(
    AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER, 
    MultiTenantConnectionProvider.INSTANCE
);

If you want to do multi-tenancy by ConnectionProvider, you'll need a thread-local to provide context. See this brief run-through:

http://literatejava.com/hibernate/multi-tenancy-architecture-with-hibernate/