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 Transform ResultSet to a Map 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 "Transform ResultSet to a Map" Watch "Transform ResultSet to a Map" New topic

Transform ResultSet to a Map

Ricky Murphy
Ranch Hand

Joined: Oct 16, 2007
Posts: 31
Hello All:

I have the following ResultSet reflecting a Product table:

Comp_id Prod_id Prod_Desc Qty
----------- ----------- ---------------- -----
1 1 prod_1 50
1 2 prod_2 30
1 3 prod_3 60
2 1 prod_a 30
2 2 prod_b 20
3 1 dummy1 70
3 2 dummy2 30
3 3 dummy3 50

As you can see, this table tells you the inventory items and companies (comp_ids) who manufacture them. I have created a ProductDTO class for �product� which includes �prod_id�, �prod_desc� and �qty�.

Now I want to transform the above resultSet/table into a map, where the key is the company id (comp_id) and the value is an ArrayList of productDTO objects. Forexample, the first item of the map is (Key=1, Value=ArrayList (which contains 3 productDTO objects) and so on�).

What came to my mind was to loop through this ResultSet and create an ArrayList when �comp_id� starts to change at the beginning of each iteration. And at the last round, add the last round of collection into the map. As pseudo-code illustrated below, I can get it done. However, the whole process seems awkward and messy to me. Any suggestion to do this kind of procedure clean?? Thank you.

- Ricky

==== pseudo code =========

-get restultSet
int existing_compID = 0;
ArrayList prodArrayList = new ArrayList();
While (resultSet.hasNext()
int comp_id = resultSet.getInt(�comp_id�);
if (comp_id != existing_compID)
add to productArrayList to map;
prodArrayList = new ArrayList();
- ProductDTO productDTO = new ProductDTO();
- get data from resultSet and do productDTO.setXXX();
- add each DTO to prodArrayList;
existing_compID = comp_id;
if (!
- add the last set of prodArrayList to map;

Rob Spoor

Joined: Oct 27, 2005
Posts: 20274

How To Ask Questions How To Answer Questions
Ricky Murphy
Ranch Hand

Joined: Oct 16, 2007
Posts: 31
Hello Rob,

Thank you for the reply. That is a very slick and smart way to handle it. I am gonna give a try.

subject: Transform ResultSet to a Map
It's not a secret anymore!