How to use Criteria And pagination

user_3628479 0
Viewed 5285

Hi guys I'm trying to use criteria along with pagination but my code isn't working, here it is :

public Page<Person> getAuthorizationsTest() {

    PageRequest pageRequest = new PageRequest(1, 5);

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Person> queryBase = criteriaBuilder.createQuery(Person.class);
    Root<Person> root = queryBase.from(Person.class);
    List<Predicate> queryConditions = new ArrayList<>();

    Predicate predicate = criteriaBuilder.like(root.get("name"), "%[myValue]%");
    queryConditions.add(predicate);

    queryBase.where(queryConditions.toArray(new Predicate[]{}));

    TypedQuery<Person> query = em.createQuery(queryBase);

    List<Person> list = query.getResultList();

    Page<Person> authorizations = new PageImpl<Person>(list, pageRequest, list.size());

    return authorizations;
}

All seems fine, but when I execute it, I receive a list Page with all the results and not only the ones specified in my pageRequest

What am I doing wrong ?

spring-boot criteria pagination java
2 Answers

Predicate array and where clause looking properly defined. In these lines:

Predicate predicate = criteriaBuilder.like(root.get("name"), "%[myValue]%"); queryConditions.add(predicate);

queryBase.where(queryConditions.toArray(new Predicate[]{}));

TypedQuery<Person> query = em.createQuery(queryBase);

Just a reminder. Maybe you need criteriaBuilder.equal instead of criteriaBuilder.like. If you sure what you did you can omitted this reminder.

You need to change following lines like:

query.setFirstResult(Math.toIntExact(pageRequest.getOffset()));
query.setMaxResults(pageRequest.getPageSize());

return new PageImpl<Person>(query.getResultList(), pageRequest, getTotalCount(criteriaBuilder, queryConditions));

Then add getTotalCount method for the sake of consistency.

private Long getTotalCount(CriteriaBuilder criteriaBuilder, Predicate[] predicateArray) {
        CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
        Root<T> root = criteriaQuery.from(entityType);

        criteriaQuery.select(criteriaBuilder.count(root));
        criteriaQuery.where(predicateArray);

        return entityManager.createQuery(criteriaQuery).getSingleResult();
    }
user_64243830

You are not using a paging request :

List list = query.getResultList();

This query will always query for all the results which is very inefficient.

Instead, you should do this the Spring way ie you should use a repository interface that extends a PagingAndSortingRepository (such as the JpaRepository interface).

Then simply use a method to paginate your results such as findAll as stated in the documentation :

PagingAndSortingRepository<User, Long> repository = // … get access to a bean
Page<User> users = repository.findAll(new PageRequest(1, 20));
user_26968650
Related