CODEWITHSUNDEEP
javasqlresultset
lock
lock

Reputation: 6614

How do I search a resultset in java?

I'm trying to minimize my SQL queries so my resolve was to list down all ID's of the records I would need. Doing so, with one query, I can get all info regarding a certain ID.

ID          Passed      flag    type    Total
123ABC      878         0       1       1070
123ABC      1272        0       1       1507
123ABC      1152        0       1       1342
123ABC      0           0       2       0
123ABC      1045        0       1       1214
123ABC      1270        0       1       1471
123ABC      0           0       1       0
123ABC      476         0       2       787
123ABC      96          0       2       158
123ABC      23          0       3       70
123ABC      11          0       2       42
987ZYX      77          1       2       135
987ZYX      0           1       2       0
987ZYX      0           1       2       0
123ABC      487         1       2       513
123ABC      1           1       2       3
987ZYX      293         0       2       759
987ZYX      5611        0       1       6386
987ZYX      0           0       2       0
987ZYX      0           0       3       0

The above table shows a sample of the results I'd get after running my query. Problem is, I need to loop through my list of ID and get all the records from this result set that is related to that ID. Is it possible to convert this table (a ResultSet object actually) into a ArrayList or HashMap for easier indexing or can I achieve it using the actual ResultSet?

Upvotes: 0

Views: 786

Answers (3)

Andreas Dolk
Andreas Dolk

Reputation: 114847

You'd have to copy the complete result set into another datastructure in order to work with its "content". A usual way is creating a class that can hold the values of a single row (aka: bean):

 class Data {
   private String id;
   private int passed;
   private boolean flag;
   private int type;   // or an enum, if "types have names"
   private int total;
   public Data(){};
   // getters, setters for all fields
 }

After having read the table, create a collection and add a bean for each row:

 List<Data> result = new ArrayList<Data>();
 while (rs.next()) {
   Data data = new Data();
   data.setId(rs.getString(0));
   // ..
 }

Now we have a collection where we can work with the actual content of the table.

If you want to filter the data, like reading only rows with a certain ID value, you should consider creating a SQL SELECT command that just returns values for rows WHERE ID='MyID'.

Upvotes: 0

James Anderson
James Anderson

Reputation: 27486

Just do it in the SQL!

All you need is a simple "ORDER BY ID" in your SQL statement.

It is nearly always more efficeient and effective to do your set manipulation in SQL. The key point to remember is that the people who write query engines are better programmers than you or I and they have probably been tuning the SQL engine for longer than you have been programming.

Upvotes: 2

Tom
Tom

Reputation: 44881

It sounds like you should be able to do this with a sub-query you select from the results of another query in one SQL statement.

Here's a link: http://beginner-sql-tutorial.com/sql-subquery.htm

Actually re-reading your question it sounds more like you just need to add to the where clause something along the lines where ID = '123ABC'

Upvotes: 1

Related Questions