Databases
StackOverflow2013
Postsdbo
POtranslating a TypedQuery to criteriaQuery (JPA 2.0) with max, min, group by and order by
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POtranslating a TypedQuery to criteriaQuery (JPA 2.0) with max, min, group by and order by
    text
    copied!<p><br /> I have some struggles in translating a well working typedQuery to a pure JPA 2.0 criteriaQuery :/</p> <p>Following my working typedQuery:</p> <pre><code>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 &gt;= '2008' and s.fetchDate &lt; '2012' and s.errorState=0 and s.actExport&gt;0 and s.name = :name group by YEAR(s.fetchDate) order by s.fetchDate "); qry.setParameter("name", name); </code></pre> <p>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.<br /> I started like this:</p> <pre><code>Date endTime = new Date(); Date startTime = DateUtil.yearsInPast(5,endTime); //own helper class CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery&lt;Tuple&gt; cq = cb.createTupleQuery(); Root&lt;StationItem&gt; 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&lt;Number&gt; select = r.get(selection); Expression&lt;Number&gt; maximum = cb.max(select); Expression&lt;Number&gt; minimum = cb.min(select); Expression&lt;Date&gt; 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&lt;Tuple&gt; l = em.createQuery(cq).getResultList(); </code></pre> <p>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?</p> <p>thanks in advance<br /> Ron.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload