Can I use a single federated data source for multiple databases using Spring / Hibernate / c3p0?

Question

Our application uses Spring / Hibernate to access the database. We use several hibernate session factories ( hibernate3.LocalSessionFactoryBean ), because the data is in several separate databases. Each factory session is configured with a federated data source using c3p0 ( c3p0.ComboPooledDataSource ).

As it happens, all databases are on the same server. Our problem is that in the end we got several db connection pools that all connect to the same server. Is there a way to share a single data pool to access multiple databases on the same server? Is it possible to configure jdbcUrl at the factory session level and not at the data source level?

Or is this not a real problem in practice? Is it okay if multiple db connection pools are configured?

Answer 1

One connection is connected to one database.

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

If you have 1 connection per pool actually, C1 and C2 (I know you have more, but this is the same ...) Of course, you could set both connections to 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 → the opportunity to give you a random connection that has already been prepared, which you can use directly, without the overhead of creating a new connection.

Now guess that if you have C1 and C2 in your unique pool, you simply cannot get a “random” connection, since they do not belong to the same database ... Thus, you will have the overhead of checking, returns whether the connection to the expected database, or you will have a 50% chance of fulfilling the query R1 in DB2.

So, yes, you can just make your own implementation of the connection pool, which simply use 2 child pools of CP1 and CP2, and will randomly use getConnection from one of these child connection pools at random, but you will need to check that you use the correct connection pool after that, in order to better separate two different connection pools.

I do not know why you need only one connection pool. Perhaps you want to tell your application “100 connections for all connection pools” and you want your application to automatically configure the optimized number of connections in each pool that you have? It seems to me that this is possible, but I don’t know if there already exists a common implementation, perhaps you can make a connection pool shell that will distribute among all existing pools the average% of connections used, and then adjust the sizes of the pools or something like that. ..

---

It is quite normal for me to have multiple connection pools in your application. In addition, it is quite normal to use two different databases in one application (Oracle + MongoDB or something like that), it is also quite normal to use 2 connection pools that use two Oracle database schemas on the same server, and even if both databases data have the same table.

---

You should look at the multi-level strategies for SaaS applications with several clients (in general B2B): - One of them is to have one database for each client and have each table with the "customer_id" column. This is easier to maintain, but the tables have more rows (but obviously you are indexing this customer_id column) - Another, perhaps what you are doing: there is one database / data source / connectionpool / sessionfactory for each client, and each database will have the same tables. In the end, you will have some mecanism, like a “configured main sessionfactory” that will use ThreadLocal (set by some credential filter?) That store the clientId to select the appropriate child session service.

Take a look at the following article (for example, a lot on this topic): 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 } } 

This is almost what I told you, except that instead of a dynamic session provider, there is a dynamic connection provider. (Tenant here = Client in my example)

You may notice that in this example there is no connection pool, but you can implement your own PooledMyTenantAwareConnectionProvider;)

Good luck.

Answer 2

I do not think you can do this. And I do not think that I will do it. Either treat them as separate databases, or as one.

If they are separate databases, they receive different connection pools and must be managed by the JTA, or you need to develop an entire system for transaction failures between these databases.

If they need to be considered as one; you can (if you use Oracle) create one connection pool and then use an alias and grants to include this single connection for writing to two other databases.