question regarding java.sql.resultset

Question

I have a question regarding jdbc Resultset.

I need to grab some records in one of the table. After retrieving the records, how do I search for records in the ResultSet with a list of ids?

Should i convert the resultset to a hashmap first?

Learn the `WHERE` clause and if I understand your functional requirement right, also the `IN` clause. `SELECT * FROM tablename WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)`. See also [this answer](http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause). This way you end up with exactly the rows you want in your Java code without duplicaing the entire database into Java's memory and potentially killing the Java application due to out of memory. — BalusC, Dec 27 '10 at 21:43

score 2 · Answer 1 · answered Dec 27 '10 at 09:50

After retrieving the records, how do I search for records in the ResultSet with a list of ids?

This is the wrong way to do it. You should do searching and filtering in your SQL query before you obtain the ResultSet.

Afterwards, assuming you've got ResultSet rs, iterate through it using

while (rs.next()) {
   // do whatever you want with results
}

Doing searching ON the ResultSet -> bad practise.

score 1 · Answer 2 · answered Dec 27 '10 at 09:42

Assuming you don't want to modify the SQL query to only retrieve those results in the first place, then converting the results into a more useful data structure (e.g. creating an object for each record and then storing them in a hash map by ID) is a fine way of going, yes.

score 0 · Answer 3 · answered Dec 27 '10 at 09:46

Consider A ResultSet as just a sort of interface to your DB, the ResultSet in itself contains no data so nothing much to search for.

So if you need to manipulate, search, ... you better read all the data into a structure.

A hash structure will probably be best but thats dependant on how you structured your data.

The only thing you should not do is to interate multiple times over the ResultSet because unless you use specific caching implementation you will cause lots of unnecesary database traffic

score 0 · Answer 4 · answered Dec 27 '10 at 09:47

You can traverse through the resultset through a loop with the help of "next()"[resultSet.next()] method. Get the values using getString(),getInt(),......

Ex: Let rs be my resultset,

while(rs.next()){

S.o.p("Name:"+rs.getString("name")); S.o.p("Age:"+rs.getInt("age")); }

score 0 · Answer 5 · answered Dec 27 '10 at 09:49

I agree that a HashMap will work. If the number of records is large you could save time and memory by not loading the ResultSet data into any structure. If you ORDER BY ID in your query, you can get your data straight from your ResultSet with rs.next(); easily.

question regarding java.sql.resultset

5 Answers5