JPA criteria return page and total

Question

for paginated data (paginated) I need to return the total number of records matching my criteria and the first page of results. this is useful for displaying information to users and calculating the total number of expected pages (on the client).

I am currently running the same query twice, once for a total bill and once for actual records. I hope there is a more efficient way.

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

counter:

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

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 assuming that there is an efficient query that retrieves both the record of the record and the pages themselves, paginated, you will need to make an additional call to db for each page that the user accesses. And this would make the performance gain gained by accessing the first page insignificant.

I mean the following: to access the first page you need:

• total number of filtered items without pagination
• paginated filtered items for page 1

To access other pages, you no longer need a counting request. You only need the filtered elements for page x.

After expanding n pages, even using the optimization you are looking for, you made n calls instead of n + 1 calls of the non-optimized version. Therefore, I would not spend too much time thinking about it.

You only need to be careful in your implementation so as not to execute the record counter when it is not needed: you need it only after the user changes the filtering.