large sql resultsets in java

Question

How can I fetch large resultset in java? I have about 140,000 rows with 3 columns.

Answer 1

There's no special way to retrieve a large result set; this can be done the same as any other database query via JDBC.

The key is in how the results are handled. 140,000 small records is not too many, but if holding them all in application memory at once is a problem, consider whether they can be processed "streamwise". That is, use the information needed from each record, then discard the record before retrieving the next. This way, the memory requirement doesn't depend on the number of records in the result set.

Answer 2

i would avoid loading such a data set with any kind of complex abstraction. this sounds like a "batch job" style application.

i would recommend using raw jdbc and map it to a very compact representation, without bloat with only 3 colums this should be fairly easy to hold in memory, if the strings? are not overly big.

avoid loading 140k rows with a tool such as hibernate. it is a great tool, but you might run into memory issues if you hold that many entities in the hibernate 1st and 2nd level caches.

Answer 3

(using a java.sql.Connection to your DB):

Statement st = cnxn.createStatement();
ResultSet rs = st.executeQuery("SELECT column1,column2,your_mom FROM some_table");

while(rs.next()){
  Object ob1 =  rs.getObject(1);
  Object ob2 =  rs.getObject(2);
  Ho ob3 =  rs.getHo(3);
  doStuffWithRow(ob1,ob2,ob3);
}

Unless your DBMS is pathetically useless, results will be read from disk/memory as requested, and won't sit in your memory or anything crazy like that. Using the primitives-based ResultSet.getXXX methods is faster than getObject, but I didn't feel like specifying column types.

Just be patient and let 'er chug. Oh, and stay the heck away from ORM here.