How to write a MAX query with a where clause in JPA 2.0?

Question

I am using JPA 2.0. Hibernate 4.1.0.Final and Java 6. How to write a JPA query from the following psuedo-SQL?

select max(e.dateProcessed) from Event e where e.org = myOrg 

And my domain object is as follows:

 @GenericGenerator(name = "uuid-strategy", strategy = "org.mainco.subco.core.util.subcoUUIDGenerator") @Entity @Table(name = "sb__event", uniqueConstraints = { @UniqueConstraint(columnNames={"EVENT_ID"}) } ) public class Event { @Id @Column(name = "ID") @GeneratedValue(generator = "uuid-strategy") private String id; @ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.REMOVE}) @JoinColumn(name = "ORGANIZATION_ID", nullable = false, updatable = true) private Organization org; @Column(name = "DATE_PROCESSED") @NotNull private java.util.Date dateProcessed; 

I know that CriteriaBuilder.greatest is involved, but I just can't figure out how to write a query. This will return all event objects that match the organization, but what I got.

 final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder(); final CriteriaQuery<Event> criteria = builder.createQuery(Event.class); final Root<Event> event = criteria.from(Event.class); criteria.select(event); criteria.where(builder.equal(Event.get("org"), org)); results.addAll(m_entityManager.createQuery(criteria).getResultList()); 

Answer 1

There are two ways: one uses JPQL, the other using criteria queries.
JPQL is simple:

 em.createQuery("select max(e.dateProcessed) from Event e where e.org = :myOrg") .setParameter("myOrg", myOrg) .getSingleResult(); 

when using criteria that you may have:

 CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaQuery<Number> cq = qb.createQuery(Number.class); Root<Event> root = cq.from(Event.class); cq.select(qb.max(root.get("dateProcessed"))); cq.where(qb.equal(Event.get("org"), qb.parameter(MyOrgType.class, "myOrg"))); em.createQuery(cq).setParameter("myOrg", myOrg).getSingleResult(); 

Answer 2

With JPQL and CriteriaBuilder

 CriteriaBuilder cb = getEntityManager().getCriteriaBuilder(); javax.persistence.criteria.CriteriaQuery cq= getEntityManager().getCriteriaBuilder().createQuery(); Root<T> c = cq.from(getEntityClass()); cq.select(cb.max(c.get("id")));