CODEWITHSUNDEEP
javajdbcconnection-pooling
haps10
haps10

Reputation: 3536

JDBC Connection Pooling: Connection Reuse?

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?

Upvotes: 16

Views: 10099

Answers (4)

punitusingh
punitusingh

Reputation: 35

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

Upvotes: 1

Glystad
Glystad

Reputation: 1

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

Upvotes: -1

Tim Jansen
Tim Jansen

Reputation: 3368

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

Upvotes: 11

skaffman
skaffman

Reputation: 403611

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.

Upvotes: 13

Related Questions