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

(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.