Pagination with JPA and Hibernate
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
The size of a result set has a huge impact on the performance of a query, the burden it puts on your Java application, and the user experience your UI provides. It’s a general best practice to split huge result sets into multiple parts presented on separate pages. This process is called pagination.
The most common way to implement pagination using a relational database is to add a LIMIT and OFFSET clause to the SQL query. The LIMIT defines the maximum number of records that shall be returned. The OFFSET clause specifies how many records shall be skipped before the first record gets returned. As long as you ensure a stable ordering of the result set, you can use these 2 clauses to scroll through the result set.
Pagination With Hibernate and JPA
You can, of course, use pagination with JPA and Hibernate. The easiest way to do that is to add the LIMIT and OFFSET clauses to a native SQL query. JPQL and the Criteria API don’t support these 2 clauses as part of the query. But they provide an API to set them. Hibernate will then add the required clauses to the generated SQL statement.
Paginating a Native SQL Query
JPA and Hibernate are designed as a leaky abstraction and enable you to execute any SQL query supported by your database. You only need to call the createNativeQuery method on your EntityManager with an SQL statement. That enables you to use database-specific features, like PostgreSQL’s JSON support. You can also execute SQL UPDATE statements or SELECT statements with a LIMIT and OFFSET clause.
Query query = em.createNativeQuery("SELECT * FROM author a ORDER a.id LIMIT 5 OFFSET 5", Author.class);
List<Author> authors = query.getResultList();
If you provide an entity class as the 2nd parameter to the createNativeQuery method, every record of the result set gets automatically mapped to an entity object. If you want to use a different mapping, e.g., to a DTO class, you can reference an @SqlResultSetMapping instead.
The downside of this approach is that every DBMS supports a slightly different SQL dialect. Hibernate doesn’t change the provided SQL statement. It only sends it to the database. You, therefore, need to make sure that you use the right dialect for your DBMS. This can be problematic if you need to deploy your application using multiple DBMS.
You can avoid this by using the setFirstResult and setMaxResults method on the Query interface.
Query query = em.createNativeQuery("SELECT * FROM author a ORDER a.id", Author.class);
List<Author> authors = query.setFirstResult(5)
.setMaxResults(5)
.getResultList();
Hibernate then uses the database dialect to generate the required SQL clauses. For a PostgreSQL database, these are an OFFSET clause with the value provided to the setFirstResult method and a LIMIT clause with the value supplied to the setMaxResults method.
SELECT * FROM author a ORDER BY a.id limit ? offset ?
Paginating a JPQL Query
As mentioned earlier, JPQL doesn’t support LIMIT and OFFSET clauses. But you can use the same setFirstResult and setMaxResults methods of the Query and TypedQuery interface that I showed you in the previous section.
TypedQuery<Author> query = em.createQuery("SELECT a FROM Author a order by a.id asc", Author.class);
List<Author> authors = query.setFirstResult(5)
.setMaxResults(5)
.getResultList();
Hibernate then uses the configured dialect to add the required SQL clauses to the generated statement. For a PostgreSQL database, these are the LIMIT and an OFFSET clause you can see in the following snippet.
select author0_.id as id1_0_, author0_.firstName as firstnam2_0_, author0_.lastName as lastname3_0_, author0_.version as version4_0_ from Author author0_ order by author0_.id asc limit ? offset ?
Paginating a CriteriaQuery
When you execute a CriteriaQuery, you instantiate the same Query or TypedQuery interfaces as you use for a JPQL query. Because of that, you can use the already explained setFirstResult and setMaxResult methods to add pagination to your CriteriaQuery.
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> root = cq.from(Author.class);
cq.orderBy(cb.asc(root.get("id")));
TypedQuery<Author> query = em.createQuery(cq);
List<Author> authors = query.setFirstResult(5)
.setMaxResults(5)
.getResultList();
Hibernate then uses the configured dialect to generate the required clauses and adds them to the SQL statement.
select author0_.id as id1_0_, author0_.firstName as firstnam2_0_, author0_.lastName as lastname3_0_, author0_.version as version4_0_ from Author author0_ order by author0_.id asc limit ? offset ?
Pitfalls When Using Pagination
As you have seen, using pagination with Hibernate and JPA is simple. But there are a few pitfalls you need to be aware of.
Pitfall 1: Pagination Requires a Stable Order
By calling the setFirstResult and setMaxResults methods on your Query interface, you tell the database the index of the first and the last record of the result set it shall return. If you want to scroll through the entire result set, you need to execute the query multiple times and provide different values to the setFirstResult and setMaxResults methods.
To ensure that this approach works as expected, you also need to ensure that the query always returns the result in the same order. This is only the case if your query contains an ORDER BY clause. Otherwise, the order of the result set is undefined and might change.
TypedQuery<Author> query = em.createQuery("SELECT a FROM Author a order by a.id asc", Author.class);
List<Author> authors = query.setFirstResult(5)
.setMaxResults(5)
.getResultList();
Pitfall 2: Pagination With JOIN FETCH and EntityGraphs Clauses
If your query returns entity objects, you can use an EntityGraph or a JOIN FETCH clause to specify the associations that shall get initialized.
TypedQuery<Author> query = em.createQuery("SELECT a FROM Author a LEFT JOIN fetch a.books ORDER BY a.id", Author.class);
This, of course, affects the generated SQL statement. Hibernate needs to add a JOIN clause for each of the retrieved associations, which increases the size of the result set.
select distinct author0_.id as id1_0_0_, books1_.id as id1_1_1_, author0_.firstName as firstnam2_0_0_, author0_.lastName as lastname3_0_0_, author0_.version as version4_0_0_, books1_.author_id as author_i4_1_1_, books1_.title as title2_1_1_, books1_.version as version3_1_1_, books1_.author_id as author_i4_1_0__, books1_.id as id1_1_0__ from Author author0_ left outer join Book books1_ on author0_.id=books1_.author_id order by author0_.id
When you add pagination to this query, you will see the following warning in the log file.
WARN: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
Hibernate:
select
distinct author0_.id as id1_0_0_,
books1_.id as id1_1_1_,
author0_.firstName as firstnam2_0_0_,
author0_.lastName as lastname3_0_0_,
author0_.version as version4_0_0_,
books1_.author_id as author_i4_1_1_,
books1_.title as title2_1_1_,
books1_.version as version3_1_1_,
books1_.author_id as author_i4_1_0__,
books1_.id as id1_1_0__
from
Author author0_
left outer join
Book books1_
The fetched associations increase the size of the result set. If Hibernate would apply pagination to this query, it would no longer return the expected result. Because of that, Hibernate has to retrieve all records of the result set and apply the pagination in memory.
You can avoid this problem by splitting your query into 2. The first one uses pagination to get a list of the records’ primary keys that match your search criteria. You can then use this list in the second query to retrieve the entities with their initialized associations.
// Get primary keys with LIMIT and OFFSET
TypedQuery<Long> idQuery = em.createQuery("SELECT a.id FROM Author a order by a.id", Long.class);
List<Long> authorIds = idQuery.setFirstResult(5)
.setMaxResults(5)
.getResultList();
// Get entities with associations
TypedQuery<Author> authorQuery = em.createQuery("SELECT DISTINCT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id in (:ids)", Author.class);
authorQuery.setParameter("ids", authorIds);
List<Author> authors = authorQuery.getResultList();
Conclusion
Pagination is a simple but important feature to limit the size of your result set to a number of records that can get efficiently processed by your application and the user. You can configure it with JPA and Hibernate by calling the setFirstResult and setMaxResults on the Query or TypedQuery interface.
When you use pagination, you need to add an ORDER BY clause, and you should try to avoid it when using JOIN FETCH clauses or an EntityGraph.
