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

public String getTime() {

return time;

}

public void setTime(String time) {

this.time = time;

}

public List

return arr;

}

public void setArr(List

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 defindQuery(DefinedReportFormDTO parameter);

}

实现类ServiceImpl

@SuppressWarnings("unchecked")

@Override

public List 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,"http://);

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 orgList = orgService.getOrgList();

//拼接所有机构的字符串（如果需要求平均数的话）

for (Company company : orgList) {

companyIds.append(company.getId()+",");

}

companyIds.deleteCharAt(companyIds.length()-1);

//定义每个表的字符串判断

Map bool = new HashMap<>();

//指标名

List fieldNames = new ArrayList();

//返回结果

List

//指标名默认添加年月机构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.getTableNagvlsQDYme()+" 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() + ",");

http:// 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 map = new HashMap<>();

if(cells.length == 3){

continue;

}

for (int j = 0; 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 definedReportFormVOList = new ArrayList<>();

Map stringListMap = new HashMap<>();

//定义返回的格式

for (Map 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 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 setYearQhttp://uery(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 findOrgStatisticalIndicatorsByTableName(String name);

}

指标serviceImpl

@Service

public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl implements OrgStatisticalIndicatorsService {

@Autowired

private OrgStatisticalIndicatorsRespository respository;

@Override

public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) {

return respository.findByIdAndAndDelFlag(id);

}

@Override

public List 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 才可以，写漏了。

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

总结

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

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 definedReportFormVOList = new ArrayList<>();

Map stringListMap = new HashMap<>();

//定义返回的格式

for (Map 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 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 setYearQhttp://uery(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 findOrgStatisticalIndicatorsByTableName(String name);

}

指标serviceImpl

@Service

public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl implements OrgStatisticalIndicatorsService {

@Autowired

private OrgStatisticalIndicatorsRespository respository;

@Override

public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) {

return respository.findByIdAndAndDelFlag(id);

}

@Override

public List 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 才可以，写漏了。

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

总结

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。