Is there a limit on the number of open JDBC ResultSets?

Question

I'm currently using JDBC with MySQL, and I'm wondering whether there is a limit on the number of ResultSet objects which can be open at a given time. I wasn't able to find an answer anywhere else on SO or in the documentation.

I'm asking because my program can potentially generate and manipulate a fairly high number of these ResultSets, and I'm afraid of any consequences this could have. My alternative to this would be to create a set of classes which mimics the ResultSet methods which I need, however this would require such a class for every table in my database (and I have many of them).

Could you post some relevant code and perhaps give some more context? — Mick Mnemonic, Jul 23 '15 at 15:39
The question wasn't intended to be context-specific, I just wanted to know if in general there is a limit to the number of open ResultSet objects. — atzol, Jul 24 '15 at 19:43

score 2 · Answer 1 · answered Jul 23 '15 at 15:46

Each open JDBC ResultSet, in addition to the resources it requires in the JVM, maintains an open cursor on the database side, in MySQL as well as in other DBMSes.

Each open cursor obviously requries server-side resources, such as memory, possibly disk space, locks etc. In MySQL each cursor is implemented as an in-memory temporary table, which potentially can be materialized as an on-disk table, as described in the documentation.

In other words, maintaining too many open ResultSets can potentially exhaust database server resources and cause concurrency problems, such as lock contention or deadlocks. It is best to keep them (as well as the number of connections, as others have mentioned) to a minimum.

Stuart Caie · Accepted Answer · 2015-07-23T16:06:21.793

Each ResultSet belongs to a Statement, and each Statement is associated with a database cursor, which is generally a limited resource in databases. MySQL doesn't have an upper limit, but having too many open cursors will affect database performance

You don't have to close ResultSets (for example it will be closed and reopened if you re-execute the statement) but you must close Statements or you will leak database resources.

From the ResultSet documentation:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Some databases like Oracle will return errors such as ORA-01000: Too many open cursors once you exceed the (configurable) per-session limit.

In general, you should close Statements as soon as possible. Using Java 7's try-with-resources is very convenient:

try (final Statement s = conn.createStatement()) {
    final ResultSet rs = s.executeQuery(sql);
    while (rs.next()) {
        // process each row of the result
    }
}  // statement is auto-closed

If you are repeating the same query multiple times where only the query parameters vary, use a PreparedStatement.

Is there a limit on the number of open JDBC ResultSets?

2 Answers2