Manage Connection Pooling in multi tenant app using Hibernate

Question

I am trying to setup a multi-tenant application with a separate schema approach.

I am using JPA with Hibernate 4 implementation.

Like said @ben75 in this very usefull thread, there many way to manage the connection (shared or own connection for each tenant).

I have developped a first solution based on a separate schema but with a shared connection pool like in this thread. This works fine, but I think that if each tenant could have his own connection it would be better because a tenant could not decrease performance of others.

This approach seems to be similar to the separate database approach but I don't know how I can create a connection (using C3P0 or something else ?). In hibernate documentation they use the class ConnectionProviderUtils, but I really don't know what is done in this class.

MultiTenantConnectionProviderImpl

public class MultiTenantConnectionProviderImpl extends AbstractMultiTenantConnectionProvider {  

private C3P0ConnectionProvider connectionProvider = null;

@Override
protected ConnectionProvider getAnyConnectionProvider() {
    // my main question is here 

}

@Override
protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
   // and here - how create a new connection ?
}

Regarding the answer of @Ben75, he said that I have to get the default hibernate and c3po config properties from a file and alter the schema. Or maybe the user/password too, if there is a different user by tenant.

Do you know how I can doing this ? Is there some class for reading persistence.xml and create a connection ?

I have found examples on separate database approach that use a JNDI service for getting the good datasource, but on my application I haven't (not full Java EE).

Edit

There is an implementation that seems working on Oracle (in this example the user/password is the same as the tenantId):

public class MultiTenantConnectionProviderImpl extends AbstractMultiTenantConnectionProvider
    implements ServiceRegistryAwareService {

     private Map<String, C3P0ConnectionProvider> connectionPool = new HashMap<>();
     private Map<String, String> originalSettings;
     private ServiceRegistryImplementor serviceRegistry;

     @Override
     protected ConnectionProvider getAnyConnectionProvider() {
         // return the default connection
         return connectionPool.get(TenantContext.get().getTenantId());
     }

     @Override
     protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
        C3P0ConnectionProvider connectionProvider = connectionPool.get(tenantIdentifier);
        if (connectionProvider == null) {
          // create the new connection and register it
          Map<String, String> settings = new HashMap<>(originalSettings);
          // alter connexion by changing user / password of the connection
          settings.put("hibernate.connection.user", tenantIdentifier);
          settings.put("hibernate.connection.password", tenantIdentifier);
          connectionProvider = new C3P0ConnectionProvider();
          connectionProvider.injectServices(serviceRegistry);
          connectionProvider.configure(settings);
          connectionPool.put(tenantIdentifier, connectionProvider);
        }
        return connectionProvider;
     }

     @Override
     public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        this.serviceRegistry = serviceRegistry;
        originalSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
        C3P0ConnectionProvider connectionProvider = new C3P0ConnectionProvider();
        connectionProvider.injectServices(serviceRegistry);
        connectionProvider.configure(originalSettings);
        connectionPool.put(TenantContext.get().getTenantId(), connectionProvider);
     }
  }

It works fine on Oracle but I don't know if this is a good implementation...

Moreover, it don't know why it does not work on my embedded H2 DB, query are always executed on the public default schema even if a change user that have no right on this.

Edit 2:

Finally, on H2 database I have to add this :

settings.put("hibernate.connection.url", "jdbc:h2:./db;INIT=SET SCHEMA " + tenantIdentifier);