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

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

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.

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: (link redacted, it pointed to a spyware page)

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