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

前言

之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。

参数接受DTO

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

数据返回VO

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

控制器Controller

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

}

服务类Service

public interface DataAcquisitionFileInfoService {

List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter);

}

实现类ServiceImpl

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

指标Service

/**
* 统计指标服务类
*/
public interface OrgStatisticalIndicatorsService {
/**
* 根据id获取
* @param id
* @return
*/
OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id);

/**
* 根据表名查询
*/
List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name);

}

指标serviceImpl

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

指标repository

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

这个repository要继承 extends JpaRepository<T, ID> 才可以，写漏了。

上面使用了union 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。

来源：https://www.cnblogs.com/xiluonanfeng/p/10245974.html