Java – JDBC Connection Pooling: Connection Reuse

connection-poolingjavajdbc

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

create connections during app initialization and put in a cache
provide these cached connections on demand to the app
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?

Best Answer

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.

Related Solutions

Java – Connection pooling options with JDBC: DBCP vs C3P0

DBCP is out of date and not production grade. Some time back we conducted an in-house analysis of the two, creating a test fixture which generated load and concurrency against the two to assess their suitability under real life conditions.

DBCP consistently generated exceptions into our test application and struggled to reach levels of performance which C3P0 was more than capable of handling without any exceptions.

C3P0 also robustly handled DB disconnects and transparent reconnects on resume whereas DBCP never recovered connections if the link was taken out from beneath it. Worse still DBCP was returning Connection objects to the application for which the underlying transport had broken.

Since then we have used C3P0 in 4 major heavy-load consumer web apps and have never looked back.

UPDATE: It turns out that after many years of sitting on a shelf, the Apache Commons folk have taken DBCP out of dormancy and it is now, once again, an actively developed project. Thus my original post may be out of date.

That being said, I haven't yet experienced this new upgraded library's performance, nor heard of it being de-facto in any recent app framework, yet.

Java – JDBC MySql connection pooling practices to avoid exhausted connection pool

The exception indicates a typical case of application code which leaks database connections. You need to ensure that you acquire and close all of them (Connection, Statement and ResultSet) in a try-with-resources block in the very same method block according the normal JDBC idiom.

public void create(Entity entity) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_CREATE);
    ) { 
        statement.setSomeObject(1, entity.getSomeProperty());
        // ...
        statement.executeUpdate();
    }
}

Or when you're not on Java 7, in a try-finally block. Closing them in finally will guarantee that they are also closed in case of exceptions.

public void create(Entity entity) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;

    try { 
        connection = dataSource.getConnection();
        statement = connection.prepareStatement(SQL_CREATE);
        statement.setSomeObject(1, entity.getSomeProperty());
        // ...
        statement.executeUpdate();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }
}

Yes, you still need to close connections yourself, even when using connection pooling. It's a common mistake among starters that they think that it will then automatically handle the close. This is not true. The connection pool namely returns a wrapped connection which does something like the following in the close():

public void close() throws SQLException {
    if (this.connection is still eligible for reuse) {
        do not close this.connection, but just return it to pool for reuse;
    } else {
        actually invoke this.connection.close();
    }
}

Not closing them would cause the connection not being released back to the pool for reuse and thus it will acquire a new one again and again until the DB runs out of connections which will cause your application to crash.

Best Answer

Related Solutions

Java – Connection pooling options with JDBC: DBCP vs C3P0

Java – JDBC MySql connection pooling practices to avoid exhausted connection pool

See also:

Related Topic