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

Action层

public ActionForward getProjectPayMoneyJsonByPayType(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

JiLiangZhiFuService jiLiangZhiFuService = this.getServiceLocator().getJiLiangZhiFuService();

int year = Integer.parseInt(request.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 allTotal = 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 =

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 = engineeringPhase.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_id).toString()) + Double.parseDouble(map.get(r_key).toString())) : Double.parseDouble(map.get(r_key).toString()));

sb.append("<"+di_id+">"+map.get(r_key)+"");

}

}

//项目支付金额合计

sb.append(""+map.get(engineeringPhase.getEp_id())+"");

allTotal += Double.parseDouble(map.get(engineeringPhase.getEp_id()).toString());

sb.append("");

}

//某支付类别金额合计

sb.append("");

sb.append("");

sb.append("合计");

DecimalFormat df = new DecimalFormat("#.00");

for(DataItem dataItem:dataItemList){

sb.append("<"+dataItem.getId()+">"+String.format("%.2f", map_type.get(dataItem.getId()))+"");

}

//各项目累计合计

sb.append(""+String.format("%.2f", allTotal)+"");

sb.append("");

sb.append("");

return sb.toString();

}

Dao层

public Map getProjectPayMoneyByPayType(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.prepareCall("{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.DataAccessException e) {

throw new DataAccessException(e.getMessage(),e);

}

}

Sql：

create procedure proc_records

@year int

as

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 numeric(16, 2)

DECLARE@sumPayTypeMoney numeric(16, 2)

BEGIN

set@payTypeMoney= 0

set@sumPayTypeMoney= 0

--查询项目列表

DECLARE project_cursor CURSOR for select ep_id,ep_name from AB_engineeringPhase where account_id='2'

open project_cursor

fetch next from project_cursor into@project_id,@project_name

while@@FETCH_STATUS= 0

begin

--查询项目支付类别

DECLARE projectType_cursor CURSOR for select id,name from T_DataItem where typeId='payType'order by sort asc

open projectType_cursor

fetch next from projectType_cursor into@type_id,@type_name

while@@FETCH_STATUS= 0

begin

--业务逻辑处理处理内层游标

--获取一个项目某一项支付类型的数据

--查询一个项目某一支付类别金额

select@payTypeMoney=sum(checkProjectPayMoney)from JiLiangZhiFu where projectId=@project_id and payType=@type_id and enterDate>CAST(@year as varchar(4))and enterDate

--一个项目某一支付类别金额存入

insert into@map_table values(@project_id+','+@type_id,@payTypeMoney)

--一个项目累计支付类别金额

set@sumPayTypeMoney=@sumPayTypeMoney+@payTypeMoney

--一个项目某一支付类别金额重置为

set@payTypeMoney= 0

fetch next from projectType_cursor into@type_id,@type_name--内层游标向下移动一行end

--插入某一项目各个支付类别的金额合计

insert into@map_table values(@project_id,@sumPayTypeMoney)

set@sumPayTypeMoney= 0

close projectType_cursor

deallocate projectType_cursor

fetch next from project_cursor into@project_id,@project_name--内层游标处理结束后,外层游标才继续向下移动一行end

close project_cursor

deallocate project_cursor

select*from@map_table

END