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多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。