JDBC Connection Pooling: Connection Reuse?

15
votes

As per my understanding, JDBC Connection Pooling (at a basic level) works this way:

  1. create connections during app initialization and put in a cache
  2. provide these cached connections on demand to the app
  3. a separate thread maintains the Connection Pool, performing activities like:
    • discard connections that have been used (closed)
    • create new connections and add to the cache to maintain a specific count of connections

But, whenever I hear the term "connection reuse" in a JDBC Connection Pooling discussion, I get confused. When does the connection reuse occurs?

Does it means that Connection Pool provides the same connection for two different database interactions (without closing it)? Or, is there a way to continue using a connection even after it gets closed after a DB call?

4
javajdbcconnection-pooling
just curious but which connection pooling libraries launch separate threads for managing the pool? I don't think commons-dbcp does this - at least not the BasicDataSource. Rather I think the connection checking is done when the connection is checked out from the pool – matt b

4 Answers

12
votes

Connection pooling works by re-using connections. Applications "borrow" a connection from the pool, then "return" it when finished. The connection is then handed out again to another part of the application, or even a different application.

This is perfectly safe as long as the same connection is not is use by two threads at the same time.

The key point with connection pooling is to avoid creating new connections where possible, since it's usually an expensive operation. Reusing connections is critical for performance.

10
votes

The connection pool does not provide you with the actual Connection instance from the driver, but returns a wrapper. When you call 'close()' on a Connection instance from the pool, it will not close the driver's Connection, but instead just return the open connection to the pool so that it can be re-used (see skaffman's answer).

1
votes

Connection pooling reuses connections. Here is how apache dbcp works underline.

Connection poolableConnection= apacheDbcpDataSource.getConnection();

Apache DBCP implementation returns connection wrapper which is of type PoolableConnection.

poolableConnection.close();

PoolableConnection.close() inspects if actual underlying connection is closed or not, if not then it returns this PoolableConnection instance into connection pool (GenericObjectPool in this case).

if (!isUnderlyingConectionClosed) {
            // Normal close: underlying connection is still open, so we
            // simply need to return this proxy to the pool
            try {
                genericObjectPool.returnObject(this); //this is PoolableConnection instance in this case
....
              }
-1
votes

My understanding is the same as stated above and, thanks to a bug, I have evidence that it's correct. In the application I work with there was a bug, an SQL command with an invalid column name. On execution an exception is thrown. If the connection is closed then the next time a connection is gotten and used, with correct SQL this time, an exception is thrown again and the error message is the same as the first time though the incorrect column name doesn't even appear in the second SQL. So the connection is obviously being reused. If the connection is not closed after the first exception is thrown (because of the bad column name) then the next time a connection is used everything works just fine. Presumably this is because the first connection hasn't been returned to the pool for reuse. (This bug is occurring with Jave 1.6_30 and a connection to a MySQL database.)