Spring JDBC Template- How to retrieve multiple result with multiple parameters with a single query

Question

I have a simple query which is

select distinct roleid,firstname,lastname where role_id='210';

I am using the same query for fetching the different lists such as projectnames ,projectmanagers name,developers...etc..based on the roleid that I am passing. My problem is I need to run the above query in a single shot by passing all the roleid and retrieve them in a single hit and assign those list to different list such as managers list ,developers list ,testers list ..etc and send it to the UI putting them in a linked hasmap.
Example

LinkedHashMap<String, List<SelectItem>> results = new LinkedHashMap<String, List<SelectItem>>();
    List<SelectItem> getProjectManager = getProjectManager(xxx);
    List<SelectItem> getResourceOwnerSE = getResourceOwner(yyy);
    List<SelectItem> getReqLeadPri = getReqLeadPri(zzz);
    results.put("getProjectManager", getProjectManager);
    results.put("getResourceOwner", getResourceOwnerSE);
    results.put("getReqLeadPri", getReqLeadPri);
    return results;

All the above methods getProjectManager(xxx),getResourceOwner(yyy),getReqLeadPri(zzz) runs with same query as mentioned above but passing different roleid xxx,yyy,zzz.

I don't know how to fetch a different list from a single query passing different parameters and assign to the list and return the results.

I am trying to achieve this because the UI is very slow when I try to get the lists individually from UI <-> DB each time when calling the same query passing different parameters each time for fetching different list. Hence, I am trying to get the results in a single shot.

score 0 · Accepted Answer · answered Jan 06 '14 at 17:05

0

If you want to fir only one sql statement you can use ResultSetExtractor

public class SelectItemResultSetExtractor implements ResultSetExtractor<LinkedHashMap<String, List<SelectItem>>>{  

    public LinkedHashMap<String, List<SelectItem>> extractData(ResultSet rs) throws SQLException,  
            DataAccessException {  

        LinkedHashMap<String, List<SelectItem>> result = new ...
        //put the 3 categories with empty arraylists


        while(rs.next()){
            SelectItem item= new SelectItem();
            item.setRoleid(rs.getInt(1))  
            item.setFirstName(rs.getInt(2));  
            item.setLastName(rs.getString(3));

            //if item.getRoleid() is ProjManager
            // then put in the list of the ProjManager
            result.get("ProjManager").add(item);
            //if item.getRoleid() is ResourceOwnerSE
            // then put in the list of the ResourceOwnerSE
            ...
        }


        return result;  
    }  

}

answered Jan 06 '14 at 17:05

Evgeni Dimitrov

21,976
33
120
145

And what might be the query that i need to run inside the loop above passing all the role id's? – Karthik Jan 06 '14 at 18:09
select distinct roleid,firstname,lastname where role_id in ('210','211',.../*the id's of the other categories */); or if the categories you listed are all that are posible you can skip the where clause... – Evgeni Dimitrov Jan 06 '14 at 18:14
I did as per the above and i got the various list but I find difficulty in assigning each list to a different list in UI. LinkedHashMap> results = got the results(); But how to iterate the above to assign each list to another list like for (Map.Entry> entry :results.entrySet()) { String key = entry.getKey();List values = entry.getValue System.out.println("Key = " + key); system.out.println("Values = " + values + "n");buh the above "Values " does not returns a valid list its returning objects like [javax.xx.yy.SelectItem@1d3 – Karthik Jan 07 '14 at 11:23
Linked that above question in [link](http://stackoverflow.com/questions/20970793/iterating-linkedhashmapstring-listselectitem-results-linkedhashmapstring-l) – Karthik Jan 07 '14 at 12:01

Spring JDBC Template- How to retrieve multiple result with multiple parameters with a single query

1 Answers1

Linked