我可以通过Spring / Hibernate / c3p0将一个池化数据源用于多个数据库吗？ [英] Can I use one pooled datasource for multiple databases with Spring/Hibernate/c3p0?

问题描述

我们的应用程序使用Spring / Hibernate进行数据库访问。我们使用多个休眠会话工厂（ hibernate3.LocalSessionFactoryBean ），因为数据位于多个单独的数据库中。每个会话工厂都使用c3p0（ c3p0.ComboPooledDataSource ）配置有池化数据源。

Our application uses Spring/Hibernate for database access. We use multiple hibernate session factories (hibernate3.LocalSessionFactoryBean), because the data is in a number of separate databases. Each session factory is configured with a pooled datasource using c3p0 (c3p0.ComboPooledDataSource).

碰巧，所有数据库都位于同一台db服务器上。我们的问题是，最终我们有许多数据库连接池，所有连接池都连接到同一服务器上。有什么方法可以共享一个池化数据源以访问同一服务器上的多个数据库？是否可以在会话工厂级别而不是数据源级别配置 jdbcUrl ？

As it happens, the databases all reside on the same db server. Our issue is that we end up with a number of db connection pools, all connecting into the same server. Is there any way of sharing one pooled datasource for access to multiple databases on the same server? Is it possible to configure the jdbcUrl at the session factory level, instead of at the datasource level?

还是这不是实际的问题？配置多个数据库连接池是否完全可以？

Or is this not a real issue in practice? Is it perfectly ok to have multiple db connection pools configured?

推荐答案

一个连接将连接到一个DB。

One Connection connects to one DB.

如果2个数据库（DB1和DB2）并需要一个连接池，您需要为两个数据库准备好连接。

If you have 2 databases, DB1 and DB2, and want a connection pool, you need to have prepared connections for both databases.

如果每个池实际上有1个连接，则C1和C2（我知道您有更多连接，但这是相同的...）
肯定会可以将这两个连接放在同一个连接池中。
这样您将拥有一个与C1和C2相连的连接池。
您对连接池有什么期望？对我来说->能够为您提供可以直接使用的随机已准备好的连接，而无需创建新连接。

If you have 1 connection per pool actually, C1 and C2 (i know you have more but it's the same...) For sure it would be possible to put these both connections in the same connection pool. Then you will have a connection pool with C1 and C2. What do you expect from your connection pool? For me -> to be able to give you random already prepared connection that you can directly use, without the overhead of creation a new connection.

现在，请猜怎么办，如果您在唯一的池中同时拥有C1和C2，您根本无法获得随机连接，因为它们不属于同一数据库...因此，您将需要检查连接返回是否指向预期的开销数据库，否则您将有50％的机会在DB2上执行请求R1。

Now guess what, if you have both C1 and C2 in your unique pool, you simply can't get a "random" connection since they do not belong to the same database... Thus you'll have the overhead of checking if the connection returned points to the expected database, or you'll have 50% chances to execute request R1 on DB2.

因此，有可能，您可以简单地自己实现连接池，只需使用2个子连接池CP1和CP2，这将随机使用这些子连接池之一中的getConnection，但是您必须在之后检查使用的连接池是否正确，因此最好分开使用2个不同的连接池

So yes it is possible, you could simple make your own implementation of a connection pool that just use 2 child connection pools CP1 and CP2 and that will randomly use the getConnection from one of these child connection pools randomly, but you'll have to check you use the right connection pool afterward so you'd better keep separated 2 different connection pools.

我不知道您为什么只想要一个连接池。也许您想告诉您的应用所有连接池有100个连接，并且希望您的应用自动在您拥有的每个池中设置优化的连接数？在我看来，这是可能的，但我不知道是否已经存在通用的实现，也许您可​​以做一个连接池包装器，该包装器将在所有现有池之间共享使用的平均连接百分比，然后调整池大小或像这样...

I don't know why you only want one connection pool. Perhaps you want to be able to tell your app "there are 100 connections for all connection pools" and you'd like your app to automagically set an optimized number of connection in each pool you have? It seems possible to me but i don't know if there is an already existing generic implementation for that, perhaps you can do a connection pool wrapper that will share between all existing pools the average % of connections used and then adjust the pools sizes or something like that...

对我来说，在您的应用程序中拥有多个连接池是完全可以的。
在同一应用程序（Oracle + MongoDB或类似的应用程序）中使用2个不同的数据库完全可以，同时在同一服务器上使用使用2个Oracle数据库模式的2个连接池也完全可以，即使两个数据库都具有完全相同的表。

For me it is totally ok to have multiple connection pools in your application. As well as it is totally ok to use 2 different databases in the same application (Oracle + MongoDB or something like that), it is also totally ok to use 2 connection pools that use 2 Oracle database schemas on the same server, and even if both databases have the exact same tables.

您应该查看具有多个客户的SaaS应用程序的多租户策略（通常是B2B）：
-一种是为每个客户提供一个DB，并且每个表都具有 customer_id列。维护起来更容易，但是表上有更多行（但显然您为customer_id列建立了索引）
-另一个可能是您要做的：每个客户拥有一个数据库/数据源/连接池/会话工厂，并且每个数据库都会有相同的表。最终，您将具有某种机制，例如定制的主sessionfactory，它将使用ThreadLocal（由某些凭据过滤器设置吗？）存储customerId来选择适当的子sessionfactory。

You should look at multitenancy strategies for SaaS applications with multiple customers (B2B generally): - One is to have one DB for every customers, and to have each table having a column "customer_id". It's easier to maintain but there are more rows on the tables (but obviously you index that customer_id column) - The other is perhaps what you do: have one database/datasource/connectionpool/sessionfactory per customer, and every database will have the same tables. Eventually you'll have some mecanism like a "customized main sessionfactory" which will use a ThreadLocal (set by some credential filter?) that store the customerId to select the appropriate child sessionfactory.

以下列示例为例（该主题有很多）：
http： //relation.to/Bloggers/MultitenancyInHibernate

Take a look at the following article for exemple (there are many on that subject): http://relation.to/Bloggers/MultitenancyInHibernate

public class MyTenantAwareConnectionProvider implements ConnectionProvider {
    public static final String BASE_JNDI_NAME_PARAM = "MyTenantAwareConnectionProvider.baseJndiName";

    private String baseJndiName;

    public void configure(Properties props) {
        baseJndiName = props.getProperty( BASE_JNDI_NAME_PARAM );
    }

    public Connection getConnection() throws SQLException {
        final String tenantId = TenantContext.getTenantId()
        final String tenantDataSourceName = baseJndiName + '/' + tenantId;
        DataSource tenantDataSource = JndiHelper.lookupDataSource( tenantDataSourceName );
        return tenantDataSource.getConnection();
    }

    public void closeConnection(Connection conn) throws SQLException {
        conn.close();
    }

    public boolean supportsAggressiveRelease() {
        // so long as the tenant identifier remains available in TL throughout, we can
        return true;
    }

    public close() {
        // currently nothing to do here
    }
}

除了我这里有一个动态连接提供程序而不是一个动态会话提供程序外，这几乎是我告诉你的。
（这里的租户=客户）

It's almost what i told you except that here there is a dynamic connection provider instead of a dynamic session provider. (Tenant here = Customer in my exemple)

您可以注意到此示例中没有连接池，但是您可以实现自己的PooledMyTenantAwareConnectionProvider;）

You can notice that there is no connection pooling in this exemple but you sure can implement your own PooledMyTenantAwareConnectionProvider ;)

祝你好运。

这篇关于我可以通过Spring / Hibernate / c3p0将一个池化数据源用于多个数据库吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！