Reputation: 6614
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
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
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
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