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

2023-08-31 20:52:05 140

前言

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

参数接受DTO

publicclassDefinedReportFormDTO{
/**
*指标id
*/
privateListids;
/**
*开始时间
*/
@DateTimeFormat(pattern="yyyy-MM")
privateDatestartTime;
/**
*结束时间
*/
@DateTimeFormat(pattern="yyyy-MM")
privateDateendTime;
/**
*频率
*/
privateStringtimeStyle;


privatebooleanavg=false;

privateStringidsParam;

privateStringcompanyIdsParam;

publicvoidsetCompanyIdsParam(StringcompanyIdsParam){
this.companyIdsParam=companyIdsParam;
}

publicvoidsetIdsParam(StringidsParam){
this.idsParam=idsParam;
}

publicStringgetCompanyIdsParam(){
returncompanyIdsParam;
}

publicStringgetIdsParam(){
returnidsParam;
}
publicbooleanisAvg(){
returnavg;
}

publicvoidsetAvg(booleanavg){
this.avg=avg;
}


publicDategetStartTime(){
returnstartTime;
}

publicvoidsetStartTime(DatestartTime){
this.startTime=startTime;
}

publicDategetEndTime(){
returnendTime;
}

publicvoidsetEndTime(DateendTime){
this.endTime=endTime;
}

publicStringgetTimeStyle(){
returntimeStyle;
}

publicvoidsetTimeStyle(StringtimeStyle){
this.timeStyle=timeStyle;
}

publicListgetIds(){
returnids;
}

publicvoidsetIds(Listids){
this.ids=ids;
}
}

数据返回VO

publicclassDefinedReportFormVOimplementsSerializable{
privateStringtime;
privateList>arr=newArrayList<>();

publicStringgetTime(){
returntime;
}

publicvoidsetTime(Stringtime){
this.time=time;
}

publicList>getArr(){
returnarr;
}

publicvoidsetArr(List>arr){
this.arr=arr;
}
}

控制器Controller

@GetMapping("/report/defindReport")
publicJsonResponseExtdefindReport(DefinedReportFormDTOdefinedReportFormDTO){




//测试数据


Listlist1=newArrayList<>();
list1.add("111");
definedReportFormDTO.setIds(list1);
definedReportFormDTO.setTimeStyle("month");
definedReportFormDTO.setAvg(true);

Calendarinstance=Calendar.getInstance();
instance.set(2018,1,11);
definedReportFormDTO.setStartTime(instance.getTime());
instance.setTime(newDate());
definedReportFormDTO.setEndTime(instance.getTime());

returnJsonResponseExt.success(dataAcquisitionFileInfoService.defindQuery(definedReportFormDTO));

}

服务类Service

publicinterfaceDataAcquisitionFileInfoService{

ListdefindQuery(DefinedReportFormDTOparameter);

}

实现类ServiceImpl

@SuppressWarnings("unchecked")
@Override
publicListdefindQuery(DefinedReportFormDTOparameter){


/**


*定义五张表的查询字符串，年月，和机构id默认查询
*/
StringBuilderorgInformationCbrc=newStringBuilder("selectreporting_yearasreportingYear,reporting_monthasreportingMonth,company_id,");
StringBuilderorgBasicInformation=newStringBuilder("selectreporting_yearasreportingYear,reporting_monthasreportingMonth,company_id,");
StringBuilderorgBusinessStructure=newStringBuilder("selectreporting_yearasreportingYear,reporting_monthasreportingMonth,company_id,");
StringBuilderorgProfit=newStringBuilder("selectreporting_yearasreportingYear,reporting_monthasreportingMonth,company_id,");
StringBuilderorgBalanceSheets=newStringBuilder("selectreporting_yearasreportingYear,reporting_monthasreportingMonth,company_id,");

//定义机构的字符串
StringBuildercompanyIds=newStringBuilder("");
//查询所有机构
ListorgList=orgService.getOrgList();

//拼接所有机构的字符串（如果需要求平均数的话）
for(Companycompany:orgList){
companyIds.append(company.getId()+",");
}

companyIds.deleteCharAt(companyIds.length()-1);
//定义每个表的字符串判断
Mapbool=newHashMap<>();

//指标名
ListfieldNames=newArrayList();
//返回结果
List>result=newArrayList<>();

//指标名默认添加年月机构id
fieldNames.add("reportingYear");
fieldNames.add("reportingMonth");
fieldNames.add("companyId");
//定义指标id集合
Listids=parameter.getIds();
//循环所有的指标
for(Objectid:ids){
//如果指标为空
if(!"".equals(id)&&id!=null){
//根据指标id查询指标
OrgStatisticalIndicatorsorgStatisticalIndicators=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("nullas"+orgStatisticalIndicators.getField()+",");
orgBalanceSheets.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBusinessStructure.append("nullas"+orgStatisticalIndicators.getField()+",");
orgProfit.append("nullas"+orgStatisticalIndicators.getField()+",");

//行业平均
if(parameter.isAvg()){
if("year".equals(parameter.getTimeStyle())){
orgInformationCbrc.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearANDreporting_month='12')AS"+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgInformationCbrc.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
}


orgBalanceSheets.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");

orgBasicInformation.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");

orgBusinessStructure.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");

orgProfit.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");




}


}elseif("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("nullas"+orgStatisticalIndicators.getField()+",");
orgBalanceSheets.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBusinessStructure.append("nullas"+orgStatisticalIndicators.getField()+",");
orgProfit.append("nullas"+orgStatisticalIndicators.getField()+",");

//行业平均
if(parameter.isAvg()){
if("year".equals(parameter.getTimeStyle())){
orgBasicInformation.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearANDreporting_month='12')AS"+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBasicInformation.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
}

orgProfit.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");

}

}elseif("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("nullas"+orgStatisticalIndicators.getField()+",");
orgInformationCbrc.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBalanceSheets.append("nullas"+orgStatisticalIndicators.getField()+",");
orgProfit.append("nullas"+orgStatisticalIndicators.getField()+",");

//行业平均
if(parameter.isAvg()){
if("year".equals(parameter.getTimeStyle())){
orgBusinessStructure.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearANDreporting_month='12')AS"+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBusinessStructure.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
}

orgProfit.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBasicInformation.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");





}
}elseif("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("nullas"+orgStatisticalIndicators.getField()+",");
orgInformationCbrc.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBalanceSheets.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBusinessStructure.append("nullas"+orgStatisticalIndicators.getField()+",");

//行业平均
if(parameter.isAvg()){
if("year".equals(parameter.getTimeStyle())){
orgProfit.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearANDreporting_month='12')AS"+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgProfit.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
}

orgBasicInformation.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");



}

}elseif("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("nullas"+orgStatisticalIndicators.getField()+",");
orgInformationCbrc.append("nullas"+orgStatisticalIndicators.getField()+",");
orgBusinessStructure.append("nullas"+orgStatisticalIndicators.getField()+",");
orgProfit.append("nullas"+orgStatisticalIndicators.getField()+",");

//行业平均
if(parameter.isAvg()){
if("year".equals(parameter.getTimeStyle())){
orgBalanceSheets.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearANDreporting_month='12')AS"+orgStatisticalIndicators.getField()+"Avg,");
}else{
orgBalanceSheets.append("(SELECTavg("+orgStatisticalIndicators.getTableField()+")FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
}


orgProfit.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgInformationCbrc.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBalanceSheets.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_month=reportingMonth)AS"+orgStatisticalIndicators.getField()+"Avg,");
orgBusinessStructure.append("(SELECTavg(null)FROM"+orgStatisticalIndicators.getTableName()+"wherereporting_year=reportingYearandreporting_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条件
StringBuilderwhereSql=newStringBuilder("WHERE1=1");


if("year".equals(parameter.getTimeStyle())){
whereSql.append("ANDreporting_year>=:startYearandreporting_year<=:endYearANDreporting_month='12'");
}else{
whereSql.append("andCONCAT(reporting_year,'-',Right(100+CAST(reporting_monthasSIGNED),2))>=:startYearandCONCAT(reporting_year,'-',Right(100+CAST(reporting_monthasSIGNED),2))<=:endYear");
}

//获取所有机构id
ListparameterCompanyIds=parameter.getCompanyIds();
//如果机构id不为空
if(parameterCompanyIds.size()>0){
whereSql.append("ANDcompany_idin(");


for(inti=0;i>"+orgBalanceSheets.toString());


//创建本地sql查询实例
orgBalanceSheetsQuery=entityManager.createNativeQuery(orgBalanceSheets.toString());

//如果时间为空那就获取现在的时间
if(parameter.getEndTime()==null){
parameter.setEndTime(newDate());
}
if(parameter.getStartTime()==null){
parameter.setStartTime(newDate());
}


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"));
}elseif("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(inti=0;imap=newHashMap<>();
if(cells.length==3){
continue;
}
for(intj=0;jdefinedReportFormVOList=newArrayList<>();
MapstringListMap=newHashMap<>();



//定义返回的格式
for(Mapmap:result){
StringreportingYear=(String)map.get("reportingYear");
StringreportingMonth=(String)map.get("reportingMonth");
StringreportingDate=reportingYear+"-"+reportingMonth;
//如果时间类型是年
if("year".equals(parameter.getTimeStyle())){
Listlist=stringListMap.get(reportingYear);
if(list!=null){
list.add(map);
stringListMap.put(reportingYear,list);
}else{
Listinner=newArrayList();
inner.add(map);
stringListMap.put(reportingYear,inner);
}
}else{//如果为月

Listlist=stringListMap.get(reportingDate);
if(list!=null){
list.add(map);
stringListMap.put(reportingDate,list);
}else{
Listinner=newArrayList();
inner.add(map);
stringListMap.put(reportingDate,inner);
}
}

}

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


for(Map.Entryentry:stringListMap.entrySet()){
DefinedReportFormVOformVO=newDefinedReportFormVO();
formVO.setTime(entry.getKey());

if(parameter.isAvg()==true){
formVO.setArr(setAvg(entry.getValue(),fieldNames));
}else{
formVO.setArr(entry.getValue());
}

definedReportFormVOList.add(formVO);

}


returndefinedReportFormVOList;
}

指标实体

/**
*统计指标
*/
@Entity
@Table(name="org_statistical_indicators",catalog="zhsupervision")
publicclassOrgStatisticalIndicators{
@Id
@GeneratedValue
privateLongid;
/**
*前端显示名
*/
privateStringname;
/**
*表属性
*/
privateStringtableField;
/**
*表名称
*/
privateStringtableName;
/**
*创建时间
*/
privateDatecreateTime;
/**
*更新时间
*/
privateDateupdateTime;
/**
*删除标识
*/
privateStringdelFlag;
//父节点
privateLongpId;
//属性
privateStringfield;
//该指标查询月的时候是否查询
privateStringmonthQuery;
//该指标查询年的时候是否查询
privateStringyearQuery;

publicStringgetMonthQuery(){
returnmonthQuery;
}

publicvoidsetMonthQuery(StringmonthQuery){
this.monthQuery=monthQuery;
}

publicStringgetYearQuery(){
returnyearQuery;
}

publicvoidsetYearQuery(StringyearQuery){
this.yearQuery=yearQuery;
}

publicStringgetField(){
returnfield;
}

publicvoidsetField(Stringfield){
this.field=field;
}

publicLonggetId(){
returnid;
}

publicvoidsetId(Longid){
this.id=id;
}

publicLonggetpId(){
returnpId;
}

publicvoidsetpId(LongpId){
this.pId=pId;
}

publicStringgetName(){
returnname;
}

publicvoidsetName(Stringname){
this.name=name;
}

publicStringgetTableField(){
returntableField;
}

publicvoidsetTableField(StringtableField){
this.tableField=tableField;
}

publicStringgetTableName(){
returntableName;
}

publicvoidsetTableName(StringtableName){
this.tableName=tableName;
}

publicDategetCreateTime(){
returncreateTime;
}

publicvoidsetCreateTime(DatecreateTime){
this.createTime=createTime;
}

publicDategetUpdateTime(){
returnupdateTime;
}

publicvoidsetUpdateTime(DateupdateTime){
this.updateTime=updateTime;
}

publicStringgetDelFlag(){
returndelFlag;
}

publicvoidsetDelFlag(StringdelFlag){
this.delFlag=delFlag;
}
}

指标Service

/**
*统计指标服务类
*/
publicinterfaceOrgStatisticalIndicatorsService{
/**
*根据id获取
*@paramid
*@return
*/
OrgStatisticalIndicatorsfindOrgStatisticalIndicatorsById(Longid);

/**
*根据表名查询
*/
ListfindOrgStatisticalIndicatorsByTableName(Stringname);

}

指标serviceImpl

@Service
publicclassOrgStatisticalIndicatorsServiceImplextendsBaseServiceImplimplementsOrgStatisticalIndicatorsService{

@Autowired
privateOrgStatisticalIndicatorsRespositoryrespository;

@Override
publicOrgStatisticalIndicatorsfindOrgStatisticalIndicatorsById(Longid){
returnrespository.findByIdAndAndDelFlag(id);
}

@Override
publicListfindOrgStatisticalIndicatorsByTableName(Stringname){
returnrespository.findOrgStatisticalIndicatorsByTableName(name);
}
}

指标repository

publicinterfaceOrgStatisticalIndicatorsRespositoryextendsJpaSpecificationExecutor{

@Query(value="select*fromorg_statistical_indicatorsWHEREID=?1anddel_flag='0'",nativeQuery=true)
OrgStatisticalIndicatorsfindByIdAndAndDelFlag(Longid);

@Query(value="select*fromorg_statistical_indicatorsWHEREdel_flag='0'andNAME=?1",nativeQuery=true)
OrgStatisticalIndicatorsfindOrgStatisticalIndicatorsByName(Stringname);
}

这个repository要继承extendsJpaRepository才可以，写漏了。

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

总结

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

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

热门推荐

随机推荐