Spring JDBC模板-如何通过单个查询检索具有多个参数的多个结果 [英] Spring JDBC Template- How to retrieve multiple result with multiple parameters with a single query

问题描述

我有一个简单的查询

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

我正在使用相同的查询来获取基于我传递的角色ID的不同列表，例如项目名称，项目经理名称，开发人员等. 我的问题是，我需要通过传递所有角色ID并一次命中来一次性运行上述查询，然后将这些列表分配给不同的列表，例如管理者列表，开发人员列表，测试人员列表..etc并将其发送给将它们放在链接的hasmap中的UI.
例子

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;

以上所有方法getProjectManager(xxx)，getResourceOwner(yyy)，getReqLeadPri(zzz)与上述查询运行相同，但传递了不同的Roleid xxx，yyy，zzz.

我不知道如何从传递不同参数的单个查询中获取其他列表，然后分配给该列表并返回结果.

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.

我正在尝试实现这一点，因为每次调用相同的查询并每次传递不同的参数以获取不同的列表时，每次尝试从UI<-> DB分别获取列表时，UI都非常缓慢. 因此，我试图一次性获得结果.

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.

推荐答案

如果仅要触发一个sql语句，则可以使用ResultSetExtractor

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;  
    }  

}  

这篇关于Spring JDBC模板-如何通过单个查询检索具有多个参数的多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！