dbcp: number of open prepared statement

Question

Hi, I get MaxOpenPreparedStatement exception in my program. I can monitor number of objects in GenericObjectPool with getNumActive()/getNumIdle() functions. How can I get connection & prepared statement pools from org.apache.commons.dbcp.BasicDataSource object? Thanks

Answer 1

DBCP's BasicDataSource exposes the maxOpenPreparedStatements value that the datasource is configured with.

The presence of this exception seems to indicate that you are opening too many statements and not closing them however:

Since a connection usually only uses one or two statements at a time, this is mostly used to help detect resource leaks.

Answer 2

I am not sure about the answer on the actual question, but the maximum allowable amount of opened preparedstatements is usually pretty high. So I strongly suspect that the technical problem causing you to ask this question is that the JDBC code is not properly closing all the opened statements in the finally block as per the following JDBC idiom:

Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
// ...

try {
    connection = database.getConnection();
    preparedStatement = connection.prepareStatement(SQL_STRING);
    resultSet = preparedStatement.executeQuery();
    // ...
} finally {
    if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
    if (preparedStatement != null) try { preparedStatement.close(); } catch (SQLException ignore) {}
    if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
}