使用 Spring、Hibernate 和 C3P0 在多租户 Web 应用程序中管理连接池

Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0

提问by Khosrow

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:

我正在尝试设置一个多租户 Web 应用程序，（理想情况下）可以同时使用数据库分离和模式分离方法。虽然我将从架构分离开始。我们目前正在使用：

• 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)

• 春天 4.0.0
• 休眠 4.2.8
• Hibernate-c3p0 4.2.8（使用 c3p0-0.9.2.1）
• 和 PostgreSQL 9.3（我怀疑它对整体架构真的很重要）

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

大多数情况下，我遵循了这个线程（因为 的解决方案@Transactional）。但我有点迷失在实施MultiTenantContextConnectionProvider. SO上也有类似的问题，但有些方面我想不通：

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?

1) 连接池会发生什么？我的意思是，它是由 Spring 还是 Hibernate 管理的？我想ConnectionProviderBuilder- 或者按照建议 - 它的任何实现，Hibernate 都是管理它的人。
2）Spring不管理连接池是不是一个好方法？或者Spring是否有可能管理它？
3）这是未来实现数据库和模式分离的正确途径吗？

Any comments or descriptions are totally appreciated.

任何评论或描述都非常感谢。

application-context.xml

应用程序上下文.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

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

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 answerof @ben75:

关于@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]

这是MultiTenantContextConnectionProvider. 它不再延伸AbstractMultiTenantConnectionProvider。而是实现MultiTenantConnectionProvider, 能够返回[Connection][4]而不是[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;
    }
}

回答by ben75

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.

您可以在 3 种会影响连接轮询的不同策略之间进行选择。在任何情况下，您都必须提供MultiTenantConnectionProvider. 您选择的策略当然会影响您的实施。

General remark about MultiTenantConnectionProvider.getAnyConnection()

一般评论 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).

getAnyConnection()hibernate 需要收集元数据并设置 SessionFactory。通常在多租户架构中，您有一个特殊的/主数据库（或模式），任何租户都不使用。它是一种模板数据库（或模式）。如果此方法返回与此数据库（或模式）的连接，则可以。

Strategy 1 : each tenant have it's own database.(and so it's own connection pool)

策略1：每个租户都有自己的数据库。（所以它是自己的连接池）

In this case, each tenant have it's own connection pool managed by C3PO and you can provide an implementation of MultiTenantConnectionProviderbased on AbstractMultiTenantConnectionProvider

在这种情况下，每个租户都有自己的由 C3PO 管理的连接池，您可以提供MultiTenantConnectionProvider基于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 :

每个租户都有自己的C3P0ConnectionProvider，所以你要做的selectConnectionProvider(tenantIdentifier)就是归还正确的。您可以保留一个 Map 来缓存它们，并且可以使用以下内容延迟初始化 C3POConnectionProvider：

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

策略 2：每个租户在单个数据库中都有自己的架构和自己的连接池

This case is very similar to the first strategy regarding ConnectionProviderimplementation since you can also use AbstractMultiTenantConnectionProvideras base class to implement your MultiTenantConnectionProvider

这种情况与关于ConnectionProvider实现的第一个策略非常相似，因为您也可以AbstractMultiTenantConnectionProvider用作基类来实现您的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

该实现与策略 1 的建议实现非常相似，不同之处在于您必须在 c3po 配置中更改架构而不是数据库

Strategy 3 : each tenant have it's own schema in a single database but use a shared connection pool

策略 3：每个租户在单个数据库中都有自己的架构，但使用共享连接池

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 AbstractMultiTenantConnectionProviderwon't work).

这种情况略有不同，因为每个租户将使用相同的连接提供程序（因此连接池将被共享）。在这种情况下：连接提供者必须在使用任何连接之前设置要使用的模式。即您必须实现MultiTenantConnectionProvider.getConnection(String tenantIdentifier)（即提供的默认实现AbstractMultiTenantConnectionProvider不起作用）。

With postgresqlyou can do it with :

使用postgresql你可以做到：

 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:

所以这就是你的getConnection(tenant_identifier);意愿：

@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

其他有用的链接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.

您可以在实施中结合使用策略 1 和策略 2。您只需要一种方法来为当前租户找到正确的连接属性/连接 url。

---

---

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).

我认为在策略 2 或 3 之间的选择取决于您的应用程序上的流量和租户数量。使用单独的连接池：一个租户可用的连接数量会少得多，因此：如果出于某种合法原因，一个租户突然需要很多连接，该特定租户所看到的性能将急剧下降（而另一个租户不会影响）。

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.

另一方面，对于策略 3，如果由于某种合法原因一个租户突然需要很多连接：每个租户看到的性能都会下降。

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)

总的来说，我认为策略 2 更灵活和安全：每个租户不能消耗超过给定数量的连接（如果需要，可以为每个租户配置这个数量）

回答by Saravanan

IMHO, the connection pool management will be default handled by the Sql Server itself, however some programming languages like C# do offer some ways to control the pools. Refer here

恕我直言，连接池管理将默认由 Sql Server 本身处理，但是像 C# 这样的一些编程语言确实提供了一些控制池的方法。参考这里

The choice of (1) schema or (2) separate database for a tenant depends upon the volume of the data that you can anticipate for the tenant. However, the following consideration can be worth looking into

为租户选择 (1) 模式还是 (2) 单独的数据库取决于您可以为租户预期的数据量。但是，以下考虑值得研究

1. create a shared schema model for the trial customers and the low volume customers, this can be identified by the number of the features that you provide to a tenant during the process of onboarding a customer

2. when you create or onboard a enterprise level customer that may have a large transactional data, it is ideal to go for a separate database.

3. The schema model may have a different implementation for SQL Server and a different one for the MySQL Server, which you should consider.

4. also when choosing for the option, do consider the fact that a customer [tenant] may be willing to scale out after a considerable amount of time and system usage. If there is no appropriate scale out option supported in your app, you will have to be bothered.

1. 为试用客户和小批量客户创建共享模式模型，这可以通过您在客户入职过程中提供给租户的功能数量来识别

2. 当您创建或加入可能拥有大量交易数据的企业级客户时，最好使用单独的数据库。

3. 架构模型对于 SQL Server 可能有不同的实现，对于 MySQL Server 可能有不同的实现，您应该考虑这一点。

4. 此外，在选择该选项时，请务必考虑这样一个事实，即客户 [租户] 可能愿意在经过大量时间和系统使用后进行横向扩展。如果您的应用程序中不支持适当的横向扩展选项，您将不得不感到困扰。

Share your comments on the above points, to take this discussion further

分享您对以上几点的评论，以进一步讨论