Java如何使用Query动态拼接SQL详解

public class DefinedReportFormDTO { /** * 指标id */ private List ids; /** * 开始时间 */ @DateTimeFormat(pattern = "yyyy-MM") private Date startTime; /** * 结束时间 */ @DateTimeFormat(pattern = "yyyy-MM") private Date endTime; /** * 频率 */ private String timeStyle; private boolean avg =false; private String idsParam; private String companyIdsParam; public void setCompanyIdsParam(String companyIdsParam) { this.companyIdsParam = companyIdsParam; } public void setIdsParam(String idsParam) { this.idsParam = idsParam; } public String getCompanyIdsParam() { return companyIdsParam; } public String getIdsParam() { return idsParam; } public boolean isAvg() { return avg; } public void setAvg(boolean avg) { this.avg = avg; } public Date getStartTime() { return startTime; } public void setStartTime(Date startTime) { this.startTime = startTime; } public Date getEndTime() { return endTime; } public void setEndTime(Date endTime) { this.endTime = endTime; } public String getTimeStyle() { return timeStyle; } public void setTimeStyle(String timeStyle) { this.timeStyle = timeStyle; } public List getIds() { return ids; } public void setIds(List ids) { this.ids = ids; } }

public class DefinedReportFormVO implements Serializable { private String time; private List<Map<String, Object>> arr = new ArrayList<>(); public String getTime() { return time; } public void setTime(String time) { this.time = time; } public List<Map<String, Object>> getArr() { return arr; } public void setArr(List<Map<String, Object>> arr) { this.arr = arr; } }

@GetMapping("/report/defindReport") public JsonResponseExt defindReport(DefinedReportFormDTO definedReportFormDTO){ //测试数据 List list1 = new ArrayList<>(); list1.add("111"); definedReportFormDTO.setIds(list1); definedReportFormDTO.setTimeStyle("month"); definedReportFormDTO.setAvg(true); Calendar instance = Calendar.getInstance(); instance.set(2018,1,11); definedReportFormDTO.setStartTime(instance.getTime()); instance.setTime(new Date()); definedReportFormDTO.setEndTime(instance.getTime()); return JsonResponseExt.success(dataAcquisitionFileInfoService.defindQuery(definedReportFormDTO)); }

@SuppressWarnings("unchecked") @Override public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) { /** * 定义五张表的查询字符串，年月，和机构id默认查询 */ StringBuilder orgInformationCbrc = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id ,"); StringBuilder orgBasicInformation = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); StringBuilder orgBusinessStructure = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); StringBuilder orgProfit = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); StringBuilder orgBalanceSheets = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,"); //定义机构的字符串 StringBuilder companyIds = new StringBuilder(""); //查询所有机构 List<Company> orgList = orgService.getOrgList(); //拼接所有机构的字符串（如果需要求平均数的话） for (Company company : orgList) { companyIds.append(company.getId()+","); } companyIds.deleteCharAt(companyIds.length()-1); //定义每个表的字符串判断 Map<String ,String> bool = new HashMap<>(); //指标名 List<String> fieldNames = new ArrayList(); //返回结果 List<Map<String,Object>> result = new ArrayList<>(); //指标名默认添加年月机构id fieldNames.add("reportingYear"); fieldNames.add("reportingMonth"); fieldNames.add("companyId"); //定义指标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("ifnull("+orgStatisticalIndicators.getTableField()+",0) 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()) { if("year".equals(parameter.getTimeStyle())){ orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); }else{ orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) 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("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 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()) { if("year".equals(parameter.getTimeStyle())){ orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); }else{ orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } } else if ("org_business_structure".equals(orgStatisticalIndicators.getTableName())) { orgBusinessStructure.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 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()) { if("year".equals(parameter.getTimeStyle())){ orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); }else{ orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } } else if ("org_profit".equals(orgStatisticalIndicators.getTableName())) { orgProfit.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,"); 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()) { if("year".equals(parameter.getTimeStyle())){ orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); }else{ orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } } else if ("org_balance_sheets".equals(orgStatisticalIndicators.getTableName())) { orgBalanceSheets.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) 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()) { if("year".equals(parameter.getTimeStyle())){ orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,"); }else{ orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); } orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,"); orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) 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"); } //获取所有机构id List parameterCompanyIds = parameter.getCompanyIds(); //如果机构id不为空 if (parameterCompanyIds.size()>0) { whereSql.append(" AND company_id in ( "); for (int i = 0; i < parameterCompanyIds.size(); i++) { whereSql.append(":s"+i+" ,"); } whereSql.deleteCharAt(whereSql.length()-1); whereSql.append(" )"); } //定义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")); } if (parameterCompanyIds.size()>0) { for (int i = 0; i < parameterCompanyIds.size(); i++) { orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i)); } } //获取数据 List resultList = orgBalanceSheetsQuery.getResultList(); System.out.println("resultList==="+resultList); //给数据设置属性 for (int i = 0; i < resultList.size(); i++) { Object o = resultList.get(i); Object[] cells = (Object[]) o; Map<String,Object> map = new HashMap<>(); if(cells.length == 3){ continue; } 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); } } } System.out.println("stringListMap == "+stringListMap); 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; }

/** * 统计指标 */ @Entity @Table(name = "org_statistical_indicators", catalog = "zhsupervision") public class OrgStatisticalIndicators { @Id @GeneratedValue private Long id; /** * 前端显示名 */ private String name; /** * 表属性 */ private String tableField; /** * 表名称 */ private String tableName; /** * 创建时间 */ private Date createTime; /** * 更新时间 */ private Date updateTime; /** * 删除标识 */ private String delFlag; //父节点 private Long pId; //属性 private String field; //该指标查询月的时候是否查询 private String monthQuery; //该指标查询年的时候是否查询 private String yearQuery; public String getMonthQuery() { return monthQuery; } public void setMonthQuery(String monthQuery) { this.monthQuery = monthQuery; } public String getYearQuery() { return yearQuery; } public void setYearQuery(String yearQuery) { this.yearQuery = yearQuery; } public String getField() { return field; } public void setField(String field) { this.field = field; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Long getpId() { return pId; } public void setpId(Long pId) { this.pId = pId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTableField() { return tableField; } public void setTableField(String tableField) { this.tableField = tableField; } public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getUpdateTime() { return updateTime; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } public String getDelFlag() { return delFlag; } public void setDelFlag(String delFlag) { this.delFlag = delFlag; } }

/** * 统计指标服务类 */ public interface OrgStatisticalIndicatorsService { /** * 根据id获取 * @param id * @return */ OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id); /** * 根据表名查询 */ List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name); }

@Service public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl<OrgStatisticalIndicators, String> implements OrgStatisticalIndicatorsService { @Autowired private OrgStatisticalIndicatorsRespository respository; @Override public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) { return respository.findByIdAndAndDelFlag(id); } @Override public List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name) { return respository.findOrgStatisticalIndicatorsByTableName(name); } }

public interface OrgStatisticalIndicatorsRespository extends JpaSpecificationExecutor { @Query(value = "select * from org_statistical_indicators WHERE ID=?1 and del_flag = '0'",nativeQuery = true) OrgStatisticalIndicators findByIdAndAndDelFlag(Long id); @Query(value = "select * from org_statistical_indicators WHERE del_flag = '0' and NAME =?1",nativeQuery = true) OrgStatisticalIndicators findOrgStatisticalIndicatorsByName(String name); }

相关阅读