Java 使用Query动态拼接SQl
2019-01-10 07:48:43来源：博客园 阅读 ()
之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。
参数接受DTO
1 public class DefinedReportFormDTO { 2 /** 3 * 指标id 4 */ 5 private List ids; 6 /** 7 * 开始时间 8 */ 9 @DateTimeFormat(pattern = "yyyy-MM") 10 private Date startTime; 11 /** 12 * 结束时间 13 */ 14 @DateTimeFormat(pattern = "yyyy-MM") 15 private Date endTime; 16 /** 17 * 频率 18 */ 19 private String timeStyle; 20 21 22 private boolean avg =false; 23 24 private String idsParam; 25 26 private String companyIdsParam; 27 28 public void setCompanyIdsParam(String companyIdsParam) { 29 this.companyIdsParam = companyIdsParam; 30 } 31 32 public void setIdsParam(String idsParam) { 33 this.idsParam = idsParam; 34 } 35 36 public String getCompanyIdsParam() { 37 return companyIdsParam; 38 } 39 40 public String getIdsParam() { 41 return idsParam; 42 } 43 public boolean isAvg() { 44 return avg; 45 } 46 47 public void setAvg(boolean avg) { 48 this.avg = avg; 49 } 50 51 52 public Date getStartTime() { 53 return startTime; 54 } 55 56 public void setStartTime(Date startTime) { 57 this.startTime = startTime; 58 } 59 60 public Date getEndTime() { 61 return endTime; 62 } 63 64 public void setEndTime(Date endTime) { 65 this.endTime = endTime; 66 } 67 68 public String getTimeStyle() { 69 return timeStyle; 70 } 71 72 public void setTimeStyle(String timeStyle) { 73 this.timeStyle = timeStyle; 74 } 75 76 public List getIds() { 77 return ids; 78 } 79 80 public void setIds(List ids) { 81 this.ids = ids; 82 } 83 84 85 86 }
数据返回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多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
上一篇：Java多线程基础（二）
下一篇：多线程系列之 线程安全
相关文章
- 国外程序员整理的Java资源大全（全部是干货） 2020-06-12
- 2020年深圳中国平安各部门Java中级面试真题合集（附答案） 2020-06-11
- 2020年java就业前景 2020-06-11
- 04.Java基础语法 2020-06-11
- Java--反射(框架设计的灵魂)案例 2020-06-11
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
热门词条
最新资讯
热门关注
热门标签