Using JPA CriteriaQuery to build Paginated Queries

When we work creating APIs, and we have the need to return a list of data, it’s very common to use pagination. So, if we have thousands or millions of records, we don’t return all of them in the same request. If we would do it, the performance of the application might be compromised seriously.

Pagination provides us with a way to return subsets of data that we want to display or handle. In the Java world, it is probably to be using JPA to access the data/persistence layer, the idea of this article is to explain how to do pagination using the JPA Criteria features. The Criteria API of JPA allows creating queries programmatically, which are very useful when you have optional filter clauses as part the wherethat you can not handle statically with a JPQL query.

Let’s start

For a Paginated structure we need 2 parts:

• The data of the page (which contains only a subset of the result). A is defined page by an offset (position of the first record) and a limit (size of the page).
• The total number of all the records for the query.

It’s time to query the persistence layer (database) to get the information for that structure.

To get the data page, we will use methods setMaxResults(int) to indicate the size of the page and setFirstResult(int) to indicate offset or initial position. From the Criteria API, we are using the CriteriaBuilder to create a CriteriaQuery, with the latter, we will create the desired query

public List<Person> findAll(int offset, int limit){    
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Person> criteriaQuery = cb.createQuery(Person.class);
    Root<ECronExecution> root = criteriaQuery.from(Person.class);
    criteriaQuery.select(root);

    List<Person> result =
        entityManager
            .createQuery(criteriaQuery)
            .setMaxResults(limit)
            .setFirstResult(offset)
            .getResultList();

    return result;
}

public List<Person> findAll(int offset, int limit){    

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    CriteriaQuery<Person> criteriaQuery = cb.createQuery(Person.class);

    Root<ECronExecution> root = criteriaQuery.from(Person.class);

    criteriaQuery.select(root);

​

    List<Person> result =

        entityManager

            .createQuery(criteriaQuery)

            .setMaxResults(limit)

            .setFirstResult(offset)

            .getResultList();

​

    return result;

}

The method to get the total count of records is pretty similar to the previous one. But the difference is the CriteriaQuery will return a Long type, and when we are creating the select clause we will use the CriteriaBuilder to execute a count. The other difference is that we are not using the setFirstResult and setMaxResults method on it.

public Long countAll(){ 	
	CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);
    Root<Person> root = criteriaQuery.from(Person.class);
    criteriaQuery.select(cb.count(root));

    Long result = entityManager.createQuery(criteriaQuery).getSingleResult();
	return result;
}

public Long countAll(){     

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);

    Root<Person> root = criteriaQuery.from(Person.class);

    criteriaQuery.select(cb.count(root));

​

    Long result = entityManager.createQuery(criteriaQuery).getSingleResult();

    return result;

}

Ok, it’s true, the previous queries are very plain and straight forward, in a real application we would probably need to filter the data by somes. The Criteria API allows filtering values in the query, creating an array of Predicates and then adding them to the where clause of the Criteria Query. As we can see, we use the CriteriaBuilder to create the predicates.

    List<Predicate> predicatesList = new ArrayList<>();
    predicatesList.add(cb.equal(root.get("status"), status));
    if (status != null) {
      predicatesList.add(cb.equal(root.get("lastName"), lastName));
    }
    Predicate[] finalPredicates = new Predicate[predicatesList.size()];
    predicatesList.toArray(finalPredicates);
    criteriaQuery.where(finalPredicates);

    List<Predicate> predicatesList = new ArrayList<>();

    predicatesList.add(cb.equal(root.get("status"), status));

    if (status != null) {

      predicatesList.add(cb.equal(root.get("lastName"), lastName));

    }

    Predicate[] finalPredicates = new Predicate[predicatesList.size()];

    predicatesList.toArray(finalPredicates);

    criteriaQuery.where(finalPredicates);