Java Query 动态自定义查询
之前有做个一个自定义报表的查询，这里使用的是一个动态的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 }
View Code
数据返回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 }
View Code
控制器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 }
View Code
服务类Service
1 public interface DataAcquisitionFileInfoService { 2 3 List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter); 4 5 }
View Code
实现类ServiceImpl
@Service public class DataAcquisitionFileInfoServiceImpl implements DataAcquisitionFileInfoService { @PersistenceContext private EntityManager entityManager; @Autowired private OrgStatisticalIndicatorsRespository orgStatisticalIndicatorsRespository; @SuppressWarnings("unchecked") @Override public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) { /** * 定义五张表的查询字符串，年月，和机构id默认查询 */ StringBuilder orgInformationCbrc = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, id,"); StringBuilder orgBasicInformation = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, id,"); StringBuilder orgBusinessStructure = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, id,"); StringBuilder orgProfit = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, id,"); StringBuilder orgBalanceSheets = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, id,"); //定义每个表的字符串判断 Map<String ,String> bool = new HashMap<>(); //指标名 List<String> fieldNames = new ArrayList(); //返回结果 List<Map<String,Object>> result = new ArrayList<>(); //指标名默认添加年月 fieldNames.add("id"); fieldNames.add("reportingYear"); fieldNames.add("reportingMonth"); //定义指标id集合 List ids = parameter.getIds(); //循环所有的指标 for (Object id : ids) { //如果指标为空 if (!"".equals(id) && id != null) { //根据指标id查询指标 OrgStatisticalIndicators orgStatisticalIndicators = orgStatisticalIndicatorsRespository.findByIdAndAndDelFlag(Long.parseLong(id.toString())); if(("year".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getYearQuery())) || ("month".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getMonthQuery()))){ /** * 判断指标所在的表，然后为各自的表拼接上表的字段 */ if ("org_information_cbrc".equals(orgStatisticalIndicators.getTableName())) { orgInformationCbrc.append(orgStatisticalIndicators.getTableField() + " as " + orgStatisticalIndicators.getField() + ","); // if (bool.get("org_information_cbrc") == null) { bool.put("org_information_cbrc", orgStatisticalIndicators.getTableField()); } //如果其他表不存在这个属性则为其他表拼接null orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); //行业平均 if (parameter.isAvg()) { orgInformationCbrc.append(" (select avg(" + orgStatisticalIndicators.getTableField() + ") " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBalanceSheets.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBasicInformation.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBusinessStructure.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgProfit.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); //如果时间类型为年 if ("year".equals(parameter.getTimeStyle())) { orgInformationCbrc.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBasicInformation.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); } else {//否则为月 orgInformationCbrc.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBasicInformation.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); } } } else if ("org_basic_information".equals(orgStatisticalIndicators.getTableName())) { if (bool.get("org_basic_information") == null) { bool.put("org_basic_information", orgStatisticalIndicators.getTableField()); } orgBasicInformation.append(orgStatisticalIndicators.getTableField() + ","); orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); //行业平均 if (parameter.isAvg()) { orgBasicInformation.append(" (select avg(" + orgStatisticalIndicators.getTableField() + ") " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgProfit.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgInformationCbrc.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBalanceSheets.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBusinessStructure.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); if ("year".equals(parameter.getTimeStyle())) { orgBasicInformation.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); } else { orgBasicInformation.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); } } } else if ("org_business_structure".equals(orgStatisticalIndicators.getTableName())) { orgBusinessStructure.append(orgStatisticalIndicators.getTableField() + ","); if (bool.get("org_business_structure") == null) { bool.put("org_business_structure", orgStatisticalIndicators.getTableField()); } orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); //行业平均 if (parameter.isAvg()) { orgBasicInformation.append(" (select avg(" + orgStatisticalIndicators.getTableField() + ") " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgProfit.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgInformationCbrc.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBalanceSheets.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBusinessStructure.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); if ("year".equals(parameter.getTimeStyle())) { orgBasicInformation.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12')" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); } else { orgBasicInformation.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); } } } else if ("org_profit".equals(orgStatisticalIndicators.getTableName())) { orgProfit.append(orgStatisticalIndicators.getTableField() + ","); if (bool.get("org_profit") == null) { bool.put("org_profit", orgStatisticalIndicators.getTableField()); } orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ","); orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); //行业平均 if (parameter.isAvg()) { orgBasicInformation.append(" (select avg(" + orgStatisticalIndicators.getTableField() + ") " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgProfit.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgInformationCbrc.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBalanceSheets.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBusinessStructure.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); if ("year".equals(parameter.getTimeStyle())) { orgBasicInformation.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); } else { orgBasicInformation.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); } } } else if ("org_balance_sheets".equals(orgStatisticalIndicators.getTableName())) { orgBalanceSheets.append(orgStatisticalIndicators.getTableField() + " as " + orgStatisticalIndicators.getField() + ","); if (bool.get("org_balance_sheets") == null) { bool.put("org_balance_sheets", orgStatisticalIndicators.getTableField()); } orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ","); orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ","); orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ","); orgProfit.append("null as " + orgStatisticalIndicators.getField() + ","); //行业平均 if (parameter.isAvg()) { orgBasicInformation.append(" (select avg(" + orgStatisticalIndicators.getTableField() + ") " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgProfit.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgInformationCbrc.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBalanceSheets.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); orgBusinessStructure.append(" (select avg(null) " + "as " + orgStatisticalIndicators.getField() + "Avg from " + "" + orgStatisticalIndicators.getTableName() + "" + " where 1=1"); if ("year".equals(parameter.getTimeStyle())) { orgBasicInformation.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' AND )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' )" + "as " + orgStatisticalIndicators.getField() + "Avg,"); } else { orgBasicInformation.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgProfit.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgInformationCbrc.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBalanceSheets.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); orgBusinessStructure.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 ) " + "as " + orgStatisticalIndicators.getField() + "Avg,"); } } } if (parameter.isAvg()==true) { fieldNames.add(orgStatisticalIndicators.getField()); fieldNames.add(orgStatisticalIndicators.getField()+"Avg"); } else { fieldNames.add(orgStatisticalIndicators.getField()); } } } } //拼接where条件 StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1"); if("year".equals(parameter.getTimeStyle())){ whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' "); }else{ 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"); } //定义Query Query orgBalanceSheetsQuery = null; //拼接五张表和条件 orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1); orgBalanceSheets.append(" from org_balance_sheets "); orgBalanceSheets.append(whereSql); orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1); orgBasicInformation.append(" from org_basic_information "); orgBasicInformation.append(whereSql); orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1); orgBusinessStructure.append(" from org_business_structure "); orgBusinessStructure.append(whereSql); orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1); orgInformationCbrc.append(" from org_information_cbrc "); orgInformationCbrc.append(whereSql); orgProfit.deleteCharAt(orgProfit.length()-1); orgProfit.append(" from org_profit "); orgProfit.append(whereSql); //关联五张表 orgBalanceSheets.append(" UNION "); orgBalanceSheets.append(orgBasicInformation.toString()); orgBalanceSheets.append(" UNION "); orgBalanceSheets.append(orgBusinessStructure.toString()); orgBalanceSheets.append(" UNION "); orgBalanceSheets.append(orgInformationCbrc.toString()); orgBalanceSheets.append(" UNION "); orgBalanceSheets.append(orgProfit.toString()); System.out.println(">>"+orgBalanceSheets.toString()); //创建本地sql查询实例 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString()); //如果时间为空那就获取现在的时间 if(parameter.getEndTime() == null){ parameter.setEndTime(new Date()); } if(parameter.getStartTime() == null){ parameter.setStartTime(new Date()); } if("year".equals(parameter.getTimeStyle())){ orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy")); orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy")); }else if("month".equals(parameter.getTimeStyle())){ orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM")); orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM")); } //获取数据 List resultList = orgBalanceSheetsQuery.getResultList(); //给数据设置属性 for (int i = 0; i < resultList.size(); i++) { Object o = resultList.get(i); Object[] cells = (Object[]) o; Map<String,Object> map = new HashMap<>(); for (int j = 0; j<cells.length; j++) { if (cells[j] != null && !"".equals(cells[j].toString())) { map.put((String) fieldNames.get(j),cells[j]); }else{ setField(resultList,fieldNames,map,i,j); } } result.add(map); } System.out.println("result == "+result); List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>(); Map<String,List> stringListMap = new HashMap<>(); //相同时间的放在一起 for (Map<String, Object> map : result) { String reportingYear = (String) map.get("reportingYear"); String reportingMonth = (String) map.get("reportingMonth"); String reportingDate = reportingYear+"-"+reportingMonth; //如果时间类型是年 if ("year".equals(parameter.getTimeStyle())) { List list = stringListMap.get(reportingYear); if (list != null) { list.add(map); stringListMap.put(reportingYear,list); }else{ List inner =new ArrayList(); inner.add(map); stringListMap.put(reportingYear,inner); } }else{//如果为月 List list = stringListMap.get(reportingDate); if (list != null) { list.add(map); stringListMap.put(reportingDate,list); }else{ List inner =new ArrayList(); inner.add(map); stringListMap.put(reportingDate,inner); } } } //定义返回的格式 for (Map.Entry<String,List> entry : stringListMap.entrySet()) { DefinedReportFormVO formVO = new DefinedReportFormVO(); formVO.setTime(entry.getKey()); if(parameter.isAvg()==true){ formVO.setArr(setAvg(entry.getValue(),fieldNames)); }else{ formVO.setArr(entry.getValue()); } definedReportFormVOList.add(formVO); } return definedReportFormVOList; } //如果当前值没有要下一个对象的同一个位置的值 public void setField(List resultList,List fieldNames,Map map,int num,int index){ int i = num; i++; if(i>=resultList.size()){ return; } Object o = resultList.get(i); Object[] cells = (Object[]) o; if (cells[index] != null && !"".equals(cells[index].toString())) { map.put((String) fieldNames.get(index),cells[index].toString()); }else{ setField(resultList,fieldNames,map,i,index); } } //设置平均值的返回 public List setAvg(List list,List fieldNames){ List avgList = new ArrayList(); for (int i = 0; i < list.size(); i++) { Map<String, Object> map = (Map<String, Object>) list.get(i); Map<String,Object> avg = new HashMap<>(); Map<String,Object> common = new HashMap<>(); for (int k = 0; k < fieldNames.size(); k++) { if(map.get(fieldNames.get(k)) == null){ continue; } if(fieldNames.get(k).toString().endsWith("Avg")){ avg.put(fieldNames.get(k).toString().substring(0,fieldNames.get(k).toString().length()-3), map.get(fieldNames.get(k))); }else{ common.put(fieldNames.get(k).toString(),map.get(fieldNames.get(k))); } } avg.put("id","avg"); avg.put("reportingYear",map.get("reportingYear")); avg.put("reportingMonth",map.get("reportingMonth")); avgList.add(avg); avgList.add(common); } return avgList; } }
指标实体
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 }
View Code
指标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 }
View Code
指标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 }
View Code
指标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 }
View Code
这个repository要继承 extends JpaRepository<T, ID> 才可以，写漏了。
上面使用了union 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
版权声明：本文为xiluonanfeng原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。