欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-01 21:03:34
前言 之前有做个一个自定义报表的查询,这里使用的是一个动态的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多个,无法确定查询的返回实体,所以只能自己根据数据的返回给数据绑定属性。

总结

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