jdbc取mysql存储过程多个结果集

 先来返回结果格式：

[
    [
        {"name":"分管公司1","id":"1000"},
        {"name":"分管公司2","id":"1001"},
        {"name":"分管公司3","id":"1014"},
        {"name":"分管公司4","id":"1015"},
        {"name":"分管公司5","id":"1016"},
        {"name":"分管公司6","id":"1017"}
    ],
    [
        {"哈哈":""}
    ],
    [
        {"呵呵":2}
    ]
]

java代码： 

调用存储过程个方法针对框架进行封装过，大家可以忽略也可替换，cs.execute();后辨识解析的返回值的代码

public List callShellProcedureList(final String param){
		System.out.println(1);
		List list = jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{call USP_PRG_CallShellProcedure(?)}";// 调用存储过程名
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, param);// 设置输入参数的值
				return cs;
			}
		}, new CallableStatementCallback<List>() {
			public List doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				List list1 =new ArrayList();
				ResultSet resultSet = cs.getResultSet();//获取第一个resultSet
				//result 封装结果集
				List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
				if(null != resultSet)
				{
					ResultSetMetaData data = resultSet.getMetaData();
					if (null != data) {
						//每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
						while (resultSet.next()) {
							Map<String, Object> map = new HashMap<String, Object>();
							for (int i = 0; i < data.getColumnCount(); i++)
								//以键值对存放数据
								//直接存放字段名称
								//map.put(data.getColumnName(i + 1), rs.getObject(i + 1));
								//直接存放字段的别名
								map.put(data.getColumnLabel(i + 1), resultSet.getObject(i + 1));
							result.add(map);
						}
						list1.add(result);
					}
					while(cs.getMoreResults())//判断是否还有结果集如果有就进入循环去取值
					{
						List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
						resultSet = cs.getResultSet();//这里去到的是当前getMoreResults()里面的getResultset
						ResultSetMetaData datas = resultSet.getMetaData();
						if (null != datas) {
							//每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
							while (resultSet.next()) {
								Map<String, Object> map = new HashMap<String, Object>();
								for (int i = 0; i < datas.getColumnCount(); i++)
									//以键值对存放数据
									//直接存放字段名称
									//map.put(data.getColumnName(i + 1), rs.getObject(i + 1));
									//直接存放字段的别名
									map.put(datas.getColumnLabel(i + 1), resultSet.getObject(i + 1));
								results.add(map);
							}
							list1.add(results);
						}
					}
				}
				return list1;
			}
		});
		return list;
	}

 

传入要调用的存储过程接口

这个方法会将多返回集以数组的方式返回出来

返回示例:

[
    [
        {"name":"分管公司1","id":"1000"},
        {"name":"分管公司2","id":"1001"},
        {"name":"分管公司3","id":"1014"},
        {"name":"分管公司4","id":"1015"},
        {"name":"分管公司5","id":"1016"},
        {"name":"分管公司6","id":"1017"}
    ],
    [
        {"哈哈":""}
    ],
    [
        {"呵呵":2}
    ]
]

存储过程代码：

begin

select s1.name,s1.id from sys_office as s1,(select LENGTH(parent_ids)  - LENGTH(REPLACE( `parent_ids`,',','' )) as countdou,parent_ids from sys_office
where parent_ids LIKE '%0,318d76c07c2540259b16c63df273d7b3,%' and del_flag =0 
GROUP BY parent_ids
order by countdou DESC 
LIMIT 0,1) as s2 
where s1.parent_ids = s2.parent_ids and s1.del_flag =0 ;
select null as `哈哈`;
select 2 as `呵呵`;

end

如果遇到问题的话可以加我qq讨论一下 1311353736