Connection Pooling over New Connection instance per Thread (JDBC)

Question

Hi,

I am creating a multi-threaded application. However, I have experienced lots of unexpected behavior from my application when I have one connection object serving all threads.

I am in a dilemma. Should I let every thread create, use and dispose its own connection object or should I use a connection pool?

I have tried connection pooling which makes the application painfully shower. However, my intuition is that if I let every thread create its own connection object, I might get a "too many connection" error.

Please let me know if there is any way to assist in this.

Regards.

Answer 1

Regardless of the threading issue, you should definitely go for a connection pool. It will greatly increase connecting performance. Then to the threading issue, this is indeed a major problem. The normal JDBC idiom is to acquire and close all resources in the shortest possible scope. I.e. all should happen in the very same method block. The problem symptoms which you're describing confirms that you aren't closing those resources properly.

Closing should always happen regardless of whether the connection is coming from a pool or not. Closing a non-pooled connection will prevent it from being timed-out by the database when it's been hold open for a too long time. Closing a pooled connection will actually release it back to the pool and make it available for the next lease.

Here's how the normal JDBC idiom look like for the case of a INSERT.

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

    try { 
        // Acquire.
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_CREATE);

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