JDBC ResultSet

JDBC ResultSet is a table of data returned by a SQL query executed on a database.

JDBC ResultSet Behavior

The following table compares the behavior of ResultSet depending on AutoCommit. AutoCommit is configured using the setAutoCommit() method on the connection object.

AutoCommit ON AutoCommit OFF

	AutoCommit ON	AutoCommit OFF
Transaction commit	Transaction commit happens automatically after each statement. For statements that return a ResultSet, commit is deferred until iteration is complete.	Transaction commit happens only with manual commit.
Number of active `ResultSet`	One active `ResultSet` is allowed per statement. Executing a new query closes the previous `ResultSet`.	Multiple active `ResultSet` are allowed, provided each `ResultSet` comes from a different `Statement` instance. There cannot be multiple open `ResultSet` objects from the same `Statement`.
`ResultSetMetaData`	Metadata is valid as long as the associated `ResultSet` is open. Metadata becomes invalid when `ResultSet` is closed, committed, or another query is executed by the `Statement` object. It becomes invalid when `ResultSet` is closed.	Metadata valid as long as the associated `ResultSet` is open. Multiple metadata can exist simultaneously. Metadata becomes invalid when the associated `ResultSet` is closed.
Iteration over `ResultSet`	A `Statement` that returns a `ResultSet` defers committing the transaction until iteration is finished, so that the data is consistent with the transaction isolation level. Execution of a new SQL query using will commit the previously deferred transaction and close the associated `ResultSet`.	Iteration over the `ResultSet` can be done safely as long as the `ResultSet` is valid. When the transaction is committed, any currently associated `ResultSet` is closed and cannot be reopened or reused in any way.

Transaction commit

Transaction commit happens automatically after each statement. For statements that return a ResultSet, commit is deferred until iteration is complete.

Transaction commit happens only with manual commit.

Number of active ResultSet

One active ResultSet is allowed per statement. Executing a new query closes the previous ResultSet.

Multiple active ResultSet are allowed, provided each ResultSet comes from a different Statement instance. There cannot be multiple open ResultSet objects from the same Statement.

ResultSetMetaData

Metadata is valid as long as the associated ResultSet is open. Metadata becomes invalid when ResultSet is closed, committed, or another query is executed by the Statement object. It becomes invalid when ResultSet is closed.

Metadata valid as long as the associated ResultSet is open. Multiple metadata can exist simultaneously. Metadata becomes invalid when the associated ResultSet is closed.

Iteration over ResultSet

A Statement that returns a ResultSet defers committing the transaction until iteration is finished, so that the data is consistent with the transaction isolation level. Execution of a new SQL query using will commit the previously deferred transaction and close the associated ResultSet.

Iteration over the ResultSet can be done safely as long as the ResultSet is valid. When the transaction is committed, any currently associated ResultSet is closed and cannot be reopened or reused in any way.

JDBC ResultSet Holdability

By default, a ResultSet created within a transaction is closed after the transaction is committed to the database. To prevent the ResultSet from getting closed and to hold it open after the transaction has been committed, set the setHoldability property to HOLD_CURSORS_OVER_COMMIT.

For example:

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

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