SQL结果封装到JSONArray

SQL结果封装到JSONObject，多条JSONObject封装到一条JSONArray，

JSONArray再将里面的JSONObject一条一条取出，把value值放在ArrayList中，

最终把ArrayList Add到JSONArray。

	/**
	 * 组别人均产出率统计
	 * @return
	 */
	public JSONArray getGroupOutputRate(){
		String processId = ResourceUtil.getString("improvementProcessId", "sys-operationPortal").trim();
		String itDepartmentId = ResourceUtil.getString("itDepartmentId", "sys-operationPortal").trim();
		String sql = "SELECT D.FD_ID,D.FD_NAME,D.TOTAL_NUM,D.FINISH_NUM,E.PERSON_NUM,"
				+ " ROUND(D.TOTAL_NUM/E.PERSON_NUM,2)PERSON_AVERAGE FROM"
				+ " (SELECT B.FD_ID,B.FD_NAME,COUNT(*) TOTAL_NUM,"
				+ " NVL(SUM(CASE WHEN A.DOC_STATUS='30'THEN 1 ELSE 0 END),0)FINISH_NUM"
				+ " FROM KM_REVIEW_MAIN A"
				+ " LEFT JOIN SYS_ORG_ELEMENT B"
				+ " ON A.FD_DEPARTMENT_ID = B.FD_ID"
				+ " LEFT JOIN KM_REVIEW_TEMPLATE C"
				+ " ON A.FD_TEMPLATE_ID = C.FD_ID"
				+ " WHERE C.FD_ID = '"+processId+"' AND A.DOC_STATUS != '10' AND A.DOC_STATUS != '00'"
				+ " GROUP BY A.FD_DEPARTMENT_ID,B.FD_NAME,B.FD_ID)D LEFT JOIN"
				+ " (SELECT A.FD_NAME,A.FD_ID,COUNT(*) PERSON_NUM FROM SYS_ORG_ELEMENT A"
				+ " LEFT JOIN SYS_ORG_ELEMENT B"
				+ " ON A.FD_PARENTID = B.FD_ID"
				+ " LEFT JOIN SYS_ORG_ELEMENT C"
				+ " ON C.FD_PARENTID = A.FD_ID"
				+ " WHERE B.FD_ID='"+itDepartmentId+"' AND C.FD_ORG_TYPE='8'"
				+ " GROUP BY A.FD_ID,A.FD_NAME)E"
				+ " ON D.FD_ID = E.FD_ID";
		Query query = getBaseDao().getHibernateSession().createSQLQuery(sql);
		@SuppressWarnings("unchecked")
		List<Object> rateInfo = query.list();	
		Object[] obj = null;
		JSONObject jsonObj = new JSONObject();
		JSONArray array = new JSONArray();
		for (int a = 0; a < b = rateInfo.size(); a++) {
            //取出一条数据，内容包含[组名，提报总数...]，再分别put到JSONObject
			obj = (Object[]) rateInfo.get(a);
			jsonObj.put("groupName", (String) obj[1]);//组别名
			jsonObj.put("totalNum", String.valueOf(obj[2]));//累积提报总数
			jsonObj.put("finishNum", String.valueOf(obj[3]));//已完成
			jsonObj.put("personAverage", String.valueOf(obj[5]));//人均产出
			array.add(jsonObj);
		}
        //最终得到JSONArray格式[{"groupName":"name1","totalNum":"num1"...} , {...}]
		List<String> groupNameList = new ArrayList<String>();//组别名列表
		List<String> totalNumList = new ArrayList<String>();//累积提报总数列表
		List<String> finishNumList = new ArrayList<String>();//已完成列表
		List<String> personAverageList = new ArrayList<String>();//人均产出列表
		for(int i=0;i<array.size();i++){
            //取出一条JSONObject {"groupName":"name1","totalNum":"num1"...}
			JSONObject groupNameObj = array.getJSONObject(i);
			String groupName = groupNameObj.getString("groupName");
			String totalNum = groupNameObj.getString("totalNum");
			String finishNum = groupNameObj.getString("finishNum");
			String personAverage = groupNameObj.getString("personAverage");
			groupNameList.add(groupName);
			totalNumList.add(totalNum);
			finishNumList.add(finishNum);
			personAverageList.add(personAverage);
		}
		JSONArray jsonArray = new JSONArray();
        //将List加入到JSONArray 
		jsonArray.add(groupNameList);
		jsonArray.add(totalNumList);
		jsonArray.add(finishNumList);
		jsonArray.add(personAverageList);
		return jsonArray;	
	}

获取JSONObject数据{"dayRange":[{"TOTAL":1,"DATE":"07-18"},{"TOTAL":2,"DATE":"07-19"}]}

取出dayRange对应的数组数据放入List<Map<String, String>>，即[{key1=value1,key2=value2},{}]

将其中的value取出，放入List<String>，

最后将List<String> add到JSONArray。

	//根据url接口获取JSON数据，并返回字符串数据 [[Value1.1 , V1.2...],[V2.1 , V2.2...]...]
	public static String getJsonList(String Package,String key,String url,String item){
		//从Package包下的ApplicationResources.properties中取key的值拼接成地址
		String wholeUrl = ResourceUtil.getString(key, Package) + url;// 请求地址
		//从URL中获取JSON数据
		String result = HttpRequest.sendGet(wholeUrl, "");
		//取出JSON数据{"dayRange":[{"TOTAL":1,"DATE":"07-18"},{"TOTAL":2,"DATE":"07-19"}]}
		JSONObject json = JSONObject.fromObject(result);
		//取出dayRange对应的数组[{},{}]
		//JSONArray itemData = (JSONArray) json.get(item);
		//取出的数组放在list中[{key1=value1,key2=value2},{}]
		List<Map<String, String>> itemList = (List<Map<String, String>>)json.get(item);
		//数据放在List，
		List<String> totalList = new ArrayList<String>();
		List<String> responseTimeList = new ArrayList<String>();
		List<String> receiveTimeList = new ArrayList<String>();
		List<String> scoreList = new ArrayList<String>();
		List<String> dateList = new ArrayList<String>();
		for(Map<String, String> map:itemList){
			totalList.add(String.valueOf(map.get("TOTAL")));
			responseTimeList.add(String.valueOf(map.get("XIANGYING_TIME")));
			receiveTimeList.add(String.valueOf(map.get("JIEDAN_TIME")));
			scoreList.add(String.valueOf(map.get("SCORE")));
			dateList.add(map.get("DATE"));
		};
		//List放在JSONArray中，顺序为[[TOTAL],[XIANGYING_TIME],[JIEDAN_TIME],[SCORE],[DATE]]
		JSONArray jsonArray = new JSONArray();
		jsonArray.add(totalList);
		jsonArray.add(responseTimeList);
		jsonArray.add(receiveTimeList);
		jsonArray.add(scoreList);
		jsonArray.add(dateList);
		//返回字符串形式的JSONArray数据，"[[1,2],[1.1,1.2],[2.1,2.2],[3.1,3.2],[07-18,7-19]]"
		return jsonArray.toString();
	}

JSONObject通过List<Map<String, String>>，不如通过JSONArray将Value放入List。

优化方法：

	//优化方法
	//根据url接口获取JSON数据，并返回字符串数据 [[Value1.1 , V1.2...],[V2.1 , V2.2...]...]
	public static String getJsonString(String Package,String key,String url,String item){
		//从Package包下的ApplicationResources.properties中取key的值拼接成地址
		String wholeUrl = ResourceUtil.getString(key, Package) + url;// 请求地址
		//从URL中获取JSON数据
		String result = HttpRequest.sendGet(wholeUrl, "");
		//取出JSON数据{"dayRange":[{"TOTAL":1,"DATE":"07-18"},{"TOTAL":2,"DATE":"07-19"}]}
		JSONObject json = JSONObject.fromObject(result);
		//取出dayRange对应的数组[{},{}]
		JSONArray itemData = (JSONArray) json.get(item);
		
		List<String> totalList = new ArrayList<String>();
		List<String> responseTimeList = new ArrayList<String>();
		List<String> receiveTimeList = new ArrayList<String>();
		List<String> scoreList = new ArrayList<String>();
		List<String> dateList = new ArrayList<String>();
		for(int i=0;i<itemData.size();i++){
			//取出一条JSONObject，即{"TOTAL":1,"DATE":"07-18"...}
			JSONObject jsonObject = itemData.getJSONObject(i);
			totalList.add(jsonObject.getString("TOTAL"));
			responseTimeList.add(jsonObject.getString("XIANGYING_TIME"));
			receiveTimeList.add(jsonObject.getString("JIEDAN_TIME"));
			scoreList.add(jsonObject.getString("SCORE"));
			dateList.add(jsonObject.getString("DATE"));
		}
		
		//List放在JSONArray中，顺序为[[TOTAL],[XIANGYING_TIME],[JIEDAN_TIME],[SCORE],[DATE]]
		JSONArray jsonArray = new JSONArray();
		jsonArray.add(totalList);
		jsonArray.add(responseTimeList);
		jsonArray.add(receiveTimeList);
		jsonArray.add(scoreList);
		jsonArray.add(dateList);
		//返回字符串形式的JSONArray数据，"[[1,2],[1.1,1.2],[2.1,2.2],[3.1,3.2],[07-18,7-19]]"
		return jsonArray.toString();
	}