平移通过

Question

一个TypedQuery到CriteriaQuery中（JPA 2.0）使用最大值，最小值，组和顺序我有在翻译以及工作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]和写的最大值和最小值的表达式。
我开始是这样的：

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(); 

此刻的值最大值和最小值都一样......但是这是非常合乎逻辑的，因为在今年的fetchDate ... 怎能不分组我只在我约会的那一年实现了分组，最大最小计算将如何显示？

在此先感谢
罗恩。

Answer 1

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

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