21

When using one of the various JDBC template methods I am confused on how to iterate/scroll over large result sets (which won't fit into memory). Even without a direct exposure of an Iterable interface I would at least expect instances of RowCallbackHandler to get called while the query is executing not after it's finished (or the heap overfloats).

I did have a look a at this (which changed nothing for me despite being similar in spirit to this post on stack overflow) and at this post in the spring forums. The latter seems to suggest that the callback handler should indeed get called while the cursor is fetching data. My tests however show no such behaviour.

The database is an Oracle10g. I am using the 11.1.0.7.0-Production driver and Spring 2.5.6.SEC01. Any ideas anyone how to iterate over result sets, preferably while keeping the mapping logic of RowMapper etc.?

Community
  • 1
  • 1
yawn
  • 8,014
  • 7
  • 29
  • 34

5 Answers5

22

The Oracle JDBC driver has proper support for the setFetchSize() method on java.sql.Statement, which allows you to control how many rows the driver will fetch in one go.

However, RowMapper as used by Spring works by reading each row into memory, getting the RowMapper to translate it into an object, and storing each row's object in one big list. If your result set is huge, then this list will get big, regardless of how JDBC fetches the row data.

If you need to handle large result sets, then RowMapper isn't scaleable. You might consider using RowCallbackHandler instead, along with the corresponding methods on JdbcTemplate. RowCallbackHandler doesn't dictate how the results are stored, leaving it up to you to store them.

skaffman
  • 398,947
  • 96
  • 818
  • 769
  • 1
    setFetchSize did not change things for me, I tried using it before. Do you develop against an Oracle instance? For me RowCallBackHandler just hangs, waiting for the query to finish as I wrote in my OP. – yawn Aug 27 '09 at 19:23
  • 2
    Apparently I forgot the call afterPropertiesSet() on the JDBC template in my test. Embarrassing, but now it works :*) – yawn Aug 28 '09 at 07:58
5

It's a property of the driver/connection whether to stream data back to you or whether to send it back in one chunk. For example, in SQL Server, you use the SelectMethod property on the connection URL:

jdbc:microsoft:sqlserver://gsasql03:1433;DatabaseName=my_db;SelectMethod=direct

The value of direct means that the results should come in one go. The other choice is cursor, which allows you to specify that you want the connection to stream results back to you. I'm not sure what the analog for an Oracle data source is, I'm afraid

the RowCallbackHandler certainly works for me.

oxbow_lakes
  • 133,303
  • 56
  • 317
  • 449
  • Yes, ideally you don't put into a dataset, you would grab the records while keepin the connection open asking if there are more records. I did this in .NET and got a pretty good improvement in performance. I imagine something similar for java would exist, especially in spring. – Zoidberg Aug 27 '09 at 13:58
  • @*Zoidberg* - the OP appears to be doing the correct thing, using a row callback handler – oxbow_lakes Aug 27 '09 at 14:03
  • This sounds promising. Unfortunately I couldn't find anything similar to this SQL Server URL setting in the Oracle JDBC documentation. – yawn Aug 27 '09 at 19:25
5

You may use springjdbc-iterable library:

CloseableIterator<MyObj> iter = jt.queryForIter("select ...", params, mapper);

Iterator will be auto-closed on exhaustion or may be closed manually. It will work only within transaction bounds.

Disclaimer: I wrote this library

alexkasko
  • 4,855
  • 1
  • 26
  • 31
0
  1. Create a custom stored procedure that extends StoredProcedure
  2. Create a RowCallBackHandler that can handle each row, one at a time.
  3. Declare your parameters. If you have a Result Set, declare that one first. Use the SqlReturnResultSet class and create it using your RowCallBackHandler
  4. Declare any other parameters
  5. Compile
  6. I did steps 2 through 5 in the constructor of my customer stored procedure
  7. Create a Map containing your input parameters
  8. Execute your stored procedures with the input parameters

I would provide code, but the following article contains all of this information.

Calling Stored Procedures with Spring JDBC Templates

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob Breidecker
  • 604
  • 1
  • 7
  • 12
-3

here's a good library for pulling java sql resultsets all into memory.

http://casperdatasets.googlecode.com

you can scroll / iterate through the dataset, you can issue queries against it, and build indexes for optimization. it also implements the java.sql.resultset interface so you can continue to operate on results from this dataset with minimals chnages to your jdbc code.

jli_123
  • 215
  • 1
  • 4
  • it also has an adapter that will convert a jdbc resultset into an in-memory resultset - i assume you can invoke this adapter in your spring jdbc results callback method. – jli_123 Jan 06 '11 at 00:21
  • 1
    Sorry, but "how to iterate/scroll over large result sets (which *won't fit into memory*)". – yawn Jan 07 '11 at 11:12