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

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?

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(int rows) method on the ResultSet. This will allow you to control how big the blocks it retrieves at once.

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);