使用jpa利用queryDSL实现无sql聚合查询

1、直接上代码

public Result selectDate(QuestionnaireDataSearchDto searchDto){
          
   
        QWsHighTemperature qentity = QWsHighTemperature.wsHighTemperature;
        BlazeJPAQuery<Tuple> blazeJPAQuery = new BlazeJPAQuery<>(entityManager, createCriteriaBuilderFactory);
        blazeJPAQuery.from(qentity);
        //查询聚合结果
        blazeJPAQuery.select(qentity.dataTime.year(), qentity.dailyMaxTemperature.count());

        //拼接where条件
        blazeJPAQuery.where(qentity.stationCode.eq(searchDto.getStationCode()));
        blazeJPAQuery.where(qentity.dailyMaxTemperature.goe(35F));

        //按年进行聚合
        blazeJPAQuery.groupBy(qentity.dataTime.year());

        List<Tuple> fetch = blazeJPAQuery.fetch();
        Map<String, List<Object>> resultMap = new HashMap<>();
        resultMap.put("date", new ArrayList<>());
        resultMap.put("data", new ArrayList<>());
        // 对返回的数据进行封装，返回适合前端解析的格式。
        for (Tuple tuple : fetch) {
          
   
            resultMap.get("date").add(tuple.get(qentity.dataTime.year()));
            resultMap.get("data").add(tuple.get(qentity.dailyMaxTemperature.count()));
        }
        return Result.success(resultMap);
    }

返回结果，按照历年进行统计查询

{
          
   
		"date": [
			2005,
			2010,
			2017
		],
		"data": [
			3,
			2,
			2
		]
	}

2、补充一个自定义时间格式化的

public Result selectDate(QuestionnaireDataSearchDto searchDto){
          
   
        QWsHighTemperature qentity = QWsHighTemperature.wsHighTemperature;
        StringTemplate stringTemplate = Expressions.stringTemplate("TO_CHAR({0}, MM)", qentity.dataTime);
        BlazeJPAQuery<Tuple> blazeJPAQuery = new BlazeJPAQuery<>(entityManager, createCriteriaBuilderFactory);
        blazeJPAQuery.from(qentity);
        //查询聚合结果
        blazeJPAQuery.select(stringTemplate, qentity.dailyMaxTemperature.count());

        //拼接where条件
        blazeJPAQuery.where(qentity.stationCode.eq(searchDto.getStationCode()));
        blazeJPAQuery.where(qentity.dailyMaxTemperature.goe(35F));

        //按月进行聚合
        blazeJPAQuery.groupBy(stringTemplate);

        List<Tuple> fetch = blazeJPAQuery.fetch();
        Map<String, List<Object>> resultMap = new HashMap<>();
        resultMap.put("date", new ArrayList<>());
        resultMap.put("data", new ArrayList<>());
        // 对返回的数据进行封装，返回适合前端解析的格式。
        for (Tuple tuple : fetch) {
          
   
            resultMap.get("date").add(tuple.get(stringTemplate));
            resultMap.get("data").add(tuple.get(qentity.dailyMaxTemperature.count()));
        }
        return Result.success(resultMap);
    }

返回结果，按照累年每月进行聚合统计，不管有多少年返回的都是12个月

{
          
   
		"date": [
			"06",
			"07"
		],
		"data": [
			2,
			5
		]
	}