Login

I'm trying to setup a multi-tenant web application, with (ideally) possibility for both Database-separated and Schema-separated approach at the same time. Although I'm going to start with Schema separation. We're currently using:

- Spring 4.0.0
- Hibernate 4.2.8
- Hibernate-c3p0 4.2.8 (which uses c3p0-0.9.2.1)
- and PostgreSQL 9.3 (which I doubt it really matters for the overall architecture)

Mostly I followed [this thread][1] (because of the solution for `@Transactional`). But I'm kinda lost in implementing `MultiTenantContextConnectionProvider`. There is also [this similar question][2] asked here on SO, but there are some aspects that I can't figure out:

1) What happens to Connection Pooling? I mean, is it managed by Spring or Hibernate? I guess with `ConnectionProviderBuilder` - or as suggested - any of its implementation, Hibernate is the guy who manages it.
2) Is it a good approach that Spring does not manage Connection Pooling? or Is it even possible that Spring does manage it?
3) Is this the right path for future implementing of both Database and Schema separation?

Any comments or descriptions are totally appreciated.

**application-context.xml**

<beans>
...
<bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource" ref="c3p0DataSource" />
</bean>

<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="org.postgresql.Driver" />
... other C3P0 related config
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="packagesToScan" value="com.webapp.domain.model" />

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
<prop key="hibernate.default_schema">public</prop>

<prop key="hibernate.multiTenancy">SCHEMA</prop>
<prop key="hibernate.tenant_identifier_resolver">com.webapp.persistence.utility.CurrentTenantContextIdentifierResolver</prop>
<prop key="hibernate.multi_tenant_connection_provider">com.webapp.persistence.utility.MultiTenantContextConnectionProvider</prop>
</props>
</property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="autodetectDataSource" value="false" />
<property name="sessionFactory" ref="sessionFactory" />
</bean>

...
</beans>

**CurrentTenantContextIdentifierResolver.java**

public class CurrentTenantContextIdentifierResolver implements CurrentTenantIdentifierResolver {
@Override
public String resolveCurrentTenantIdentifier() {
return CurrentTenantIdentifier; // e.g.: public, tid130, tid456, ...
}

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

**MultiTenantContextConnectionProvider.java**

public class MultiTenantContextConnectionProvider extends AbstractMultiTenantConnectionProvider {
// Do I need this and its configuratrion?
//private C3P0ConnectionProvider connectionProvider = null;

@Override
public ConnectionProvider getAnyConnectionProvider() {
// the main question is here.
}

@Override
public ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
// and of course here.
}
}

<br />

---

**Edit**

Regarding [the answer][3] of @ben75:

This is a new implementation of `MultiTenantContextConnectionProvider`. It no longer extends `AbstractMultiTenantConnectionProvider`. It rather implements `MultiTenantConnectionProvider`, to be able to return `[Connection][4]` instead of `[ConnectionProvider][5]`

public class MultiTenantContextConnectionProvider implements MultiTenantConnectionProvider, ServiceRegistryAwareService {
private DataSource lazyDatasource;;

@Override
public void injectServices(ServiceRegistryImplementor serviceRegistry) {
Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();

lazyDatasource = (DataSource) lSettings.get( Environment.DATASOURCE );
}

@Override
public Connection getAnyConnection() throws SQLException {
return lazyDatasource.getConnection();
}

@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;
}
}

[1]: