File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes OutOfMemoryError Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "OutOfMemoryError" Watch "OutOfMemoryError" New topic


Blikkies Marais
Ranch Hand

Joined: Jun 12, 2003
Posts: 50
I am using JDBC to query a database using stmt.execute(), getting a resultset. The result set may be thousands of rows, but I only want to show a batch of 20 at a time.
The result set is a java object and therefore takes up memory, and if there are alot of users, alot of memory. - I get a java.lang.OutOfMemoryError Exception.
What is the best way to reduce the size of my result set just to the relevant rows? Do I need to declare a cursor? (How do I do that?) or are there better techniques?
Also we still need to know the number of rows in the entire resultset. (eg. 21 to 40 of 1000)
[ July 11, 2003: Message edited by: Blikkies Marais ]
Les Hayden

Joined: Jun 19, 2002
Posts: 28
I'm not sure what is causing your out of memory condition.
However, you can try to adjust the fetch size of a record set by using the statement method setFetchSize(int numRows). However, that only gives a *suggestion* to the driver and different dbs react to that method differently.
The default fetch size is only 25. If you use 0, that frees the db to choose what it think is optimal(which it may do anyway)
Hope this helps.

Les Hayden
Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
If your just looking for a quick way to limit the result set then I suggest digging into your specific database's sql syntax to see what phase to use to limit the reset set. For example in db2, the phrase is something like "select ... from ... where ... fetch first 20 rows only".
If you need to do batches of sql, then it depends on the table and primary key structure. Lets say the primary key was an integer field and was sequential as in 1, 2, 3, ... and so on. Then your sql could be "select ... from ... where key <= 20 order by key". Then the next group would have a where clause of "where key > 20 and <= 40" and so on.
You'd have to programmetically keep track of where you are and piece together the sql accordingly. The two methods can be combined together for dealing with more complicated keys.
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
I need to check to give you the exact syntax, but there are scrollable result sets avbl in JDBC2.0.

For this we need to create the preparedStatement with TYPE_SCROLL_INSENSITIVE set to false.
I shall check the actual syntax and post again.

Best Regards,<br />Nagendra Prasad.
I agree. Here's the link:
subject: OutOfMemoryError
It's not a secret anymore!