YouTube video

Hibernate Tips: How to use pagination with JPQL

By Thorben Janssen

Jpql, Query

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.

Get it now!



Tags

Jpql, 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.

Books and Courses

Coaching and Consulting

Tools

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.

  1. 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…

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

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

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