javacriteriaquery

CriteriaQuery with max


I have a table like this :

bookName            updateId
----------------------------
LordOfTheRings      12
WheelOfTime         3
Cradle              9
WheelOfTime         14
Cradle              10
Cradle              11

How can i build a CriteriaQuery to get the maximum updateId for a given book ? I need something like this :

bookCriteriaQuery.select(bookRoot);
bookCriteriaQuery.and(
    bookCriteriaBuilder.equal(bookRoot.get("bookName"), "Cradle"),
    bookCriteriaBuilder.max(bookRoot.get("updateId")) // <---  except this doesn't work
);
Book newest = entityManager.createQuery(bookCriteriaQuery).getSingleResult();

Solution

  • Your query can be slightly modified to perform the desired result like so :

        Predicate pred = cb.equal(cb.equal(bookRoot.get("bookName"), "Cradle"));
        Order order = cb.desc(bookRoot.get("updateId"));
        query.select(bookRoot).where(pred).orderBy(order);
        entityManager.createQuery(query).setMaxResults(1)
            .getResultList().stream()
            .findFirst();
    

    Note that if your query finds more than 1 result an exception will be thrown by.getSingleResult()