transform ResultSet data into a DTO

[Arnav Velimala]

Hi,

Am trying to retrieve the following data from the database.

The table has "SupplierID, SupplierName, SiteId, SiteName" information.
For a given supplierID, there can be more than one siteID's.
The Query is
"SELECT DISTINCT supplier_id, supplier_name, site_division_id, site_division_name FROM spend ORDER BY supplier_id"
And there are some 5000 records.

Can any one suggest me the best way to populate these values in my DTO's?

Is it fine if I use a Map to store these retrieved values -
Map with Key having the supplierDTO(which has id & name attributes) and values being a *List* of SiteDTO(with id & name attributes)'s.
As one supplier can have more than one site, I'll be searching for the supplier in the Map before inserting.

Please suggest.

thanks,
Arnav

[Campbell Ritchie]

That suggestion would work, but I can't help thinking there is a way to write an SQL query which will do that work for you.