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)); }

public interface DataAcquisitionFileInfoService {  List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter);  }

@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);}

@Servicepublic 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);}