JPA criteria return a page and total

Question

for a paged data (paginated) I need to return the total number of records matching my criteria and the results first page. this is useful to display information for the users and calculate the total number of pages to expect (on the client).

at the moment I run the same query twice, once for the total count and once for the actual records. I hope there is a more efficient way.

can these two queries be combined to one call to the database?

the count:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<VersionJpa> r = cq.from(VersionJpa.class);
    Predicate p = cb.conjunction();
    p = // some filtering code define by caller
    cq.where(p);
    cq.select(cb.count(r));
    TypedQuery<Long> tq = em.createQuery(cq);
    return tq.getSingleResult().intValue();

the page:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<VersionJpa> cq = cb.createQuery(VersionJpa.class);
    Root<VersionJpa> root = cq.from(VersionJpa.class);
    Predicate p = cb.conjunction();
    p = // same filtering code as in the count above
    cq.where(p);
    TypedQuery<VersionJpa> tq = em.createQuery(cq);
    // paginatong
    tq.setFirstResults(first); // from caller
    tq.setMaxResults(max);     // from caller
    return tq.getResultList();

Answer 1

Even supposing that exists an efficient query that retrieves both the record count and the paginated records themselves, you would need to perform an additional call to the db for each page the user accesses. And this would make negligible the performance gain obtained during the access to the first page.

I mean the following: for accessing the first page, you need:

1. the total count of the filtered items without pagination
2. the filtered items with the pagination for page 1

For accessing the other pages, you don't need anymore the count query. You only need the filtered items for page x.

After unfolding n pages, even using the optimization you are looking for, you'd have performed n calls instead of the n+1 calls of the non-optimized version. So, I'd not spend too much time in thinking about this.

You only have to be careful, in your implementation, to not perform the record count when it's not necessary: you need it only after the user changes the filtering.