Working with Result Sets

ResultSet Longevity

A ResultSet behaves differently depending on whether the autocommit property is set on or off.

If auto-commit is on

  • You are limited to one active, valid ResultSet.

  • Associated ResultSetMetaData objects, and ResultSet objects returned from Statement.getGeneratedKeys() can also be open at the same time but have the same "shelf life" - they are valid until closed, committed, or another statement is executed by the Statement object.

  • A Statement that returns a ResultSet defers committing the transaction, so iteration over the ResultSet is consistent with the data and the transaction isolation level.

  • Each execution of a Statement will commit any previously deferred transaction and so closes any associated ResultSet.

If auto-commit is off

  • You can have as many ResultSet objects as you need (up to a maximum of 1000), provided each ResultSet comes from a unique Statement instance.

  • You cannot have multiple open ResultSet objects from the same Statement.

    • When a Statement is executed, any currently associated ResultSet is closed and cannot be reopened or reused in any way.

Setting Result Set Holdability

By default, a result set created within a transaction is closed after the transaction is committed to the database. It is sometimes useful for the result set to be held open after the transaction has been committed. To prevent the result set from being closed, set the holdability property to HOLD_CURSORS_OVER_COMMIT:

Connection conn = dataSource.getConnection();
conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);

As a result of using the HOLD_CURSORS_OVER_COMMIT value, the result set is materialized in-memory. To facilitate queries with result sets that exceed memory available, NuoDB provides Spill to Disk capability. If Spill to Disk is enabled and the result set exceeds the DISKSPILL_MEMORY_THRESHOLD_MB value, the result set will be written to disk on the Transaction Engine (TE).

For more information on Spill to Disk in NuoDB, see Spill to Disk.