浅沫记忆 发表于 2021-10-5 21:47:33

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

这篇文章主要给大家介绍了关于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 != null && !"".equals(cells.tostring())) {
   map.put((string) fieldnames.get(j),cells);
}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多个,无法确定查询的返回实体,所以只能自己根据数据的返回给数据绑定属性。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对CodeAE代码之家的支持。
原文链接:https://www.cnblogs.com/xiluonanfeng/p/10245974.html

http://www.zzvips.com/article/175104.html
页: [1]
查看完整版本: Java如何使用Query动态拼接SQL详解