Java 使用Query动态拼接SQl
之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。
参数接收DTO
1 /** 2 * 自定义报表 3 */ 4 public class DefinedReportFormDTO { 5 /** 6 * 指标id 7 */ 8 private List<Long> ids; 9 /** 10 * 开始时间 11 */ 12 @DateTimeFormat(pattern = "yyyy-MM") 13 private Date startTime; 14 /** 15 * 结束时间 16 */ 17 @DateTimeFormat(pattern = "yyyy-MM") 18 private Date endTime; 19 /** 20 * 频率 21 */ 22 private String timeStyle; 23 /** 24 * 机构id 25 */ 26 private List companyIds; 27 28 private boolean avg =false; 29 30 private String idsParam; 31 32 private String companyIdsParam; 33 34 public void setCompanyIdsParam(String companyIdsParam) { 35 this.companyIdsParam = companyIdsParam; 36 } 37 38 public void setIdsParam(String idsParam) { 39 this.idsParam = idsParam; 40 } 41 42 public String getCompanyIdsParam() { 43 return companyIdsParam; 44 } 45 46 public String getIdsParam() { 47 return idsParam; 48 } 49 public boolean isAvg() { 50 return avg; 51 } 52 53 public void setAvg(boolean avg) { 54 this.avg = avg; 55 } 56 57 58 public Date getStartTime() { 59 return startTime; 60 } 61 62 public void setStartTime(Date startTime) { 63 this.startTime = startTime; 64 } 65 66 public Date getEndTime() { 67 return endTime; 68 } 69 70 public void setEndTime(Date endTime) { 71 this.endTime = endTime; 72 } 73 74 public String getTimeStyle() { 75 return timeStyle; 76 } 77 78 public void setTimeStyle(String timeStyle) { 79 this.timeStyle = timeStyle; 80 } 81 82 public List<Long> getIds() { 83 return ids; 84 } 85 86 public void setIds(List<Long> ids) { 87 this.ids = ids; 88 } 89 90 public List getCompanyIds() { 91 return companyIds; 92 } 93 94 public void setCompanyIds(List companyIds) { 95 this.companyIds = companyIds; 96 } 97 98 }
数据返回VO
1 public class DefinedReportFormVO implements Serializable { 2 private String time; 3 private List<Map<String, Object>> arr = new ArrayList<>(); 4 5 public String getTime() { 6 return time; 7 } 8 9 public void setTime(String time) { 10 this.time = time; 11 } 12 13 public List<Map<String, Object>> getArr() { 14 return arr; 15 } 16 17 public void setArr(List<Map<String, Object>> arr) { 18 this.arr = arr; 19 } 20 21 22 }
控制器Controller
1 @GetMapping("/report/defindReport") 2 public JsonResponseExt defindReport(DefinedReportFormDTO definedReportFormDTO){ 3 4 5 6 7 //测试数据 8 9 10 List list1 = new ArrayList<>(); 11 list1.add("111"); 12 definedReportFormDTO.setIds(list1); 13 definedReportFormDTO.setTimeStyle("month"); 14 definedReportFormDTO.setAvg(true); 15 16 17 Calendar instance = Calendar.getInstance(); 18 instance.set(2018,1,11); 19 definedReportFormDTO.setStartTime(instance.getTime()); 20 instance.setTime(new Date()); 21 definedReportFormDTO.setEndTime(instance.getTime()); 22 23 24 return JsonResponseExt.success(dataAcquisitionFileInfoService.defindQuery(definedReportFormDTO)); 25 26 }
服务类Service
1 public interface DataAcquisitionFileInfoService { 2 3 List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter); 4 5 }
实现类ServiceImpl
1 @SuppressWarnings("unchecked") 2 @Override 3 public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) { 4 5 6 /** 7 8 9 * 定义五张表的查询字符串，年月，和机构id默认查询 10 */ 11 StringBuilder orgInformationCbrc = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id ,"); 12 StringBuilder orgBasicInformation = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); 13 StringBuilder orgBusinessStructure = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); 14 StringBuilder orgProfit = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); 15 StringBuilder orgBalanceSheets = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); 16 17 //定义机构的字符串 18 StringBuilder companyIds = new StringBuilder(""); 19 //查询所有机构 20 List<Company> orgList = orgService.getOrgList(); 21 22 //拼接所有机构的字符串（如果需要求平均数的话） 23 for (Company company : orgList) { 24 companyIds.append(company.getId()+","); 25 } 26 27 companyIds.deleteCharAt(companyIds.length()-1); 28 //定义每个表的字符串判断 29 Map<String ,String> bool = new HashMap<>(); 30 31 //指标名 32 List<String> fieldNames = new ArrayList(); 33 //返回结果 34 List<Map<String,Object>> result = new ArrayList<>(); 35 36 //指标名默认添加年月机构id 37 fieldNames.add("reportingYear"); 38 fieldNames.add("reportingMonth"); 39 fieldNames.add("companyId"); 40 //定义指标id集合 41 List ids = parameter.getIds(); 42 //循环所有的指标 43 for (Object id : ids) { 44 //如果指标为空 45 if (!"".equals(id) && id != null) { 46 //根据指标id查询指标 47 OrgStatisticalIndicators orgStatisticalIndicators = orgStatisticalIndicatorsRespository.findByIdAndAndDelFlag(Long.parseLong(id.toString())); 48 if(("year".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getYearQuery())) || ("month".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getMonthQuery()))){ 49 /** 50 * 判断指标所在的表，然后为各自的表拼接上表的字段 51 */ 52 if ("org_information_cbrc".equals(orgStatisticalIndicators.getTableName())) { 53 orgInformationCbrc.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 54 // 55 if (bool.get("org_information_cbrc") == null) { 56 bool.put("org_information_cbrc", orgStatisticalIndicators.getTableField()); 57 } 58 //如果其他表不存在这个属性则为其他表拼接null 59 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); 60 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); 61 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); 62 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); 63 64 //行业平均 65 if (parameter.isAvg()) { 66 if("year".equals(parameter.getTimeStyle())){ 67 orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); 68 }else{ 69 orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 70 } 71 72 73 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 74 75 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 76 77 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 78 79 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 80 81 82 83 84 } 85 86 87 } else if ("org_basic_information".equals(orgStatisticalIndicators.getTableName())) { 88 if (bool.get("org_basic_information") == null) { 89 bool.put("org_basic_information", orgStatisticalIndicators.getTableField()); 90 } 91 92 orgBasicInformation.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 93 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); 94 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); 95 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); 96 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); 97 98 //行业平均 99 if (parameter.isAvg()) { 100 if("year".equals(parameter.getTimeStyle())){ 101 orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); 102 }else{ 103 orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 104 } 105 106 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 107 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 108 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 109 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 110 111 } 112 113 } else if ("org_business_structure".equals(orgStatisticalIndicators.getTableName())) { 114 orgBusinessStructure.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 115 if (bool.get("org_business_structure") == null) { 116 bool.put("org_business_structure", orgStatisticalIndicators.getTableField()); 117 } 118 119 120 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); 121 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); 122 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); 123 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); 124 125 //行业平均 126 if (parameter.isAvg()) { 127 if("year".equals(parameter.getTimeStyle())){ 128 orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); 129 }else{ 130 orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 131 } 132 133 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 134 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 135 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 136 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 137 138 139 140 141 142 } 143 } else if ("org_profit".equals(orgStatisticalIndicators.getTableName())) { 144 orgProfit.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 145 if (bool.get("org_profit") == null) { 146 bool.put("org_profit", orgStatisticalIndicators.getTableField()); 147 } 148 149 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); 150 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); 151 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); 152 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); 153 154 //行业平均 155 if (parameter.isAvg()) { 156 if("year".equals(parameter.getTimeStyle())){ 157 orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); 158 }else{ 159 orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 160 } 161 162 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 163 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 164 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 165 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 166 167 168 169 } 170 171 } else if ("org_balance_sheets".equals(orgStatisticalIndicators.getTableName())) { 172 orgBalanceSheets.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 173 if (bool.get("org_balance_sheets") == null) { 174 bool.put("org_balance_sheets", orgStatisticalIndicators.getTableField()); 175 } 176 177 178 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); 179 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); 180 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); 181 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); 182 183 //行业平均 184 if (parameter.isAvg()) { 185 if("year".equals(parameter.getTimeStyle())){ 186 orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); 187 }else{ 188 orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 189 } 190 191 192 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 193 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 194 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 195 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); 196 197 } 198 } 199 if (parameter.isAvg()==true) { 200 fieldNames.add(orgStatisticalIndicators.getField()); 201 fieldNames.add(orgStatisticalIndicators.getField()+"Avg"); 202 } else { 203 fieldNames.add(orgStatisticalIndicators.getField()); 204 } 205 206 } 207 208 } 209 } 210 211 212 //拼接where条件 213 StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1"); 214 215 216 if("year".equals(parameter.getTimeStyle())){ 217 whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' "); 218 }else{ 219 whereSql.append(" and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) )>= :startYear and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) ) <= :endYear"); 220 } 221 222 //获取所有机构id 223 List parameterCompanyIds = parameter.getCompanyIds(); 224 //如果机构id不为空 225 if (parameterCompanyIds.size()>0) { 226 whereSql.append(" AND company_id in ( "); 227 228 229 for (int i = 0; i < parameterCompanyIds.size(); i++) { 230 whereSql.append(":s"+i+" ,"); 231 } 232 233 whereSql.deleteCharAt(whereSql.length()-1); 234 whereSql.append(" )"); 235 } 236 237 //定义Query 238 Query orgBalanceSheetsQuery = null; 239 240 241 242 //拼接五张表和条件 243 orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1); 244 orgBalanceSheets.append(" from org_balance_sheets "); 245 orgBalanceSheets.append(whereSql); 246 247 orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1); 248 orgBasicInformation.append(" from org_basic_information "); 249 orgBasicInformation.append(whereSql); 250 251 orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1); 252 orgBusinessStructure.append(" from org_business_structure "); 253 orgBusinessStructure.append(whereSql); 254 255 orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1); 256 orgInformationCbrc.append(" from org_information_cbrc "); 257 orgInformationCbrc.append(whereSql); 258 259 260 orgProfit.deleteCharAt(orgProfit.length()-1); 261 orgProfit.append(" from org_profit "); 262 orgProfit.append(whereSql); 263 264 265 //关联五张表 266 orgBalanceSheets.append(" UNION "); 267 orgBalanceSheets.append(orgBasicInformation.toString()); 268 269 orgBalanceSheets.append(" UNION "); 270 orgBalanceSheets.append(orgBusinessStructure.toString()); 271 272 orgBalanceSheets.append(" UNION "); 273 orgBalanceSheets.append(orgInformationCbrc.toString()); 274 275 orgBalanceSheets.append(" UNION "); 276 orgBalanceSheets.append(orgProfit.toString()); 277 278 279 System.out.println(">>"+orgBalanceSheets.toString()); 280 281 282 //创建本地sql查询实例 283 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString()); 284 285 //如果时间为空那就获取现在的时间 286 if(parameter.getEndTime() == null){ 287 parameter.setEndTime(new Date()); 288 } 289 if(parameter.getStartTime() == null){ 290 parameter.setStartTime(new Date()); 291 } 292 293 294 if("year".equals(parameter.getTimeStyle())){ 295 296 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy")); 297 298 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy")); 299 }else if("month".equals(parameter.getTimeStyle())){ 300 301 302 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM")); 303 304 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM")); 305 306 307 } 308 309 310 311 312 if (parameterCompanyIds.size()>0) { 313 314 for (int i = 0; i < parameterCompanyIds.size(); i++) { 315 orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i)); 316 } 317 } 318 319 320 //获取数据 321 List resultList = orgBalanceSheetsQuery.getResultList(); 322 323 324 System.out.println("resultList==="+resultList); 325 326 //给数据设置属性 327 for (int i = 0; i < resultList.size(); i++) { 328 Object o = resultList.get(i); 329 Object[] cells = (Object[]) o; 330 Map<String,Object> map = new HashMap<>(); 331 if(cells.length == 3){ 332 continue; 333 } 334 for (int j = 0; j<cells.length; j++) { 335 336 if (cells[j] != null && !"".equals(cells[j].toString())) { 337 map.put((String) fieldNames.get(j),cells[j]); 338 }else{ 339 setField(resultList,fieldNames,map,i,j); 340 } 341 342 } 343 result.add(map); 344 } 345 346 System.out.println("result == "+result); 347 348 349 List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>(); 350 Map<String,List> stringListMap = new HashMap<>(); 351 352 353 354 //定义返回的格式 355 for (Map<String, Object> map : result) { 356 String reportingYear = (String) map.get("reportingYear"); 357 String reportingMonth = (String) map.get("reportingMonth"); 358 String reportingDate = reportingYear+"-"+reportingMonth; 359 //如果时间类型是年 360 if ("year".equals(parameter.getTimeStyle())) { 361 List list = stringListMap.get(reportingYear); 362 if (list != null) { 363 list.add(map); 364 stringListMap.put(reportingYear,list); 365 }else{ 366 List inner =new ArrayList(); 367 inner.add(map); 368 stringListMap.put(reportingYear,inner); 369 } 370 }else{//如果为月 371 372 List list = stringListMap.get(reportingDate); 373 if (list != null) { 374 list.add(map); 375 stringListMap.put(reportingDate,list); 376 }else{ 377 List inner =new ArrayList(); 378 inner.add(map); 379 stringListMap.put(reportingDate,inner); 380 } 381 } 382 383 } 384 385 System.out.println("stringListMap == "+stringListMap); 386 387 388 for (Map.Entry<String,List> entry : stringListMap.entrySet()) { 389 DefinedReportFormVO formVO = new DefinedReportFormVO(); 390 formVO.setTime(entry.getKey()); 391 392 if(parameter.isAvg()==true){ 393 formVO.setArr(setAvg(entry.getValue(),fieldNames)); 394 }else{ 395 formVO.setArr(entry.getValue()); 396 } 397 398 definedReportFormVOList.add(formVO); 399 400 } 401 402 403 return definedReportFormVOList; 404 }
指标实体
1 /** 2 * 统计指标 3 */ 4 @Entity 5 @Table(name = "org_statistical_indicators", catalog = "zhsupervision") 6 public class OrgStatisticalIndicators { 7 @Id 8 @GeneratedValue 9 private Long id; 10 /** 11 * 前端显示名 12 */ 13 private String name; 14 /** 15 * 表属性 16 */ 17 private String tableField; 18 /** 19 * 表名称 20 */ 21 private String tableName; 22 /** 23 * 创建时间 24 */ 25 private Date createTime; 26 /** 27 * 更新时间 28 */ 29 private Date updateTime; 30 /** 31 * 删除标识 32 */ 33 private String delFlag; 34 //父节点 35 private Long pId; 36 //属性 37 private String field; 38 //该指标查询月的时候是否查询 39 private String monthQuery; 40 //该指标查询年的时候是否查询 41 private String yearQuery; 42 43 public String getMonthQuery() { 44 return monthQuery; 45 } 46 47 public void setMonthQuery(String monthQuery) { 48 this.monthQuery = monthQuery; 49 } 50 51 public String getYearQuery() { 52 return yearQuery; 53 } 54 55 public void setYearQuery(String yearQuery) { 56 this.yearQuery = yearQuery; 57 } 58 59 public String getField() { 60 return field; 61 } 62 63 public void setField(String field) { 64 this.field = field; 65 } 66 67 public Long getId() { 68 return id; 69 } 70 71 public void setId(Long id) { 72 this.id = id; 73 } 74 75 public Long getpId() { 76 return pId; 77 } 78 79 public void setpId(Long pId) { 80 this.pId = pId; 81 } 82 83 public String getName() { 84 return name; 85 } 86 87 public void setName(String name) { 88 this.name = name; 89 } 90 91 public String getTableField() { 92 return tableField; 93 } 94 95 public void setTableField(String tableField) { 96 this.tableField = tableField; 97 } 98 99 public String getTableName() { 100 return tableName; 101 } 102 103 public void setTableName(String tableName) { 104 this.tableName = tableName; 105 } 106 107 public Date getCreateTime() { 108 return createTime; 109 } 110 111 public void setCreateTime(Date createTime) { 112 this.createTime = createTime; 113 } 114 115 public Date getUpdateTime() { 116 return updateTime; 117 } 118 119 public void setUpdateTime(Date updateTime) { 120 this.updateTime = updateTime; 121 } 122 123 public String getDelFlag() { 124 return delFlag; 125 } 126 127 public void setDelFlag(String delFlag) { 128 this.delFlag = delFlag; 129 } 130 131 132 }
指标Service
1 /** 2 * 统计指标服务类 3 */ 4 public interface OrgStatisticalIndicatorsService { 5 /** 6 * 根据id获取 7 * @param id 8 * @return 9 */ 10 OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id); 11 12 /** 13 * 根据表名查询 14 */ 15 List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name); 16 17 }
指标serviceImpl
1 @Service 2 public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl<OrgStatisticalIndicators, String> implements OrgStatisticalIndicatorsService { 3 4 @Autowired 5 private OrgStatisticalIndicatorsRespository respository; 6 7 @Override 8 public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) { 9 return respository.findByIdAndAndDelFlag(id); 10 } 11 12 @Override 13 public List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name) { 14 return respository.findOrgStatisticalIndicatorsByTableName(name); 15 } 16 }
指标repository
1 public interface OrgStatisticalIndicatorsRespository extends JpaSpecificationExecutor { 2 3 @Query(value = "select * from org_statistical_indicators WHERE ID=?1 and del_flag = '0'",nativeQuery = true) 4 OrgStatisticalIndicators findByIdAndAndDelFlag(Long id); 5 6 @Query(value = "select * from org_statistical_indicators WHERE del_flag = '0' and NAME =?1",nativeQuery = true) 7 OrgStatisticalIndicators findOrgStatisticalIndicatorsByName(String name); 8 9 }
这个repository要继承 extends JpaRepository<T, ID> 才可以，写漏了。
上面使用了union 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
写完之后我发现语句太长并且嵌套了子查询后执行的时间也变长了，有时候还会卡，所以我优化了一下，指标查询指标，统计查询统计这样的执行时间就变短了，而且后来需求有所改变，有区分年查询和月查询。以下是我对实现类和指标实体的修改。
指标实体：
1 /** 2 * 统计指标 3 */ 4 @Entity 5 @Table(name = "org_statistical_indicators", catalog = "zhsupervision") 6 public class OrgStatisticalIndicators { 7 @Id 8 @GeneratedValue 9 private Long id; 10 /** 11 * 前端显示名 12 */ 13 private String name; 14 /** 15 * 表属性 16 */ 17 private String tableField; 18 /** 19 * 表名称 20 */ 21 private String tableName; 22 /** 23 * 创建时间 24 */ 25 private Date createTime; 26 /** 27 * 更新时间 28 */ 29 private Date updateTime; 30 /** 31 * 删除标识 32 */ 33 private String delFlag; 34 35 private Long pId; 36 37 private String field; 38 39 private String monthQuery; 40 41 private String yearQuery; 42 43 private String isQuery; 44 45 private String avgQuery; 46 47 48 49 50 public String getAvgQuery() { 51 return avgQuery; 52 } 53 54 public void setAvgQuery(String avgQuery) { 55 this.avgQuery = avgQuery; 56 } 57 58 public String getIsQuery() { 59 return isQuery; 60 } 61 62 public void setIsQuery(String isQuery) { 63 this.isQuery = isQuery; 64 } 65 66 public String getMonthQuery() { 67 return monthQuery; 68 } 69 70 public void setMonthQuery(String monthQuery) { 71 this.monthQuery = monthQuery; 72 } 73 74 public String getYearQuery() { 75 return yearQuery; 76 } 77 78 public void setYearQuery(String yearQuery) { 79 this.yearQuery = yearQuery; 80 } 81 82 public String getField() { 83 return field; 84 } 85 86 public void setField(String field) { 87 this.field = field; 88 } 89 90 public Long getId() { 91 return id; 92 } 93 94 public void setId(Long id) { 95 this.id = id; 96 } 97 98 public Long getpId() { 99 return pId; 100 } 101 102 public void setpId(Long pId) { 103 this.pId = pId; 104 } 105 106 public String getName() { 107 return name; 108 } 109 110 public void setName(String name) { 111 this.name = name; 112 } 113 114 public String getTableField() { 115 return tableField; 116 } 117 118 public void setTableField(String tableField) { 119 this.tableField = tableField; 120 } 121 122 public String getTableName() { 123 return tableName; 124 } 125 126 public void setTableName(String tableName) { 127 this.tableName = tableName; 128 } 129 130 public Date getCreateTime() { 131 return createTime; 132 } 133 134 public void setCreateTime(Date createTime) { 135 this.createTime = createTime; 136 } 137 138 public Date getUpdateTime() { 139 return updateTime; 140 } 141 142 public void setUpdateTime(Date updateTime) { 143 this.updateTime = updateTime; 144 } 145 146 public String getDelFlag() { 147 return delFlag; 148 } 149 150 public void setDelFlag(String delFlag) { 151 this.delFlag = delFlag; 152 } 153 }
实现类Impl：
1 @SuppressWarnings("unchecked") 2 @Override 3 public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) { 4 5 6 /** 7 * 定义五张表的查询字符串，年月，和机构id默认查询 8 */ 9 StringBuilder orgInformationCbrc = new StringBuilder("select ID as cbrcId, reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,"); 10 StringBuilder orgBasicInformation = new StringBuilder("select ID as basicId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,"); 11 StringBuilder orgBusinessStructure = new StringBuilder("select ID as businessId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,"); 12 StringBuilder orgProfit = new StringBuilder("select ID as profitId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId ,"); 13 StringBuilder orgBalanceSheets = new StringBuilder("select ID as balanceId, reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId ,"); 14 /** 15 * 平均数sql 16 */ 17 StringBuilder orgInformationCbrcAvg = new StringBuilder("select reporting_year ,reporting_month , "); 18 StringBuilder orgBasicInformationAvg = new StringBuilder("select reporting_year ,reporting_month , "); 19 StringBuilder orgBusinessStructureAvg = new StringBuilder("select reporting_year ,reporting_month , "); 20 StringBuilder orgProfitAvg = new StringBuilder("select reporting_year ,reporting_month , "); 21 StringBuilder orgBalanceSheetsAvg = new StringBuilder("select reporting_year ,reporting_month , "); 22 23 24 //指标名 25 List<String> fieldNames = new ArrayList(); 26 27 //指标名默认添加年月机构id 28 fieldNames.add("id"); 29 fieldNames.add("reportingYear"); 30 fieldNames.add("reportingMonth"); 31 fieldNames.add("companyId"); 32 33 //记录是哪一个表的平均数 34 List<String> orgInformationCbrcAvgField = new ArrayList<>(); 35 List<String> orgBasicInformationAvgField = new ArrayList<>(); 36 List<String> orgBusinessStructureAvgField = new ArrayList<>(); 37 List<String> orgProfitAvgField = new ArrayList<>(); 38 List<String> orgBalanceSheetsAvgField = new ArrayList<>(); 39 40 41 orgInformationCbrcAvgField.add("reportingYear"); 42 orgInformationCbrcAvgField.add("reportingMonth"); 43 44 orgBasicInformationAvgField.add("reportingYear"); 45 orgBasicInformationAvgField.add("reportingMonth"); 46 47 orgBusinessStructureAvgField.add("reportingYear"); 48 orgBusinessStructureAvgField.add("reportingMonth"); 49 50 orgProfitAvgField.add("reportingYear"); 51 orgProfitAvgField.add("reportingMonth"); 52 53 orgBalanceSheetsAvgField.add("reportingYear"); 54 orgBalanceSheetsAvgField.add("reportingMonth"); 55 56 57 58 59 //返回前端的结果集 60 List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>(); 61 //指标结果集 62 Map<String,List<Map<String,Object>>> stringListMap = new HashMap<String,List<Map<String,Object>>>(); 63 //指标Query 64 Query orgBalanceSheetsQuery = null; 65 66 //平均数结果集 67 Map<String,List<Map<String,Object>>> avgListMap = new HashMap<String,List<Map<String,Object>>>(); 68 69 //获取机构id 70 List parameterCompanyIds = parameter.getCompanyIds(); 71 72 List<OrgStatisticalIndicators> orgStatisticalIndicatorsByIds = orgStatisticalIndicatorsRespository.findOrgStatisticalIndicatorsByIds(parameter.getIds()); 73 74 for (OrgStatisticalIndicators orgStatisticalIndicators : orgStatisticalIndicatorsByIds) { 75 String query = ""; 76 String field = orgStatisticalIndicators.getField(); 77 String isQuery = orgStatisticalIndicators.getIsQuery(); 78 String isAvgQuery = orgStatisticalIndicators.getAvgQuery(); 79 String tableName = orgStatisticalIndicators.getTableName(); 80 /** 81 * 1.前端显示后端不查询 82 * 0.前端显示后端也查询 83 */ 84 if("1".equals(isQuery)){ 85 continue; 86 } 87 88 /** 89 * year.按照年份查询 90 * month.按照月查询 91 */ 92 if("year".equals(parameter.getTimeStyle())){ 93 query = orgStatisticalIndicators.getYearQuery(); 94 }else{ 95 query = orgStatisticalIndicators.getMonthQuery(); 96 } 97 //如果结果为空跳过本次循环 98 if(query == null || "".equals(query)){ 99 continue; 100 } 101 102 /** 103 * 判断指标所在的表，然后为各自的表拼接上表的字段 104 */ 105 if ("org_information_cbrc".equals(tableName)) { 106 107 if(parameterCompanyIds.size()>0){ 108 orgInformationCbrc.append(query+" ,"); 109 110 //如果其他表不存在这个属性则为其他表拼接null 111 orgBasicInformation.append("null as "+field+","); 112 orgBalanceSheets.append("null as "+field+","); 113 orgBusinessStructure.append("null as "+field+","); 114 orgProfit.append("null as "+field+","); 115 } 116 117 //行业平均 118 if (parameter.isAvg() ==true && !"1".equals(isAvgQuery)) { 119 orgInformationCbrcAvg.append(" avg("+query+"),"); 120 orgInformationCbrcAvgField.add(field); 121 122 } 123 } else if ("org_basic_information".equals(tableName)) { 124 if(parameterCompanyIds.size()>0){ 125 orgBasicInformation.append(query+" ,"); 126 127 orgInformationCbrc.append("null as "+field+","); 128 orgBalanceSheets.append("null as "+field+","); 129 orgBusinessStructure.append("null as "+field+","); 130 orgProfit.append("null as "+field+","); 131 } 132 133 //行业平均 134 if (parameter.isAvg() && !"1".equals(isAvgQuery)) { 135 orgBasicInformationAvg.append("avg("+query+"),"); 136 orgBasicInformationAvgField.add(field); 137 138 } 139 140 } else if ("org_business_structure".equals(tableName)) { 141 if(parameterCompanyIds.size()>0){ 142 orgBusinessStructure.append(query+" ,"); 143 144 orgBasicInformation.append("null as "+field+","); 145 orgInformationCbrc.append("null as "+field+","); 146 orgBalanceSheets.append("null as "+field+","); 147 orgProfit.append("null as "+field+","); 148 } 149 150 //行业平均 151 if (parameter.isAvg() && !"1".equals(isAvgQuery)) { 152 orgBusinessStructureAvg.append("avg("+query+"),"); 153 orgBusinessStructureAvgField.add(field); 154 } 155 } else if ("org_profit".equals(tableName)) { 156 157 if(parameterCompanyIds.size()>0){ 158 orgProfit.append(query+" AS "+field+" ,"); 159 orgBasicInformation.append("null as "+field+","); 160 orgInformationCbrc.append("null as "+field+","); 161 orgBalanceSheets.append("null as "+field+","); 162 orgBusinessStructure.append("null as "+field+","); 163 } 164 165 //行业平均 166 if (parameter.isAvg() && !"1".equals(isAvgQuery)) { 167 orgProfitAvg.append("avg("+query+"),"); 168 orgProfitAvgField.add(field); 169 } 170 } else if ("org_balance_sheets".equals(tableName)) { 171 if(parameterCompanyIds.size()>0){ 172 orgBalanceSheets.append(query+" ,"); 173 174 orgBasicInformation.append("null as "+field+","); 175 orgInformationCbrc.append("null as "+field+","); 176 orgBusinessStructure.append("null as "+field+","); 177 orgProfit.append("null as "+field+","); 178 } 179 180 //行业平均 181 if (parameter.isAvg() == true && !"1".equals(isAvgQuery)) { 182 183 orgBalanceSheetsAvg.append("avg("+query+"),"); 184 orgBalanceSheetsAvgField.add(field); 185 186 } 187 }else if("org_basic_info_list".equals(tableName)){//因为这几个字段关联的是其他表所以使用子查询 188 189 if(orgStatisticalIndicators.getName().startsWith("银行")){ 190 if(parameterCompanyIds.size()>0){ 191 orgBasicInformation.append("(SELECT "+query+" FROM "+tableName+" where org_basic_id = ID and type ='1' limit 1) ,"); 192 193 orgInformationCbrc.append("null as "+field+","); 194 orgBalanceSheets.append("null as "+field+","); 195 orgBusinessStructure.append("null as "+field+","); 196 orgProfit.append("null as "+field+","); 197 } 198 199 if(parameter.isAvg() == true && !"1".equals(isAvgQuery)){ 200 orgBasicInformationAvg.append("(SELECT avg("+query+") FROM "+tableName+" where org_basic_id = ID and type ='1' limit 1) ,"); 201 } 202 203 }else if(orgStatisticalIndicators.getName().startsWith("资本")){ 204 if(parameterCompanyIds.size()>0){ 205 orgBasicInformation.append("(SELECT "+query+" FROM "+tableName+" where org_basic_id = ID and type ='0'limit 1) ,"); 206 207 orgInformationCbrc.append("null as "+field+"1,"); 208 orgBalanceSheets.append("null as "+field+"1,"); 209 orgBusinessStructure.append("null as "+field+"1,"); 210 orgProfit.append("null as "+field+"1,"); 211 } 212 if(parameter.isAvg() == true && !"1".equals(isAvgQuery)){ 213 orgBasicInformationAvg.append("(SELECT avg("+query+") FROM "+tableName+" where org_basic_id = ID and type ='0' limit 1) ,"); 214 } 215 } 216 217 if (parameter.isAvg()==true && !"1".equals(isAvgQuery)) { 218 orgBasicInformationAvgField.add(field); 219 } 220 221 } 222 if (!"1".equals(isQuery)) { 223 if(parameterCompanyIds.size()>0){ 224 fieldNames.add(field); 225 } 226 } 227 228 } 229 230 //拼接where条件 231 StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1"); 232 233 234 if("year".equals(parameter.getTimeStyle())){ 235 whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' "); 236 }else{ 237 whereSql.append(" and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) )>= :startYear and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) ) <= :endYear"); 238 } 239 240 241 //如果机构id不为空 242 if (parameterCompanyIds.size()>0) { 243 whereSql.append(" AND company_id in ( "); 244 245 246 for (int i = 0; i < parameterCompanyIds.size(); i++) { 247 whereSql.append(":s"+i+" ,"); 248 } 249 250 whereSql.deleteCharAt(whereSql.length()-1); 251 whereSql.append(" )"); 252 } 253 254 255 //拼接五张表和条件 256 orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1); 257 orgBalanceSheets.append(" from org_balance_sheets "); 258 orgBalanceSheets.append(whereSql); 259 260 orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1); 261 orgBasicInformation.append(" from org_basic_information "); 262 orgBasicInformation.append(whereSql); 263 264 orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1); 265 orgBusinessStructure.append(" from org_business_structure "); 266 orgBusinessStructure.append(whereSql); 267 268 orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1); 269 orgInformationCbrc.append(" from org_information_cbrc "); 270 orgInformationCbrc.append(whereSql); 271 272 273 orgProfit.deleteCharAt(orgProfit.length()-1); 274 orgProfit.append(" from org_profit "); 275 orgProfit.append(whereSql); 276 277 278 //关联五张表 279 orgBalanceSheets.append(" UNION "); 280 orgBalanceSheets.append(orgBasicInformation.toString()); 281 282 orgBalanceSheets.append(" UNION "); 283 orgBalanceSheets.append(orgBusinessStructure.toString()); 284 285 orgBalanceSheets.append(" UNION "); 286 orgBalanceSheets.append(orgInformationCbrc.toString()); 287 288 orgBalanceSheets.append(" UNION "); 289 orgBalanceSheets.append(orgProfit.toString()); 290 291 //如果有选机构 292 if(parameterCompanyIds.size() > 0){ 293 //创建本地sql查询实例 294 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString()); 295 296 //如果时间为空那就获取现在的时间 297 if(parameter.getEndTime() == null){ 298 parameter.setEndTime(new Date()); 299 } 300 if(parameter.getStartTime() == null){ 301 parameter.setStartTime(new Date()); 302 } 303 304 if("year".equals(parameter.getTimeStyle())){ 305 306 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy")); 307 308 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy")); 309 }else if("month".equals(parameter.getTimeStyle())){ 310 311 312 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM")); 313 314 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM")); 315 316 317 } 318 319 320 if (parameterCompanyIds.size()>0) { 321 322 for (int i = 0; i < parameterCompanyIds.size(); i++) { 323 orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i)); 324 } 325 } 326 327 //返回结果 328 List<Map<String,Object>> result = new ArrayList<>(); 329 330 //获取数据 331 List resultList = orgBalanceSheetsQuery.getResultList(); 332 333 dataEncapsulation(resultList, fieldNames, result); 334 335 // 336 for (Map<String, Object> map : result) { 337 String reportingYear = (String) map.get("reportingYear"); 338 String reportingMonth = (String) map.get("reportingMonth"); 339 String reportingDate = reportingYear+"-"+reportingMonth; 340 //如果时间类型是年 341 if ("year".equals(parameter.getTimeStyle())) { 342 List list = stringListMap.get(reportingYear); 343 if (list != null) { 344 list.add(map); 345 stringListMap.put(reportingYear,list); 346 }else{ 347 List inner =new ArrayList(); 348 inner.add(map); 349 stringListMap.put(reportingYear,inner); 350 } 351 }else{//如果为月 352 353 List list = stringListMap.get(reportingDate); 354 if (list != null) { 355 list.add(map); 356 stringListMap.put(reportingDate,list); 357 }else{ 358 List inner =new ArrayList(); 359 inner.add(map); 360 stringListMap.put(reportingDate,inner); 361 } 362 } 363 364 } 365 366 } 367 368 369 /** 370 * 平均数 371 */ 372 if (parameter.isAvg() == true) { 373 374 //定义Query 375 Query avgQuerey = null; 376 377 String where = ""; 378 /** 379 * 年月查询sql 380 * 1.年查询的是本年的第12月的数据 381 * 2.月查询是查询开始到结束的数据 382 */ 383 if("month".equals(parameter.getTimeStyle())){ 384 where = " where CONCAT( reporting_year,'-',RIGHT (100 + CAST(reporting_month AS SIGNED),2)) >= :startTime AND CONCAT( reporting_year,'-',RIGHT (100 + CAST(reporting_month AS SIGNED),2)) <= :endTime GROUP BY reporting_year,reporting_month "; 385 }else{ 386 where = " where reporting_year >= :startTime AND reporting_year<= :endTime AND reporting_month = '12' GROUP BY reporting_year,reporting_month "; 387 } 388 389 String startTime = ""; 390 String endTime = ""; 391 /** 392 * 年查询和月查询所给时间赋的值是不一样的 393 */ 394 if("year".equals(parameter.getTimeStyle())){ 395 startTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy"); 396 endTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy"); 397 }else{ 398 startTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM"); 399 endTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM"); 400 } 401 402 //如果有这个集合添加的属性超过2个（年月默认添加了） 403 if(orgBalanceSheetsAvgField.size()>2){ 404 orgBalanceSheetsAvg.deleteCharAt(orgBalanceSheetsAvg.length()-1); 405 orgBalanceSheetsAvg.append(" from org_balance_sheets "); 406 orgBalanceSheetsAvg.append(where); 407 avgSetValue(avgQuerey,orgBalanceSheetsAvg,startTime,endTime,orgBalanceSheetsAvgField,parameter.getTimeStyle(),avgListMap); 408 } 409 410 if(orgProfitAvgField.size()>2){ 411 orgProfitAvg.deleteCharAt(orgProfitAvg.length()-1); 412 orgProfitAvg.append(" from org_profit "); 413 orgProfitAvg.append(where); 414 avgSetValue(avgQuerey,orgProfitAvg,startTime,endTime,orgProfitAvgField,parameter.getTimeStyle(),avgListMap); 415 } 416 417 if(orgBasicInformationAvgField.size() > 2){ 418 orgBasicInformationAvg.deleteCharAt(orgBasicInformationAvg.length()-1); 419 orgBasicInformationAvg.append(" from org_basic_information "); 420 orgBasicInformationAvg.append(where); 421 avgSetValue(avgQuerey,orgBasicInformationAvg,startTime,endTime,orgBasicInformationAvgField,parameter.getTimeStyle(),avgListMap); 422 } 423 424 if(orgBusinessStructureAvgField.size() > 2){ 425 orgBusinessStructureAvg.deleteCharAt(orgBusinessStructureAvg.length()-1); 426 orgBusinessStructureAvg.append(" from org_business_structure "); 427 orgBusinessStructureAvg.append(where); 428 avgSetValue(avgQuerey,orgBusinessStructureAvg,startTime,endTime,orgBusinessStructureAvgField,parameter.getTimeStyle(),avgListMap); 429 } 430 431 if(orgInformationCbrcAvgField.size() > 2){ 432 orgInformationCbrcAvg.deleteCharAt(orgInformationCbrcAvg.length()-1); 433 orgInformationCbrcAvg.append(" from org_information_cbrc "); 434 orgInformationCbrcAvg.append(where); 435 avgSetValue(avgQuerey,orgInformationCbrcAvg,startTime,endTime,orgInformationCbrcAvgField,parameter.getTimeStyle(),avgListMap); 436 } 437 438 } 439 /** 440 * 1.如果指标查询的集合为空将平均数的集合赋值给它 441 * 2.如果都有数据就将他们的相同时间段的添加在一起 442 * 3.如果平均数为空就不用操作直接返回指标查询 443 */ 444 445 if(stringListMap.size() == 0){ 446 stringListMap.putAll(avgListMap); 447 }else if(stringListMap.size() > 0 && avgListMap.size() > 0){ 448 449 for (Map.Entry<String, List<Map<String, Object>>> entry : stringListMap.entrySet()) { 450 System.out.println(entry.getValue()+"=="+entry.getKey()); 451 List<Map<String, Object>> maps = avgListMap.get(entry.getKey()); 452 if(maps != null){ 453 List<Map<String, Object>> mapList = stringListMap.get(entry.getKey()); 454 for (Map<String, Object> map : maps) { 455 mapList.add(map); 456 } 457 458 } 459 } 460 461 } 462 463 464 //绑定Vo对象 465 for (Map.Entry<String, List<Map<String, Object>>> entry : stringListMap.entrySet()) { 466 DefinedReportFormVO formVO = new DefinedReportFormVO(); 467 formVO.setTime(entry.getKey()); 468 formVO.setArr(entry.getValue()); 469 definedReportFormVOList.add(formVO); 470 } 471 472 return definedReportFormVOList; 473 } 474 475 476 /** 477 * 平均数的sql执行、参数设置和结果格式化 478 * @param avgQuerey Query对象 479 * @param orgBalanceSheetsAvg sql 480 * @param startTime 开始时间 481 * @param endTime 结束时间 482 * @param orgBalanceSheetsField 属性集合 483 * @param timeStyle 时间类型 484 * @param stringListMap 最终结果集 485 */ 486 public void avgSetValue(Query avgQuerey,StringBuilder orgBalanceSheetsAvg, String startTime,String endTime,List<String> orgBalanceSheetsField,String timeStyle,Map<String,List<Map<String,Object>>> stringListMap) { 487 //创建本地sql查询实例 488 avgQuerey = entityManager.createNativeQuery(orgBalanceSheetsAvg.toString()); 489 490 avgQuerey.setParameter("startTime", startTime); 491 492 avgQuerey.setParameter("endTime", endTime); 493 494 List queryResultList = avgQuerey.getResultList(); 495 496 avgDataEncapsulation(queryResultList, orgBalanceSheetsField,timeStyle,stringListMap); 497 498 } 499 500 /** 501 * 502 * @param resultList 执行sql得到的结果集 503 * @param field 属性集合 504 * @param result 最终得到的数据 505 */ 506 public void dataEncapsulation(List resultList,List field , List<Map<String,Object>> result){ 507 //给数据设置属性 508 for (int i = 0; i < resultList.size(); i++) { 509 Object o = resultList.get(i); 510 Object[] cells = (Object[]) o; 511 Map<String,Object> map = new HashMap<>(); 512 if(cells.length <= 4){ 513 continue; 514 } 515 for (int j = 0; j<cells.length; j++) { 516 if (cells[j] != null && !"".equals(cells[j].toString())) { 517 map.put((String) field.get(j),cells[j]); 518 }else{ 519 setField(resultList,field,map,i,j); 520 } 521 } 522 523 result.add(map); 524 } 525 526 } 527 528 /** 529 * 平均数 530 * @param resultList 执行sql得到的结果集 531 * @param fieldNames 属性集合 532 * @param timeStyle 时间类型 533 * @param stringListMap 最终得到的结果集 534 */ 535 public void avgDataEncapsulation(List resultList,List fieldNames ,String timeStyle,Map<String,List<Map<String,Object>>> stringListMap){ 536 537 //返回结果 538 List<Map<String,Object>> result = new ArrayList<>(); 539 540 //给数据设置属性 541 for (int i = 0; i < resultList.size(); i++) { 542 Object o = resultList.get(i); 543 Object[] cells = (Object[]) o; 544 Map<String,Object> map = new HashMap<>(); 545 546 for (int j = 0; j<cells.length; j++) { 547 map.put((String) fieldNames.get(j),cells[j]); 548 } 549 //与前端协议好平均数的机构id赋值上avg 550 map.put("companyId","avg"); 551 result.add(map); 552 } 553 554 for (Map<String, Object> map : result) { 555 //获取年月的数据 556 String reportingYear = (String) map.get("reportingYear"); 557 String reportingMonth = (String) map.get("reportingMonth"); 558 String reportingDate = reportingYear+"-"+reportingMonth; 559 560 //如果时间类型是年 561 if ("year".equals(timeStyle)) { 562 List<Map<String, Object>> list = stringListMap.get(reportingYear); 563 if (list != null) { 564 /** 565 * 将相同月份的属性合并在一起 566 */ 567 Map<String, Object> objectMap = list.get(0); 568 objectMap.putAll(map); 569 stringListMap.put(reportingYear,list); 570 }else{ 571 List<Map<String,Object>> inner =new ArrayList(); 572 Map<String,Object> field = new HashMap(); 573 for (Map.Entry<String, Object> entry : map.entrySet()) { 574 field.put(entry.getKey(),entry.getValue()); 575 } 576 inner.add(field); 577 stringListMap.put(reportingYear,inner); 578 } 579 }else{//如果为月 580 //查看集合中是否存在该时间段 581 List<Map<String, Object>> list = stringListMap.get(reportingDate); 582 /** 583 * 如果存在，将原本的添加上现在的，不存在就新增一个list添加进去 584 */ 585 if (list != null) { 586 Map<String, Object> objectMap = list.get(0); 587 objectMap.putAll(map); 588 stringListMap.put(reportingDate ,list); 589 }else{ 590 List<Map<String,Object>> inner =new ArrayList(); 591 Map<String,Object> field = new HashMap(); 592 //循环将所有的属性添加进集合 593 for (Map.Entry<String, Object> entry : map.entrySet()) { 594 field.put(entry.getKey(),entry.getValue()); 595 } 596 inner.add(field); 597 //设置一个新的时间段并添加上该数据 598 stringListMap.put(reportingDate,inner); 599 } 600 } 601 602 } 603 } 604 605 606 607 608 /** 609 * 设置属性 610 * @param resultList 结果集合 611 * @param fieldNames 属性集合 612 * @param map map封装 613 * @param num 当前第几个 614 * @param index 下标 615 */ 616 public void setField(List resultList,List fieldNames,Map map,int num,int index){ 617 618 int i = num; 619 i++; 620 if(i>=resultList.size()){ 621 return; 622 } 623 624 Object o = resultList.get(i); 625 Object[] cells = (Object[]) o; 626 //判断当前这个位置是否存在值， 627 if (cells[index] != null && !"".equals(cells[index].toString())) { 628 if(i==resultList.size()){ 629 map.put((String) fieldNames.get(index),null); 630 } 631 map.put((String) fieldNames.get(index),cells[index].toString()); 632 }else{ 633 setField(resultList,fieldNames,map,i,index); 634 } 635 636 }
我这里是把平均查询和普通的指标的查询区分开了，对应的我对这些数据的操作就增多了。
我这里有用到机构，做的时候不用影响也不会很大，
以前发生一个请求要很久，现在比之前快了许多，所以有关的一些统计的查询和子查询还是要分开的好一点，不然会执行会很慢。可以分开查询，或者后端进行统计之类的。
