There are really 2 practical approaches I know of to working with very very large result sets and getting through all of them, although they're not appropriate or practical for all scenarios.
The approach I generally prefer is to make use of what we used to call a producer/consumer paradigm (I don't know what the analogous "Design Pattern" is called these days, maybe "filter") to "stream" the data through your process. In this paradigm, you initialize your top-level producer object and pass it to your ultimate consumer, the thing that's spitting out your result. The top-level producer may also be a consumer from lower-level producers; you can chain them up as a sequence of "filters". Initializing the top-level producer object may require you to pass it a lower-level initialized producer, or it may already know everything it needs to in order to initialize any lower-level producers it references; that's a design decision. Either way, you'll ultimately come down to initializing your
JDBC stuff: preparing your statement and
possibly executing (personally, I prefer delaying execution until the very very last possible moment, at the first ).
Then, once everything is set up, your top consumer just starts iterating through the objects produced by the top producer (although if you have a long chain, you don't want to have both a hasNext() and next() taking you to the bottom twice. Instead, either use an exception on next() or have next() produce a testable object that will signal the end).
The other approach I know, is what you've suggested, the "chunking" approach. In general this doesn't work that well with a relational database for fundamental reasons; RDBMSs are really designed for working with unordered sets in a stateless way and you really need it to be ordered and stateful. With Oracle, you can get the chunks like this:
However, this has 2 problems. The first is that this will sort the full set for each chunk. It has to, because the database is stateless and the data is unordered, but for large data sets, sorting can be a huge amount of work, repeated over and over. The second is that, internally, Oracle is iterating over the first rows of the result to get to the chunk you want; as you go deeper and deeper into the ordered set, it takes longer and longer. (However, it's usually much much faster to discard the rows in the DB than to transfer them to
Java and discard them there.)