Does a ResultSet load all data into memory or only when requested?

Question

I have a .jsp page where I have a GUI table that displays records from an Oracle database. This table allows typical pagination behaviour, such as "FIRST", "NEXT", "PREVIOUS" and "LAST". The records are obtained from a Java ResultSet object that is returned from executing a SQL statement.

This ResultSet might be very big, so my question is:

If I have a ResultSet containing one million records but my table only displays the data from the first ten records in the ResultSet, is the data only fetched when I start requesting record data or does all of the data get loaded into memory entirely once the ResultSet is returned from executing a SQL statement?

Answer 1

The Java ResultSet is a pointer (or cursor) to the results in the database. The ResultSet loads records in blocks from the database. So to answer your question, the data is only fetched when you request it but in blocks.

If you need to control how many rows are fetched at once by the driver, you can use the setFetchSize() method on the ResultSet. This will allow you to control how big the blocks it retrieves at once.

Answer 2

While the JDBC spec does not specify whether or not the all data in the result set would get fetched, any well-written driver won't do that.

That said, a scrollable result set might be more what you have in mind: http://www.exampledepot.com/egs/java.sql/CreateScrollableResultSet.html

You may also consider a disconnected row set, that's stored in the session (depending on how scalable your site needs to be): http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/RowSet.html

Answer 3

The JDBC spec does not specify whether the data is streamed or if it is loaded into memory. Oracle streams by default. MySQL does not. To get MySQL to stream the resultset, you need to set the following on the Statement:

    pstmt = conn.prepareStatement(
        sql,
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY);
    pstmt.setFetchSize(Integer.MIN_VALUE);

Answer 4

The best idea is make a sub query and display 100 or 1000 rows at a time/in single page. And managing the connection by connection pooling.

To make a sub query you can use Row count in oracle and Limit in MY SQL.