Large list returned from a SimpleJdbcTemplate query

Question

here is my problem : at some point in my Java program, I get a (very) big List of Events from a database using the SimpleJdbcTemplate class from Spring.

List<Event> events = 
            this.simpleJdbcTemplate.query(myQuery,
            myMapper(), 
            new Object[] {
                    filter.getFirst(),
                    filter.getSecond(),
                    filter.getThird()}
            );

The problem is that the list may contain something like 600,000 Events ... Therefore using a lot of memory (and also taking time to be processed).

However I don't really need to retrieve all the Events at once. Actually I would like to be able to iterate over the list, read only a few events (linked to a specific KEY_ID - the sql query myQuery is ordered by KEY_ID), process them and finally get back iterating, letting the garbage collector get rid of the previous and already processed Events so that I never exceed a certain amount of memory.

Is there a nice way to do so using the Spring library (or any library)?

Cheers, Vakimshaar.

Answer 1

I think part of your problem is that your query is executing all at once and you're getting the result set in a big lump that hogs memory and network bandwidth. In addition to needing a way to iterate through the result set you need a way to get the results back from the database a bit at a time. Take a look at this answer about lazy-loading resultsets. It looks like you could set the fetch size in combination with using a ResultSetExtractor and possibly get the desired behavior (depending on the database).

Answer 2

You should construct your SQL query to return limited set of items starting with particular number. It is database specific operation (in Oracle and MySql you will manipulate rownum in some form) . Then you repeat the call increasing start number until all elements are processed.

Oracle example

SELECT * FROM ([your query]) WHERE rownum>=[start number] 
                               AND rownum<[start number + chunk size];

Answer 3

If I understand correctly, you would like to iterate over the result set, but are not interested in building the full list of results.

Just use the query method with a ResultSetExtractor as argument. The ResultSetExtractor can use your mapper to transform the current row into an Event. Put every event into a list until you reach a different KEY_ID or the end of the result set, then proceed with your list of events and clear the list.