统计：后台通过查询数据表（该表存放统计的sql语句）返回规定格式的json数据

package com.cxy.template.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;


import com.cxy.template.modle.CountData;
import com.cxy.template.service.CountService;
import com.cxy.template.util.HtmlUtil;


import net.sf.json.JSONObject;


/**
 * @author Administrator
 * @date 2018年1月13日
 * @tage 统计（统一接口）（包括粉丝统计和演员薪酬统计） 统计sql保存到数据库中的stat_function表中
 * @return
 */
@Controller
@RequestMapping("/tongji")
public class CountController {
@Autowired
private CountService countService;


// 数据统计
@RequestMapping("/getCountData")
@ResponseBody
public void getCountData(HttpServletRequest request, HttpServletResponse response) {
String group_name = request.getParameter("group_name");
String p_id = request.getParameter("p_id");
JSONObject result = new JSONObject();
int status = 0;
String msg = "获取成功";
List<CountData> countDataResultList = null;
List<Map<String, Object>> mapSum = null;
List<Map<String, Object>> maplist = new ArrayList<>();
Map<String, Object> returnMap = new HashMap<>();
// Map<String, Object> nameMap = new HashMap<>();
String[] str = null;


if (group_name != null && !"".equals(group_name)) {
try {
// 获得要统计的sql语句
countDataResultList = countService.getCountData(group_name);
// 防止下标越界
if (countDataResultList.size() > 0) {
// 逐条查询统计结果
for (int i = 0; i < countDataResultList.size(); i++) {
String sql_name = countDataResultList.get(i).getStat_sql();


mapSum = countService.getData(IsNeedParam(p_id, sql_name));
// 防止下标越界
if (mapSum.size() > 0) {
List listname = new ArrayList<>();


// 对于有或没有group by字段的sql 语句来选择处理的方式
if (sql_name.indexOf("GROUP BY") != -1) {
str = getSplitString(str, sql_name);
// 得到反回的数据结构
returnMap = getDataConstructionGroupBy(mapSum, str,
countDataResultList.get(i).getBack_count());
// 公共的返回Map：subtitle，type,name
returnMap = getCommonReturnMap(returnMap, countDataResultList, i);
} else {
// 得到反回的数据结构
returnMap = getDataConstruction(mapSum);
// 公共的返回Map：subtitle，type,name
returnMap = getCommonReturnMap(returnMap, countDataResultList, i);
listname.add(countDataResultList.get(i).getCount_name());
returnMap.put("name", listname);
}
} else {
status = -1;
msg = "无相关数据！";
}


maplist.add(returnMap);
}
} else {
status = -1;
msg = "无相关数据";
}
} catch (Exception e) {
e.printStackTrace();
status = -1;
msg = "出现错误";
} finally {
result.put("series", maplist);
// 小心下标越界if
if (countDataResultList.size() > 0) {
result.put("title", countDataResultList.get(0).getTitle());
}


result.put("status", status);
result.put("msg", msg);
HtmlUtil.writerJson(response, result.toString());
}
} else {
status = -1;
msg = "group_name不能为空";
result.put("series", maplist);
result.put("status", status);
result.put("msg", msg);
HtmlUtil.writerJson(response, result.toString());
}


}


public static String IsNeedParam(String p_id, String sql_name) {
/*
* 判断是否参数为空
*/
String flag = "?";
String new_sql_name = null;
if (!"".equals(p_id) && p_id != null) {
if (sql_name.contains(flag)) {
/*
*1、 先取到？对应的位置n
*2、从n开始，从后往前取“FROM”的位置m
*3、截取字符串 sm 从n-m(即：from xxxx ?)
*4、判断sm是否包含where
**/  
int n = sql_name.indexOf("?");
int m = sql_name.toUpperCase().lastIndexOf("FROM", n);
String sm = sql_name.substring(m, n);
if (Iscontain(sm, "where")) {
new_sql_name = sql_name.replace(flag, "and p_id=" + " '" + p_id + "' ");
} else {
new_sql_name = sql_name.replace(flag, "where p_id=" + " '" + p_id + "' ");
}
} else {
if (sql_name.contains(flag)) {
new_sql_name = sql_name.replace(flag, "");
} else {
new_sql_name = sql_name;
}
}
} else {
if (sql_name.contains(flag)) {
new_sql_name = sql_name.replace(flag, "");
} else {
new_sql_name = sql_name;
}
}
return new_sql_name;
}


// 判断是否有字符串，不区分大小写
public static boolean Iscontain(String input, String regex) {
if ("".equals(input) || input == null) {
return false;
}
Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(input);
boolean result = m.find();
return result;
}


// 截取sql分组的字符串：group by "team"
public static String[] getSplitString(String[] strlist, String str) {
int i = str.indexOf("GROUP BY ");
String ss = str.substring(i + 9);
int j = ss.indexOf(" ");
if (j < 0) {
strlist = ss.split(",");
} else {
strlist = ss.substring(0, j).split(",");
}
return strlist;
}


// 公共返回Map
public static Map<String, Object> getCommonReturnMap(Map<String, Object> rtMap, List<CountData> countDataResultList,
int back) {
rtMap.put("subtitle", countDataResultList.get(back).getSubtitle().trim());
rtMap.put("type", countDataResultList.get(back).getFigure_type().trim());
return rtMap;
}


// 返回没有“group by”字符串的数据结构
public static Map<String, Object> getDataConstruction(List<Map<String, Object>> maps) {


Map<String, Object> map = new HashMap<>();
List<String> listString = new ArrayList<>();
List<Integer> listInteger = new ArrayList<>();
List<Object> listIntegerObject = new ArrayList<>();
for (int i = 0; i < maps.size(); i++) {
for (Entry<String, Object> entry : maps.get(i).entrySet()) {
listString.add(entry.getKey().trim());
listInteger.add(Integer.parseInt(String.valueOf(entry.getValue()).trim()));
}
}
listIntegerObject.add(listInteger);
map.put("xData", listString);
map.put("yData", listIntegerObject);
return map;
}


// 返回有“group by”字符串的数据结构
public static Map<String, Object> getDataConstructionGroupBy(List<Map<String, Object>> maps, String[] str,
int back) {
Map<String, Object> map = new HashMap<>();
List<String> listName = new ArrayList<>();
List<String> listString = new ArrayList<>();
int[][] listInteger = new int[maps.get(0).entrySet().size() - back][maps.size()];
// List<Object> listIntegerObject = new ArrayList<>();
for (int i = 0; i < maps.size(); i++) {
int j = 0;
for (Entry<String, Object> entry : maps.get(i).entrySet()) {
/*
* String s = str[0].replace(" ", ""); String ss =
* entry.getKey().replace(" ", ""); Boolean flag = s.equals(ss);
*/
if ((str[0].replace(" ", "")).equals((entry.getKey().replace(" ", "")))) {
listName.add(String.valueOf(entry.getValue()).trim());
} else {


if (i < 1) {
listString.add(entry.getKey());
}
if (entry.getValue() instanceof Double) {
listInteger[j][i] = Integer.parseInt(
entry.getValue().toString().substring(0, entry.getValue().toString().indexOf(".")));
} else {
listInteger[j][i] = Integer.parseInt(String.valueOf(entry.getValue()).trim());
}
j++;


}
}
}
// listIntegerObject.add(listInteger);
map.put("xData", listName);
map.put("yData", listInteger);
map.put("name", listString);
return map;
}
}