Using JPA CriteriaQuery to build Paginated Queries

Advertisements

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.

Advertisements

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;
}

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;
}

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

Advertisements

One comment

  1. Hi,

    Nice and very clear article. I’m trying to do pagination in a query with joins and group by created with JPA Criteria Query. Something like:
    select count(*) from (
    select p.name, min(ps.price) from product
    inner join product_supplier ps on ps.product_id = p.id
    group by p.name
    )

    Code for do the query based on your article is:
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery tupleQuery = cb.createTupleQuery();

    Root product = tupleQuery.from(product.class);
    Join productSuppliers = product.join(“productSuppliers”);

    tupleQuery.multiselect(
    product,
    cb.min(productSuppliers.get(“price”)).alias(“price”)
    );
    tupleQuery.groupBy(product);
    tupleQuery.orderBy(cb.asc(product.get(“name”)));

    TypedQuery query = entityManager
    .createQuery(tupleQuery)
    .setMaxResults(pageable.getPageSize())
    .setFirstResult(Long.valueOf(pageable.getOffset()).intValue());

    But is not possible to count total results of this query. Doing the count, returns more than one result because the query executed is:
    select
    count(product0_.id) as col_0_0_
    from
    pu_products product0_
    inner join
    pu_product_suppliers productsu1_
    on product0_.id=productsu1_.product_id
    group by
    product0_.id

    Sorry for the long comment.
    Thanks.

Leave a Reply to Oscar Cancel reply

Your email address will not be published. Required fields are marked *