将 TypedQuery 转换为具有 max、min、group by 和 order by 的 criteriaQuery (JPA 2.0)

Question

我在将运行良好的 typedQuery 转换为纯 JPA 2.0 criteriaQuery 时遇到了一些困难：/

遵循我的工作 typedQuery：

String name = "Station1";

Query qry = em.createQuery("
select YEAR(s.fetchDate), MAX(s.actExport)-MIN(s.actExport), MIN(s.actExport), MAX(s.actExport) 
from StationItem s 
where s.fetchDate >= '2008' and s.fetchDate < '2012' 
    and s.errorState=0
    and s.actExport>0
    and s.name = :name        
group by YEAR(s.fetchDate)
order by s.fetchDate ");

qry.setParameter("name", name);

现在对我来说主要问题是按年份（日期）分组 [日期的格式为 YYYY-MM-DD hh: MM] 并写出 Max 和 Min 表达式。
我是这样开始的：

Date endTime = new Date();
Date startTime = DateUtil.yearsInPast(5,endTime); //own helper class

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<StationItem> r = cq.from(StationItem.class);

// setting predicates (used in where clause)
Predicate pStation = cb.equal(r.get("name"), station);
Predicate pError = cb.equal(r.get("errorState"), 0);
Predicate pTime = cb.between(r.get("fetchDate").as(Date.class), startTime, endTime);
Predicate pNotNull = cb.notEqual(r.get(selection), 0);

// setting expressions (used in select clause)
Expression<Number> select = r.get(selection);
Expression<Number> maximum = cb.max(select);
Expression<Number> minimum = cb.min(select);
Expression<Date> fetchDate = r.get("fetchDate").as(Date.class);

// building my query (select, where, group by, order by)
cq.multiselect(fetchDate, select, maximum, minimum); //fetchDate needs to be shrinked to only the Year! and Max-Min would be nice (but this can be done when writing the data to an object/list)
cq.where(pStation, pTime, pError, pNotNull);
cq.groupBy(fetchDate); //grouping should be done by the year of the fetchDate
cq.orderBy(cb.asc(fetchDate));

// write results to a list
List<Tuple> l = em.createQuery(cq).getResultList();

目前 Max 和 Min 的值是相同的...但这很合乎逻辑，因为 fetchDate 没有按年份分组... 如何仅按日期年份进行分组以及最大最小计算结果如何？

提前致谢
罗恩.

Answer 1

您可以使用 Criteria api 调用底层 dbms 上的函数，例如 YEAR 或 MONTH。这是一个例子：

builder.function("YEAR", Integer.class, r.get("fetchDate"))