欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

Java如何使用Query動態(tài)拼接SQL詳解

前言

成都創(chuàng)新互聯(lián)專注于和田企業(yè)網站建設,響應式網站設計,成都商城網站開發(fā)。和田網站建設公司,為和田等地區(qū)提供建站服務。全流程按需求定制設計，專業(yè)設計，全程項目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務

之前有做個一個自定義報表的查詢，這里使用的是一個動態(tài)的sql拼接，是前端選擇了什么指標就查詢什么信息?。ㄟ@里的指標是多個表的字段，前端隨便選擇了這些指標，然后后端根據(jù)這些指標拼接sql，返回這些指標的數(shù)據(jù)）。

參數(shù)接受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;
 }
}

數(shù)據(jù)返回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){

 

 
 //測試數(shù)據(jù) 
 

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

實現(xiàn)類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();

 //拼接所有機構的字符串（如果需要求平均數(shù)的話）
 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();
 //循環(huán)所有的指標
 for (Object id : ids) {
  //如果指標為空
  if (!"".equals(id) && id != null) {
  //根據(jù)指標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() + ",");

   //行業(yè)平均
   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() + ",");

   //行業(yè)平均
   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() + ",");

   //行業(yè)平均
   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() + ",");

   //行業(yè)平均
   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() + ",");

   //行業(yè)平均
   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);


 //關聯(lián)五張表
 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());


 //創(chuàng)建本地sql查詢實例
 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString());

 //如果時間為空那就獲取現(xiàn)在的時間
 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));
  }
 }


 //獲取數(shù)據(jù)
 List resultList = orgBalanceSheetsQuery.getResultList();


 System.out.println("resultList==="+resultList);

 //給數(shù)據(jù)設置屬性
 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;
 }

指標實體

/**
 * 統(tǒng)計指標
 */
@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;
 /**
 * 創(chuàng)建時間
 */
 private Date createTime;
 /**
 * 更新時間
 */
 private Date updateTime;
 /**
 * 刪除標識
 */
 private String delFlag;
 //父節(jié)點
 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

/**
 * 統(tǒng)計指標服務類
 */
public interface OrgStatisticalIndicatorsService {
 /**
 * 根據(jù)id獲取
 * @param id
 * @return
 */
 OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id);

 /**
 * 根據(jù)表名查詢
 */
 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 進行表之間的關聯(lián)查詢，關聯(lián)的表有點多，所以代碼有些長，同時因為表多，指標（表的屬性）有500多個，無法確定查詢的返回實體，所以只能自己根據(jù)數(shù)據(jù)的返回給數(shù)據(jù)綁定屬性。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對創(chuàng)新互聯(lián)的支持。

分享題目：Java如何使用Query動態(tài)拼接SQL詳解
URL地址：http://aaarwkj.com/article6/peicog.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供移動網站建設、做網站、自適應網站、Google、商城網站、面包屑導航

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

網站托管運營
十八禁一区二区在线观看| 亚洲一区二区三区有码| 福利福利视频一区二区| 久草福利资源在线观看视频| 亚洲综合偷拍日韩av| 日本一区二区三区免费黄视频 | 国产日韩视频一区二区| 日韩欧美亚洲制服丝袜| av在线亚洲网站区一| 午夜精品国产日韩欧美在线| 蜜臀av网站在线播放| 国产成人三级视频网站| 国产精品一区二区综合亚洲| 亚洲永久免费在线观看| 日韩精品一二区电影| 国产女主播在线观看免费观看| 精品国产综合一区二区三区| 中文字幕日韩乱码一级在线| 欧美一级特黄大片免色| 国产在线一区二区三区不卡| 亚洲欧美一区二区国产| 97视频精品全部免费观看| 综合国产精品久久久久久| 欧美女人又粗又长亚洲| 欧美性极品少妇精品网站| 熟女人妻精品一二三四| 日韩欧美人妻一二三四区| 不卡视频在线免费观看| 开裆丝袜高跟啪啪高潮av| 亚洲香蕉av在线一区二区三区 | 日本一区二区三区视频| 亚洲熟妇精品一区二区三区| 色男人天堂亚洲男人天堂| 少妇精品久久久一区二区三区 | 十八禁网站免费在线播放| 欧美国产一级二级三级| 欧美日韩一区二区黄色| 色哟哟在线观看国产精品| 日韩高清在线不卡视频| 亚洲新大香蕉视频在线播放| 成人18禁视频免费看|