CriteriaBuilder greatest date only

Question

I'm trying to understand how to select ONLY the latest date on a record from a join. My People entity is joined with a Membership entity. My Membership entity has a RefMembershipStatus entity.. I an trying to select ONLY the most current date from the Membership entity... My joins look as follows:

Join<People, Membership> membershipPath = root.join(People_.membershipList);

//Membership has property: Membership_.membershipStatusDate   --  I must retrieve ONLY the latest (most current) date in membershipStatusDate..

Join<Membership, RefMembershipStatus> progPath = membershipPath.join(Membership_.refMembershipStatus);
predicateList.add(cb.and(progPath.in(selectedStatus)));

Answer 1

There are two ways how you can do this.

Either you need to add a predicate which finds the max date. In the CriteriaBuilder API you would use the greatest method. It would look something like this:

Root<Membership> membership = criteria.from(Membership.class);
predicateList.add(cb.greatest(membership.get(Membership_.membershipStatusDate)));

Or you could use orderBy and then just select the first result with getFirstResult:

criteriaQuery.orderBy(cb.desc(membership.get(Membership_.membershipStatusDate)));
entityManager.createQuery(criteriaQuery).getFirstResult();