Why does using C3PO and JNDI at the same time create an issue?

Question

I'm running an application that uses Hibernate out of a Tomcat 8 server. I want to utilize a different connection pool other than Hibernate's because they make it pretty apparent that it's not suitable for use in a production environment.

Also, they mention that:

For use inside an application server, you should almost always configure Hibernate to obtain connections from an application server javax.sql.Datasource registered in JNDI.

So it seems I need to do two things:

1. Configure Hibernate to work with a third-party connection pool -- Hibernate recommends C3PO
2. Configure Hibernate to obtain connections from a javax.sql.Datasource object registered in JNDI

I've been researching to see how to make these changes and I came across this SO question. The poster is already using C3PO and is asking how to connect to their database via a JNDI Datasource object. However, they ran into problems because they were already using C3PO while they were following the answerer's steps to use the JNDI Datasource. The poster said this in the comments section of the accepted answer:

yeah right, I've been doing sth really silly in there using both c3p0 and JNDI. I removed all the c3p0 configurations and it's working fine now.

Hibernate recommends using a third-party connection pool, namely C3PO, and to use a JNDI Datasource to receive connections, and yet, it seemed to be causing an issue for this user; and they even go so far as to talk about using them both at the same time as if it's an obvious mistake.

So can I not use them both at the same time, or should I, as Hibernate recommends? All I'm trying to do is to replace Hibernate's default connection pool with a pool that is intended for use in production environments, and also configure Hibernate to obtain connections from a javax.sql.Datasource object registered in JNDI, as they recommend.

Answer 1

I'll try to cleanup the confusion.

I think it starts with the surprisingly simple DataSource interface: https://docs.oracle.com/javase/7/docs/api/javax/sql/DataSource.html

The DataSource interface is implemented by a driver vendor. There are three types of implementations:

1. Basic implementation -- produces a standard Connection object
2. Connection pooling implementation -- produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager.
3. Distributed transaction implementation -- produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.

Hibernate needs a DataSource to work with, and recommends that it uses connection pooling.

C3PO wraps an existing DataSource and applies connection pooling to it, and creates a new DataSource that is type 2. C3PO assumes that the DataSource it gets is type 1, but it cannot be sure.

In other application servers, if you declare a datasource that is registered in JNDI, it almost always uses connection pooling already in the container. In the case of Tomcat 8, it uses C3PO internally.

So there are two ways to achieve connection pooling in Hibernate: either create a type 1 datasource and embed it in a connection pool in code, or declare your datasource (with connection pool) in the container, and inject it in hibernate from JNDI.

If you do both, like in your case, the C3PO in your application get a datasource from JNDI that is itself a C3PO DataSource managed by tomcat. When the application tries to get a connection, the application C3PO will call the container C3PO, which will create the actual connection, but the connection will be pooled in both connection pools. When hibernate releases the connection, the application C3PO will keep it for reuse, but the other connection pool will keep waiting for the connection to be released as well.

Depending on the configuration, the underlying connection pool could possibly kill the connection after a certain timeout.

So configuring two connection pools on top of each other is dangerous and completely unnecessary.

To answer the bounty question: in production environments, declare the datasource in your production container and connect it to Hibernate via JNDI without any additional connection pooling configured in Hibernate.