使用Spring，Hibernate和C3P0管理多租户Web应用程序中的连接池 [英] Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0
问题描述
我试图设置一个多租户Web应用程序，同时（理想情况下）可以同时使用数据库分离和模式分离两种方法。尽管我将从Schema分离开始。我们目前使用的是：
- Spring 4.0.0
- Hibernate 4.2.8 >
- Hibernate-c3p0 4.2.8（使用c3p0-0.9.2.1）
- 和PostgreSQL 9.3（我怀疑它对于整体架构非常重要）
大多数情况下，我遵循 this thread （因为 @Transactional ）。但我有点在实现 MultiTenantContextConnectionProvider 时丢失了。这里也提到了这个类似的问题，但有一些方面我无法确定out：
1）Connection Pooling会发生什么？我的意思是，它是由Spring还是Hibernate管理的？我想用> ConnectionProviderBuilder - 或者按照建议 - 它的任何实现，Hibernate是管理它的人。
2）这是一个好方法吗？ Spring不管理连接池？或者Spring是否可以管理它？
3）这是未来实现数据库和模式分离的正确途径吗？
$ b application-context.xml
<豆类>
...
< bean id =dataSourceclass =org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy>
< property name =targetDataSourceref =c3p0DataSource/>
< / bean>
< bean id =c3p0DataSourceclass =com.mchange.v2.c3p0.ComboPooledDataSourcedestroy-method =close>
< property name =driverClassvalue =org.postgresql.Driver/>
...其他C3P0相关配置
< / bean>
< bean id =sessionFactoryclass =org.springframework.orm.hibernate4.LocalSessionFactoryBean>
< property name =packagesToScanvalue =com.webapp.domain.model/>
< property name =hibernateProperties>
<道具>
< 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>
< /道具>
< / property>
< / bean>
< bean id =transactionManagerclass =org.springframework.orm.hibernate4.HibernateTransactionManager>
< property name =autodetectDataSourcevalue =false/>
< property name =sessionFactoryref =sessionFactory/>
< / bean>
...
< / beans>
CurrentTenantContextIdentifierResolver.java
pre $
public class CurrentTenantContextIdentifierResolver implements CurrentTenantIdentifierResolver {
@Override
public String resolveCurrentTenantIdentifier（）{
return CurrentTenantIdentifier; //例如：public，tid130，tid456，...
}
@Override
public boolean validateExistingCurrentSessions（）{
return true;
$ MultiTenantContextConnectionProvider.java / p>
public class MultiTenantContextConnectionProvider extends AbstractMultiTenantConnectionProvider {
//我需要这个和它的配置吗？
// private C3P0ConnectionProvider connectionProvider = null;
@Override
public ConnectionProvider getAnyConnectionProvider（）{
//主要问题在这里。
}
@Override
public ConnectionProvider selectConnectionProvider（String tenantIdentifier）{
//当然这里。
}
}
编辑
你可以选择3种不同的策略来影响连接轮询。无论如何，您必须提供 MultiTenantConnectionProvider 。您选择的策略当然会影响您的实施。
有关 MultiTenantConnectionProvider.getAnyConnection（）
getAnyConnection（） 来收集元数据和设置SessionFactory。通常在多租户体系结构中，您有一个特殊/主数据库（或架构）不被任何租户使用。这是一种模板数据库（或模式）。如果此方法返回与此数据库（或架构）的连接，则可以。
策略1：每个租户都有自己的数据库。所以它是自己的连接池）
在这种情况下，每个租户都有自己的由C3PO管理的连接池，并且您可以提供 MultiTenantConnectionProvider AbstractMultiTenantConnectionProvider
每个房客都有自己的 C3P0Connec tionProvider ，所以您只需在 selectConnectionProvider（tenantIdentifier） 是返回正确的。你可以保留一个Map来缓存它们，你可以用一些类似的方法来初始化一个C3POConnectionProvider：
private ConnectionProvider lazyInit（String tenantIdentifier ）{
C3P0ConnectionProvider connectionProvider = new C3P0ConnectionProvider（）;
connectionProvider.configure（getC3POProperties（tenantIdentifier））;
返回connectionProvider;
$ b $ private Map getC3POProperties（String tenantIdentifier）{
//在这里，您必须从文件或从文件中获取默认的hibernate和c3po配置属性
// // Spring应用程序上下文（很有可能
//这些默认属性指向特殊/主数据库）
//并更改它们以便数据源指向租户数据库
//即：更改属性hibernate.connection.url
//（以及您架构中的任何其他租户特定属性，例如：
// hibernate.connection.username = tenantIdentifier
// hibernate.connection .password = ...
// ...）
}
策略2：每个租户都有自己的模式，并且在单个数据库中拥有自己的连接池
这种情况与第一种策略非常相似 ConnectionProvider 实现，因为您也可以使用 AbstractMultiTenantConnectionProvider 作为基类来实现您的 MultiTenantConnectionProvider
这个实现与建议的实现非常相似除了您必须改变架构而不是c3po配置中的数据库之外。策略3：每个租户在单个数据库中都有自己的模式，但是，使用共享连接池
这种情况稍有不同，因为每个租户都将使用相同的连接提供程序（因此连接池将被共享）。在这种情况下：连接提供者必须将该架构设置为在任何连接使用之前使用。即您必须实现 MultiTenantConnectionProvider.getConnection（String tenantIdentifier）（即由 AbstractMultiTenantConnectionProvider 提供的默认实现将不起作用） 。
postgresql ，你可以这样做：
SET search_path to< schema_name_for_tenant>;
或使用别名
SET模式< schema_name_for_tenant>;
因此，您的 getConnection（tenant_identifier）; 将看起来像：
@Override
public Connection getConnection（String tenantIdentifier）throws SQLException {
最终Connection connection = getAnyConnection（）;
try {
connection.createStatement（）。execute（SET search_path TO+ tenanantIdentifier）;
}
catch（SQLException e）{
抛出新的HibernateException（
无法将JDBC连接更改为指定模式[+
tenantIdentifier +]，
e
）;
}
返回连接;
}
有用的参考资料是这里（官方文档） b
$ b
其他有用的链接 C3POConnectionProvider.java
您可以在实施中将策略1和策略2结合起来。您只需要一种方法来为当前租户找到正确的连接属性/连接URL。
编辑
我认为策略2或3之间的选择取决于您应用的流量和租户数量。使用单独的连接池：一个租户可用的连接数量将会低得多，因此：如果出于某种合法性原因，一个租户需要突然多次连接，则此特定租户所看到的性能将急剧下降（而另一个租户不会另一方面，根据策略3，如果出于某种合法的原因，一个租户需要突然之间有许多联系：每个租户所看到的表现会下降。
一般来说，我认为策略2更加灵活和安全：每个租户不能超过给定的连接数量（如果需要，可以为每个租户配置此数量它）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 (because of the solution for @Transactional). But I'm kinda lost in implementing MultiTenantContextConnectionProvider. There is also this similar question 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.
}
}
Edit
Regarding the answer 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;
}
}
You can choose between 3 different strategies that will impact connection polling. In any case you have to provide an implementation of MultiTenantConnectionProvider. The strategy you choose will of course impact your implementation.
General remark about MultiTenantConnectionProvider.getAnyConnection()
getAnyConnection() is required by hibernate to collect metadata and setup the SessionFactory. Usually in a multi-tenant architecture you have a special/master database (or schema) not used by any tenant. It's a kind of template database (or schema). It's ok if this method returns a connection to this database (or schema).
Strategy 1 : each tenant have it's own database. (and so it's own connection pool)
In this case, each tenant have it's own connection pool managed by C3PO and you can provide an implementation of MultiTenantConnectionProvider based on AbstractMultiTenantConnectionProvider
Every tenant have it's own C3P0ConnectionProvider, so all you have to do in selectConnectionProvider(tenantIdentifier) is to return the correct one. You can keep a Map to cache them and you can lazy-initialize a C3POConnectionProvider with something like :
private ConnectionProvider lazyInit(String tenantIdentifier){
C3P0ConnectionProvider connectionProvider = new C3P0ConnectionProvider();
connectionProvider.configure(getC3POProperties(tenantIdentifier));
return connectionProvider;
}
private Map getC3POProperties(String tenantIdentifier){
// here you have to get the default hibernate and c3po config properties
// from a file or from Spring application context (there are good chances
// that those default properties point to the special/master database)
// and alter them so that the datasource point to the tenant database
// i.e. : change the property hibernate.connection.url
// (and any other tenant specific property in your architecture like :
// hibernate.connection.username=tenantIdentifier
// hibernate.connection.password=...
// ...)
}
Strategy 2 : each tenant have it's own schema and it's own connection pool in a single database
This case is very similar to the first strategy regarding ConnectionProvider implementation since you can also use AbstractMultiTenantConnectionProvider as base class to implement your MultiTenantConnectionProvider
The implementation is very similar to the suggested implementation for Strategy 1 except that you must alter the schema instead of the database in the c3po configuration
Strategy 3 : each tenant have it's own schema in a single database but use a shared connection pool
This case is slightly different since every tenant will use the same connection provider (and so the connection pool will be shared). In the case : the connection provider must set the schema to use prior to any usage of the connection. i.e. You must implement MultiTenantConnectionProvider.getConnection(String tenantIdentifier) (i.e. the default implementation provided by AbstractMultiTenantConnectionProvider won't work).
With postgresql you can do it with :
SET search_path to <schema_name_for_tenant>;
or using the alias
SET schema <schema_name_for_tenant>;
So here is what your getConnection(tenant_identifier); will look like:
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
final Connection connection = getAnyConnection();
try {
connection.createStatement().execute( "SET search_path TO " + tenanantIdentifier );
}
catch ( SQLException e ) {
throw new HibernateException(
"Could not alter JDBC connection to specified schema [" +
tenantIdentifier + "]",
e
);
}
return connection;
}
Useful reference is here (official doc)
Other useful link C3POConnectionProvider.java
You can combine strategy 1 and strategy 2 in your implementation. You just need a way to find the correct connection properties/connection url for the current tenant.
EDIT
I think that the choice between strategy 2 or 3 depends on the traffic and the number of tenants on your app. With separate connection pools : the amount of connections available for one tenant will be much lower and so: if for some legitime reason one tenant need suddenly many connections the performance seen by this particular tenant will drastically decrease (while the other tenant won't be impacted).
On the other hand, with strategy 3, if for some legitime reason one tenant need suddenly many connections: the performance seen by every tenant will decrease.
In general , I think that strategy 2 is more flexible and safe : every tenant cannot consume more than a given amount of connection (and this amount can be configured per tenant if you need it)
这篇关于使用Spring，Hibernate和C3P0管理多租户Web应用程序中的连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
