translating a TypedQuery to criteriaQuery (JPA 2.0) with max, min, group by and order by
Following my working 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);
Now the main problem for me is to group by Year(date) [date has the format of YYYY-MM-DD hh:MM] and to write the Max and Min expressions.
I started like this: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();
at the moment the values Max and Min are the same... but this is quite logical, because of not grouping by the year for the fetchDate... How can I achieve the grouping just by the year of my date and how would the max-min calculation look like?
thanks in advance
Ron.You can use the Criteria api to invoke a function on the underlying dbms, like YEAR or MONTH. Here is an example:
builder.function("YEAR", Integer.class, r.get("fetchDate"))
精彩评论