Java 查询存储过程 结果含有多个结果集

// 查询污染源显示的表头
private JSONObject getDataGridTitleByPs(
final String pscode) {
final List<List<Map<String, Object>>> columns = new ArrayList<>();
final List<Map<String, Object>> topColumns = new ArrayList<>();
final HashMap<String, Object> numMap = new HashMap<String, Object>();

// final int num = 0;

final List<Map<String, Object>> bottomColumns = new ArrayList<>();
basedao.getSession().doWork(new Work() {
// int num = 0;
@Override
public void execute(Connection conn) throws SQLException {
CallableStatement statement = conn
.prepareCall("{call Report_PSOnlineSituationJAdvancedDataGridTitleDataSource(?)}");
statement.setBigDecimal(1, new BigDecimal(pscode));
statement.execute();

int num = 0;
while (true) {
if (statement.getUpdateCount() != -1) {
statement.getMoreResults();
continue;
}
ResultSet rs = statement.getResultSet(); //查到的结果集
if (rs != null) {
while (rs.next()) {
Map<String, Object> column = new HashMap<>();
if ("root".equals(rs.getString("ParentID"))
&& !"MonitorTime".equals(rs.getString("ID"))) {
column.put("field", rs.getString("ID"));
if (StringUtil.toNull(rs
.getString("outputname")) == null) {
column.put("title", rs.getString("Ping"));
} else {
column.put(
"title",
DESDecrypt.decrypt(rs
.getString("outputname"))
+ rs.getString("Ping"));
}
num++;
numMap.put("num", num);
// System.out.println("num====="+num);
column.put("colspan", 3);
topColumns.add(column);
}else if ("root".equals(rs.getString("ParentID"))
&& "MonitorTime".equals(rs.getString("ID"))) {
Map<String, Object> first = new HashMap<>();
first.put("field", "MonitorTime");
first.put("title", "监测时间");
first.put("rowspan", 2);
topColumns.add(first);
}else{
Map<String, Object> column1 = new HashMap<>();
column1.put("field", rs.getString("ID"));
String string1 = rs.getString("ID").toString();
int ParentidLength = rs.getString("ParentID").length();
String string3 = string1.substring(ParentidLength, string1.length());

if(string3.equals("ShouldCounts")){
column1.put("title", "应报数");
}else if (string3.equals("Counts")) {
column1.put("title", "实报数");
} else {
column1.put("title", "上报率");
}

bottomColumns.add(column1);
}
}
break;
}
}
}
});

columns.add(topColumns);
columns.add(bottomColumns);
JSONObject js=new JSONObject();
js.put("col",columns);
js.put("num",numMap.get("num"));
return js;
}

// 查询数据
@Override
public Object getStaticAnalysisData(HttpServletRequest request) {
String pscode = request.getParameter("psname");
String begintime = request.getParameter("begintime");
String endtime = request.getParameter("endtime");
String reporttypefont = request.getParameter("reporttype");
int num =Integer.parseInt(request.getParameter("num"));
String reporttype = null;
if(reporttypefont.equals("5")){
reporttype = "4";
} else {
reporttype = request.getParameter("reporttype");
}
JSONObject data = new JSONObject();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int lineNum = 0; // 多少条记录合并为一条数据就是num
int count = 0;

//开始查询存储过程中的数据
SessionFactory tSessionFactory = BeanFactory
.getBean(SessionFactory.class);
@SuppressWarnings("deprecation")
ConnectionProvider cp = ((SessionFactoryImplementor) tSessionFactory)
.getConnectionProvider();
try {
CallableStatement statement = cp.getConnection().prepareCall(
"{call Proc_GetPSDataReportSituation(?,?,?,?,?)}");
statement.setBigDecimal(1, new BigDecimal(pscode));
if (StringUtil.isNotBlank(begintime)
&& StringUtil.isNotBlank(endtime)) {
statement.setTimestamp(2, Timestamp.valueOf(begintime));
statement.setTimestamp(3, Timestamp.valueOf(endtime));
} else {
statement.setTimestamp(2,
Timestamp.valueOf(DateUtil.getCurDate() + " 00:00:00"));
statement.setTimestamp(3,
Timestamp.valueOf(DateUtil.getCurDate() + " 23:59:59"));
}
statement.setInt(4, new Integer(reporttype));
statement.setInt(5, 0);
statement.execute();

ResultSet rs = null;
StringBuffer sb = new StringBuffer();
int rsNum = 0;// 统计结果集的数量


int updateCount = -1;
// boolean flag = statement.execute();// 这个而尔值只说明第一个返回内容是更新计数还是结果集。
do {
updateCount = statement.getUpdateCount();
if (updateCount != -1) {// 说明当前行是一个更新计数
// 处理.
System.out.println("..说明当前行是一个更新计数..:"+updateCount);
statement.getMoreResults();
continue;// 已经是更新计数了,处理完成后应该移动到下一行
// 不再判断是否是ResultSet
}
rs = statement.getResultSet();
if (rs != null) {// 如果到了这里,说明updateCount == -1
// 处理rs
rsNum++;
System.out.println("统计结果集的数量:" + rsNum);
if (rs != null) {
ResultSetMetaData rsmd = rs.getMetaData(); // 获取字段名
int numberOfColumns = rsmd.getColumnCount(); // 获取字段数

System.out.println("numberOfColumns:" + numberOfColumns);

if(numberOfColumns > 1){
// System.out.println("列数大于1了，可以取出数值:");
int i = 0;
int rowIndex = 0;

while (rs.next()) { // 将查询结果取出
StringBuffer lineSb = new StringBuffer();
Map<String, Object> record = null;
for (i = 1; i <= numberOfColumns; i++) {
// System.out.println(rs.getInt("总页数"));
String date = rs.getString(i);
sb.append(date + " ");
lineSb.append(date + " ");
record = new HashMap<String,Object>();
record.put("MonitorTime",rs.getString("MonitorTime"));
record.put("ShouldCounts", rs.getString("ShouldCounts"));
record.put("Counts", rs.getString("Counts"));
record.put("ReportRate", rs.getString("ReportRate"));
record.put("OutputId", rs.getString("OutputId"));
}
list.add(record);
System.out.println(rowIndex++ +":"+ lineSb.toString());
}
}
rs.close();
}
System.out.println("~~~~~~~~~~~~~~~~~:"+statement.getMoreResults());
continue;
// 是结果集,处理完成后应该移动到下一行
}
// 如果到了这里,说明updateCount == -1 && rs == null,什么也没的了
System.out.println(sb.toString());
} while (!(updateCount == -1 && rs == null));
// callsta.getXXX(int);//获取输出参数
} catch (Exception e) {
e.printStackTrace();
}

// 将从库中获取到的数据，同一日期的进行合并
List<Map<String, Object>> lastlist = new ArrayList<Map<String, Object>>();
if (list != null && list.size() > 0) {
if(reporttype.equals("1")){ //当为周评价时，数据没有按照时间排序，也没有统计
int daynum = 0;
// 获取当天时间
Date day = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String today = format.format(day);//"2016-03-03 10:46:46";//

int today1 = compare_date(today,begintime);
int todayf1 = compare_date(today,endtime);

if(today1 == 1 && todayf1 == -1){
daynum = Calendar.getInstance().get(java.util.Calendar.DAY_OF_WEEK)-1;
} else{
daynum = 7;
}
for(int k=0;k<daynum;k++){
HashMap<String, Object> map11 = new HashMap<String,Object>();
map11.put("MonitorTime", list.get(k).get("MonitorTime"));
String MonitorTimeCompare = list.get(k).get("MonitorTime").toString();
for(int p=k;p<list.size();p++){
if(MonitorTimeCompare.equals(list.get(p).get("MonitorTime"))){
map11.put(list.get(p).get("OutputId")+"ShouldCounts", list.get(p).get("ShouldCounts"));
map11.put(list.get(p).get("OutputId")+"Counts", list.get(p).get("Counts"));
map11.put(list.get(p).get("OutputId")+"ReportRate", list.get(p).get("ReportRate")+"%");

}
}
lastlist.add(map11);
}

}else{
int i = 0;
int j=0;
for(i=0;i<list.size();i=i+j){
HashMap<String, Object> map1 = new HashMap<String, Object>();
map1.put("MonitorTime", list.get(i).get("MonitorTime"));
for(j=0;j<num;j++){
// List<Map<String, Object>> listInMap = new ArrayList<Map<String, Object>>();
// HashMap<String, Object> map3= new HashMap<String, Object>();
// map3.put("ShouldCounts", list.get(i+j).get("ShouldCounts"));
// map3.put("Counts", list.get(i+j).get("Counts"));
// map3.put("ReportRate", list.get(i+j).get("ReportRate"));
// listInMap.add(map3);
//
// HashMap<String, Object> map2 = new HashMap<String, Object>();
// map2.put(list.get(i+j).get("OutputId").toString(), listInMap);
//
// lastlist.add(map2);

map1.put(list.get(i+j).get("OutputId")+"ShouldCounts", list.get(i+j).get("ShouldCounts"));
map1.put(list.get(i+j).get("OutputId")+"Counts", list.get(i+j).get("Counts"));
map1.put(list.get(i+j).get("OutputId")+"ReportRate", list.get(i+j).get("ReportRate")+"%");

}
lastlist.add(map1);
}
}

}
//分页
// int page = 1;
// int rows = 10;
// List<Map<String, Object>> pagelist = new ArrayList<Map<String, Object>>();
// try {
// page = Integer.parseInt(request.getParameter("page"));
// rows = Integer.parseInt(request.getParameter("rows"));
// } catch (Exception e) {
// }
// for (int r = (page-1)*rows; r < page*rows; r++) {
// pagelist.add(lastlist.get(r));
// }
// data.put("rows", pagelist);
// data.put("total", lastlist.size());

data.put("rows", lastlist);
return data.toString();
}
//比较两个时间的大小
public static int compare_date(String DATE1, String DATE2) {

DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt1 = df.parse(DATE1);
Date dt2 = df.parse(DATE2);
if (dt1.getTime() > dt2.getTime()) {
// System.out.println("dt1 在dt2前");
return 1;
} else if (dt1.getTime() < dt2.getTime()) {
// System.out.println("dt1在dt2后");
return -1;
} else {
return 0;
}
} catch (Exception exception) {
exception.printStackTrace();
}
return 0;
}