I usually post about Persistence on Twitter - you can follow me there:

1. Overview

This article illustrates how to implement pagination in the Java Persistence API.

It explains how to do paging with basic JQL and with the more type-safe Criteria-based API’s, discussing the advantages and known issues of each implementation.

2. Pagination with JQL and the setFirstResult(), setMaxResults() API

The simplest way to implement pagination is to use the Java Query Language – create a query and configure it via setMaxResults and setFirstResult:

Query query = entityManager.createQuery("From Foo");
int pageNumber = 1;
int pageSize = 10;
query.setFirstResult((pageNumber-1) * pageSize); 
query.setMaxResults(pageSize);
List <Foo> fooList = query.getResultList();

The API is simple:

  • setFirstResult(int): Sets the offset position in the result set to start pagination
  • setMaxResults(int): Sets the maximum number of entities that should be included in the page

2.1. The Total Count and the Last Page

For a more complete pagination solution, we’ll also need to get the total result count:

Query queryTotal = entityManager.createQuery
    ("Select count(f.id) from Foo f");
long countResult = (long)queryTotal.getSingleResult();

Calculating the last page is also very useful:

int pageSize = 10;
int pageNumber = (int) ((countResult / pageSize) + 1);

Notice that this approach to getting the total count of the result set does require an additional query (for the count).

3. Pagination with JQL Using the Id’s of Entities

A simple alternative pagination strategy is to first retrieve the full ids and then – based on these – retrieve the full entities. This allows for better control of entity fetching – but it also means that it needs to load the entire table to retrieve the ids:

Query queryForIds = entityManager.createQuery(
  "Select f.id from Foo f order by f.lastName");
List<Integer> fooIds = queryForIds.getResultList();
Query query = entityManager.createQuery(
  "Select f from Foo e where f.id in :ids");
query.setParameter("ids", fooIds.subList(0,10));
List<Foo> fooList = query.getResultList();

Finally, also note that it requires 2 distinct queries to retrieve the full results.

4. Pagination with JPA Using Criteria API

Next, let’s look at how we can leverage the JPA Criteria API to implement pagination:

int pageSize = 10;
CriteriaBuilder criteriaBuilder = entityManager
  .getCriteriaBuilder();
CriteriaQuery<Foo> criteriaQuery = criteriaBuilder
  .createQuery(Foo.class);
Root<Foo> from = criteriaQuery.from(Foo.class);
CriteriaQuery<Foo> select = criteriaQuery.select(from);
TypedQuery<Foo> typedQuery = entityManager.createQuery(select);
typedQuery.setFirstResult(0);
typedQuery.setMaxResults(pageSize);
List<Foo> fooList = typedQuery.getResultList();

This is useful when the aim is to create dynamic, failure-safe queries. In contrast to “hard-coded”, “string-based” JQL or HQL queries, JPA Criteria reduces run-time failures because the compiler dynamically checks for query errors.

With JPA Criteria getting the total number of entities in simple enough:

CriteriaQuery<Long> countQuery = criteriaBuilder
  .createQuery(Long.class);
countQuery.select(criteriaBuilder.count(
  countQuery.from(Foo.class)));
Long count = entityManager.createQuery(countQuery)
  .getSingleResult();

The end result is a full pagination solution, using the JPA Criteria API:

int pageNumber = 1;
int pageSize = 10;
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery<Long> countQuery = criteriaBuilder
  .createQuery(Long.class);
countQuery.select(criteriaBuilder
  .count(countQuery.from(Foo.class)));
Long count = entityManager.createQuery(countQuery)
  .getSingleResult();

CriteriaQuery<Foo> criteriaQuery = criteriaBuilder
  .createQuery(Foo.class);
Root<Foo> from = criteriaQuery.from(Foo.class);
CriteriaQuery<Foo> select = criteriaQuery.select(from);

TypedQuery<Foo> typedQuery = entityManager.createQuery(select);
while (pageNumber < count.intValue()) {
    typedQuery.setFirstResult(pageNumber - 1);
    typedQuery.setMaxResults(pageSize);
    System.out.println("Current page: " + typedQuery.getResultList());
    pageNumber += pageSize;
}

5. Conclusion

This article has explored the basic pagination options available in JPA.

Some have drawbacks – mainly related to query performance, but these are usually offset by improved control and overall flexibility.

The implementation of this Spring JPA Tutorial can be found in the GitHub project – this is an Maven-based project, so it should be easy to import and run as it is.

I usually post about Persistence on Twitter - you can follow me there:


  • David

    Using offsets for pagination works okay for small tables, but is terribly inefficient, because you will read and discard all record prior to the page you want. Also without an order by clause the order of the results is not guaranteed to stay the same over time, leading to hard to find bugs where you skip rows or see rows on two pages.

    • Hey David – thanks for the feedback. Agreed – pagination is not always efficient – you do need to pay close attention to the data you’re paginating over (same as when doing anything else with SQL really, especially with JPA on top of it). I would add that I’m not sure what you mean by reading and discarding all records prior to the current page – the generated query will use LIMIT and will retrieve the specified records and nothing more.
      On the order by clause – it makes sense to use it in real-world scenarios; the main reason I left it out is that these are basic scenarios, meant to show how to use the various APIs to do pagination; I will however add a note to mention this – thanks for the suggestion.
      Cheers,
      Eugen.

    • I agree with David here. Often, you should consider whether keyset paging isn’t the better option. This works both with SQL and JPQL. If you have a tool like jOOQ, it might be a bit easier to express.

  • I’ve always been wondering: JPA has these methods that you’ve mentioned:


    typedQuery.setFirstResult(pageNumber - 1);
    typedQuery.setMaxResults(pageSize);

    … Do these methods translate to the underlying SQL dialect’s LIMIT .. OFFSET, TOP .. START AT, OFFSET .. FETCH or ROWNUM / ROW_NUMBER() filtering? Or do they just skip / cut off JDBC ResultSets, internally, using JDBC’s maxRows property?

    • Hey Lukas, thanks for the detailed articles. Yes, these calls do generate statements with the LIMIT syntax. Cheers,
      Eugen.

      • … and also with ROWNUM / ROW_NUMBER() filtering? What happens when you use limit / offset on Oracle – e.g. in a complex JPQL query? Can you also use offset / limit in a subquery?

        • For Oracle specifally – yes, it will use rownum syntax. As for the subquery – I remember an open Hibernate bug about this, which, like all good Hibernate bugs was more than 5 years old, so – if using Hibernate at least, that may not work. I should try this out and get the results incorporated into the article. Initially I though about it as a simple, introductory article, but info about this would make sense as well. Cheers,
          Eugen.

          • Awesome! Or even better, write a follow-up article 🙂

            Also curious about DB2, for instance, as DB2 supports the equivalent of LIMIT but not that of OFFSET

  • Gloria

    I am little bit confused, is the count value obtained the same as the
    value obtained at get the size of the result list returned by the
    getResultList() method?

    • No, the total count is separate, but first – to make sure I’m looking at the same example you are – what section are you referring to?
      Cheers,
      Eugen.

      • Gloria

        Thanks for reply Eugen, I was talking about the last sample “Pagination with JPA Using Criteria API”.
        I did a few test with my project, seems that count return the total number of rows from the query but getResultList is returning the total of entities, I mean the results are different.
        I have to mention that I am using distinct(true) before get the result list, and not applying the same before count, I guess this can be producing the different results, wondering if adding a countDistinct before the do the count will cause to retrieve all the data just for count.
        I do not want to retrieve data twice.

        • That depends a lot on your dataset of course, but it’s entirely possible to get different results if you’re applying the distinct (or any other condition) on one query and not on the other.
          An example might be good here – have a look at the codebase backing this article and see if you can reproduce the problem there. I’d be happy to have a look at a failing test.
          Cheers,
          Eugen.