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 How do I get a Subset of a ResultSet 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 "How do I get a Subset of a ResultSet" Watch "How do I get a Subset of a ResultSet" New topic

How do I get a Subset of a ResultSet

Steve Dyke
Ranch Hand

Joined: Nov 16, 2004
Posts: 1562
I am getting a result set from a remote data source. After the resultset is returned how can I do a select on the result to return a subset of records?

My code:

Manuel Leiria
Ranch Hand

Joined: Jul 13, 2007
Posts: 171
The best way, in my opinion, is store the values in one ArrayList

now, close the result set ( as soon you release the result set, better).
You have the result set inside the arrayList and you can iterate and get what ever you want

Manuel Leiria<br /> <br />--------------<br />Peace cannot be kept by force; it can only be achieved by understanding. <br /> Albert Einstein
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 15082

The best thing you can do is modify your SQL statement so that it only returns the data that your application needs. That way, the database doesn't have to waste time on retrieving and sending back to your program the data that you are going to throw away.

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Bill Cruise
Ranch Hand

Joined: Jun 01, 2007
Posts: 148
I agree with Jesper above. If you only want one subset of the ResultSet you should modify the SQL.

However, if you need more than one subset, Manuel's approach is a good one. You can usually subdivide the ArrayList in one iteration. Note that you probably don't want to try to do this when you're iterating over the ResultSet. This leads to delays in closing the ResultSet.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

Jesper is right, you shouldn't query for data that you are going to throw away unless the performance difference is nominal (such as you were using 99% of the data).

A lot of ppl try to 'play' with result sets as is they are normal java objects, and you should keep in mind they are not. They are transport objects designed to ship information to/from the database and have their own very special properties. It would be like taking a moving van 'out for a spin on a racetrack'. If you have more specialized needs for the data, get it out of the resultset first into your own optimized data structure. As I mentioned to someone who posted on something similar the other day, its short work to turn a resultset into an array of Hash Maps.
[ July 18, 2007: Message edited by: Scott Selikoff ]

[OCA 8 Book] [Blog]
Ashok Mor
Ranch Hand

Joined: Jul 17, 2007
Posts: 44
Yes, You can work while modifying an SQL query, It also take less time in terms of execution database operation. Further after getting ResultSet you can initiate an bean instance for each row you get from table and store it into array list.

And later you can process on araylist, no need to go for searching into database.

Ashok Mor
I agree. Here's the link:
subject: How do I get a Subset of a ResultSet
It's not a secret anymore!