Hibernate Tips: How to use pagination with JPQL
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.
Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.
Question:
JPQL doesn’t support the LIMIT keyword. How can I use pagination with Hibernate?
Solution:
With JPA and Hibernate, you have to set the pagination information on the Query interface and not in the query String as you know it from SQL. You can do that by calling the setFirstResult(int startPosition) and setMaxResults(int maxResults) methods.
The following code snippet shows a simple example that returns the first 5 Authors from the database. The result set index is 0 based and you, therefore, need to provide 0 as a startPosition to begin with the first element.
List<Author> authors = em.createQuery(“SELECT a FROM Author a ORDER BY a.id”, Author.class) .setMaxResults(5) .setFirstResult(0) .getResultList();
To select the next 5 Authors from the database, you only need to change the startPosition to 5.
List<Author> authors = em.createQuery(“SELECT a FROM Author a ORDER BY a.id”, Author.class) .setMaxResults(5) .setFirstResult(5) .getResultList();
Further reading:
If you like to learn more about JPQL, have a look at my recent post in which I explain several JPQL capabilities: Is your query too complex for JPA and Hibernate?
Hibernate Tips Book
Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.
It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.
This works for simple cases, i.e. top level queries. But when you nest selects, LIMIT starts becoming really useful in SQL, e.g. when comparing a tuple with a maximum / minimum tuple, such as:
SELECT *
FROM author
WHERE (first_name, last_name) >= (
SELECT first_name, last_name
FROM customer
ORDER BY 1 DESC, 2 DESC
LIMIT 1
)
There are many other cases where LIMIT is extremely useful in SQL and where there is not an equivalent JPQL or JPA API method to achieve the same…
Sure, you can do more complex queries with SQL. JPQL is not a replacement for it.
Your example is just one of multiple reasons to use native queries with Hibernate. There is also a free ebook about it in the Thought on Java Library.
Hi Thorben,
First of all thanks for writing these blogs, they are really helpful. Just wanted to confirm one thing about the pagination solution you have described above. Don’t we need to specify ‘order by’ in the query? Without that is it not possible that database might return same record when you are fetching next set of five records. As each query is a separate query then how are you ensuring that same record won’t be fetched again?
Thanks,
Suraj
Hi Suraj,
I just focussed on the pagination features. But you’re, of course, right. It’s better to order the query results. Otherwise, the order is undefined and it depends on the implementation details of the database if it applies an implicit ordering (e.g. by primary key) or not.
I changed the code snippets accordingly.
Thanks,
Thorben