java调用sql返回list_java调用存储过程返回map组装List(含SqlServer存储过程)

柔光的暖阳◎ 2022-11-10 10:33 176阅读 0赞

《java调用存储过程返回map组装List(含SqlServer存储过程)》由会员分享，可在线阅读，更多相关《java调用存储过程返回map组装List(含SqlServer存储过程)(6页珍藏版)》请在人人文库网上搜索。

1、Action层public ActionForward getProjectPayMoneyJsonByPayType(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception JiLiangZhiFuService jiLiangZhiFuService = this.getServiceLocator().getJiLiangZhiFuService();int year = Integer.parseInt(request。

2、.getParameter(year);String data = jiLiangZhiFuService.getProjectPayMoneyJsonByPayType(year);response.setContentType(text/xml);response.getWriter().write(data);response.getWriter().flush();response.getWriter().close();return null;Service层public String getProjectPayMoneyJsonByPayType(int year)double a。

3、llTotal = 0;Map map = jiLiangZhiFuDao.getProjectPayMoneyByPayType(year);List dataItemList = SessionBean.getServiceLocator().getDataItemService().getAllDataItemByTypeId(payType);Map map\_ep = new HashMap();map\_ep.put(account\_id, 2);map\_ep.put(isSelectTwoStageProject, true);List engineeringPhaseList = 。

4、SessionBean.getServiceLocator().getEngineeringPhaseService().getEngineeringPhaseList(map\_ep).getData();Map map\_type = new HashMap();StringBuilder sb = new StringBuilder();sb.append(+engineeringPhaseList.size()+);for(EngineeringPhase engineeringPhase:engineeringPhaseList)String ep\_id = engineeringPha。

5、se.getEp\_id();sb.append();sb.append(+ep\_id+);sb.append(+engineeringPhase.getEp\_name()+);for(DataItem dataItem:dataItemList)String di\_id = dataItem.getId();String r\_key = ep\_id + , + di\_id;if(map.containsKey(r\_key)map\_type.put(di\_id, map\_type.containsKey(di\_id) ? (Double.parseDouble(map\_type.get(di\_i。

6、d).toString() + Double.parseDouble(map.get(r\_key).toString() : Double.parseDouble(map.get(r\_key).toString();sb.append(+map.get(r\_key)+);/项目支付金额合计sb.append(+map.get(engineeringPhase.getEp\_id()+);allTotal += Double.parseDouble(map.get(engineeringPhase.getEp\_id().toString();sb.append();/某支付类别金额合计sb.app。

7、end();sb.append();sb.append(合计);DecimalFormat df = new DecimalFormat(\#.00);for(DataItem dataItem:dataItemList)sb.append(+String.format(%.2f, map\_type.get(dataItem.getId()+);/各项目累计合计sb.append(+String.format(%.2f, allTotal)+);sb.append();sb.append();return sb.toString();Dao层public Map getProjectPayMon。

8、eyByPayType(final int year) try return (Map)this.getHibernateTemplate().execute(new HibernateCallback()public Object doInHibernate(Session session) throws HibernateException, SQLException Connection con = session.connection();Statement stmt = con.createStatement();CallableStatement cs = con.prepareC。

9、all(call proc\_records(?);cs.setInt(1, year);/ResultSet rs = stmt.executeQuery(call proc\_records);ResultSet rs = cs.executeQuery();Map map = new HashMap();while (rs.next() map.put(rs.getString(1), rs.getString(2);rs.close();stmt.close();return map;); catch ( org.springframework.dao.DataAccessExceptio。

10、n e) throw new DataAccessException(e.getMessage(),e);Sql：create procedure proc\_recordsyear intas DECLARE map\_table table(r\_key varchar(100),r\_value varchar(50)DECLARE type\_id varchar(40)DECLARE type\_name varchar(50)DECLARE project\_id varchar(40)DECLARE project\_name varchar(50)DECLARE payTypeMoney nu。

11、meric(16, 2)DECLARE sumPayTypeMoney numeric(16, 2)BEGINset payTypeMoney = 0set sumPayTypeMoney = 0-查询项目列表DECLARE project\_cursor CURSOR for select ep\_id,ep\_name from AB\_engineeringPhase where account\_id=2open project\_cursorfetch next from project\_cursor into project\_id,project\_namewhile FETCH\_STATUS 。

12、= 0begin-查询项目支付类别DECLARE projectType\_cursor CURSOR for select id,name from T\_DataItem where typeId = payType order by sort ascopen projectType\_cursorfetch next from projectType\_cursor into type\_id,type\_namewhile FETCH\_STATUS = 0begin-业务逻辑处理处理内层游标-获取一个项目某一项支付类型的数据-查询一个项目某一支付类别金额select payTypeMoney = 。

13、sum(checkProjectPayMoney) from JiLiangZhiFu where projectId = project\_id and payType = type\_id and enterDate CAST(year as varchar(4) and enterDate CAST(year+1 as varchar(4) and type = outer group by payType-一个项目某一支付类别金额存入insert into map\_table values(project\_id+,+type\_id,payTypeMoney)-一个项目累计支付类别金额set。

14、 sumPayTypeMoney = sumPayTypeMoney + payTypeMoney-一个项目某一支付类别金额重置为set payTypeMoney = 0fetch next from projectType\_cursor into type\_id,type\_name -内层游标向下移动一行end-插入某一项目各个支付类别的金额合计insert into map\_table values(project\_id,sumPayTypeMoney)set sumPayTypeMoney = 0close projectType\_cursordeallocate projectType\_cursorfetch next from project\_cursor into project\_id,project\_name -内层游标处理结束后,外层游标才继续向下移动一行end close project\_cursordeallocate project\_cursorselect \* from map\_tableEND。