JPA Pagination

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 whet 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 Eclipse based project, so it should be easy to import and run as it is.

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

>> GET THE EBOOKS <<
Get the eBooks and Learn to Build a Simple App
×
Build a Simple but Working App with Spring

  • 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.

    • http://www.baeldung.com/ Eugen Paraschiv

      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.

    • http://www.jooq.org Lukas Eder

      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.

  • http://www.jooq.org Lukas Eder

    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?

    • http://www.baeldung.com/ Eugen Paraschiv

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

      • http://www.jooq.org Lukas Eder

        … 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?

        • http://www.baeldung.com/ Eugen Paraschiv

          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.

          • http://www.jooq.org Lukas Eder

            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