JDBC MySql connection pooling practices to avoid exhausted connection pool

connection-pooling, glassfish, java, jdbc, mysql

Solution

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.

See also:

- How often should Connection, Statement and ResultSet be closed in JDBC?

- Is it safe to use a static java.sql.Connection instance in a multithreaded system?

- Closing JDBC Connections in Pool

Problem

Original source

Related problems

• Closing JDBC Connections in Pool
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• How often should Connection, Statement and ResultSet be closed in JDBC?