How can I fetch large resultset in java? I have about 140,000 rows with 3 columns.
1
-
1Well, the same way you a fetch small result set in java. What are your concerns? Performance, memory usage,...?– b.rothCommented Jul 24, 2009 at 20:27
Add a comment
|
5 Answers
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.
answered Jul 24, 2009 at 20:27
-
1You can also use paging which is built into JDBC, if I remember correctly.– aperkinsCommented Jul 24, 2009 at 20:28
-
1
-
-
@Rams I'm pretty sure it means discard from local memory, and not from the original DB (if that's what you meant). Commented Oct 8, 2015 at 12:19
-
1By "discard" I mean dereference all data from that record so that it can be garbage collected. So, if you are creating a Java object from fields in each row, don't try to retain a reference to all the objects created from all the rows. Create one object for a row, use it, and then move the next, losing your reference to the previous object so that it is collected.– ericksonCommented Oct 8, 2015 at 15:29
(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.
-
I just tried this on Postgres 9.1 -- it tried to fetch the whole table into memory! Commented Dec 21, 2011 at 17:33
-
1Aha - a Postgres-specific problem -- I'll post the solution in a separate answer. Commented Dec 21, 2011 at 17:42
-
to do this with jdbc postgresql driver, you need to call
setAutoCommit(false)on theconnectionthen callsetFetchSize(FETCH_SIZE)on yourpreparedStatement. but streaming fetch api is implemented per driver, and not consistent over all of jdbc. quite annoying.– dwardCommented Nov 22, 2013 at 4:43
If you're using PostgreSQL, you'll need to setup the Connection and the Statement as follows:
Connection cnxn = ...;
cnxn.setAutoCommit(false);
Statement stmnt = cnxn.createStatement();
stmnt.setFetchSize(1);
Otherwise your query is liable to try & load everything into memory. Thanks to http://abhirama.wordpress.com/2009/01/07/postgresql-jdbc-and-large-result-sets/ for documenting that.
answered Dec 21, 2011 at 17:41
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.
answered Jul 24, 2009 at 20:31
I recommend a batch style fetch instead of loading everything. There are performance considerations at the database end when executing a query with a big result set.
