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();

Magen

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.

Diatomaceous

thank you @Diatomaceous very good point. the client is stateless so each call may be the first one so I need to provide the total count on all responses. The database is constantly changing and the total my change from call to call and from page to page (grow). Making the combined query will be useful in my case. it will reduce DB calls from 2n to just n. My understanding to do pagination the DB has to get all objects so the count should be almost cost free. – Magen