This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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.
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.
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 ]
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.