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.
JPQL doesn’t support the LIMIT keyword. How can I use pagination with Hibernate?
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();
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.
Get it now!