Pagination with JPA and Hibernate

By Thorben Janssen

Query

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.


Tags

Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Tools

Books and Courses

Coaching and Consulting

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}