JPA Criteria api - Total records for concrete query within pagination

Question

I am programming function for pagination in my repository layer. Function receive as parameters spring's pageable object and some value like this:

public Page<Foo> filterFoo(Pageable pageable, String value) {
   CriteriaBuilder cb = entityManager.getCriteriaBuilder();
   CriteriaQuery<Foo> fooQuery = cb.createQuery(Foo.class);
   Root<Foo> foo = fooQuery .from(Foo.class);
   fooQuery .where(adding predicate for match value);

   List<Foo> result = entityManager.createQuery(fooQuery )
      .setFirstResult((pageable.getPageNumber() - 1) * pageable.getPageSize())
      .setMaxResults(pageable.getPageSize())
      .getResultList();
   return new PageImpl<>(result, pageable, xxxx);
}

Function return spring's PageImpl object filled with my result. To PageImpl I also need set total count of objects which suit predicates. This count number have to be of course without maxResult and firstResult. Is possible create another database call with my fooQuery to get total database records for that query without limit? What is the best practise to use pageable and criteria api in JPA? Thank you in advice.

Hi, Did you manage to find a workaround for this? – cp5 Apr 24 '19 at 06:35 — cp5, Apr 24 '19 at 06:35

score 0 · Answer 1 · answered Nov 02 '22 at 22:07

Because generated SQL uses aliases - you may need make separate query for get total count of rows. For example:

CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
countQuery.select(cb.count(countQuery.from(Foo.class)));
if (Objects.nonNull(filters)) {
    countQuery.where(filters);
}
return new PageImpl<>(result, pageable, em.createQuery(countQuery).getSingleResult());

where filters is equal to your adding predicate for match value expression.

Also, you may use a TupleQuery with custom SQL function for calculate count of rows in one select query. Like this:

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "count_over",
            new SQLFunctionTemplate(
                StandardBasicTypes.LONG,
                "(count(?1) over())"
            )
        );
    }
}

and Criteria:

public Page<Foo> findAll(Specification<Foo> specification, Pageable pageable) {
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<Foo.class> fooRoot = cq.from(Foo.class);
    cq.select(cb.tuple(fooRoot, cb.function("count_over", Long.class, fooRoot.get("id"))));

    Predicate filters = specification.toPredicate(fooRoot, cq, cb);
    if (Objects.nonNull(filters)) {
        cq.where(filters);
    }

    TypedQuery<Tuple> query = em.createQuery(cq);
    query.setFirstResult((int) pageable.getOffset());
    query.setMaxResults(pageable.getPageSize());
    List<Tuple> result = query.getResultList();
    if (result.isEmpty()) {
        return new PageImpl<>(List.of());
    }
    return new PageImpl<>(
        result.stream().map(tuple -> (Foo) tuple.get(0)).collect(toUnmodifiableList()),
        pageable,
        (long) result.get(0).get(1)
    );
}

See more about SQLFunction: https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/ and Custom SQL for Order in JPA Criteria API

JPA Criteria api - Total records for concrete query within pagination

1 Answers1