How to fetsch rows with offset and count in Hibernate?

Question

I need to write a query that would return users ordered by creation date. The query has to accept offset and count, so I need to get all rows starting from the offset ending with offset + count. I can easily write a corresponding SQL query, but I am not able to convert it to HQL.

To be more clear, the corresponding SQL would look like this:

SELECT * FROM users
ORDER BY created_at
OFFSET 20 ROWS
FETCH FIRST 10 ROW ONLY;

Q: How can I implement the same in HQL (JP-QL)?

I am using Hibernate together with Quarkus and Panache.

score 1 · Accepted Answer · answered Feb 12 '21 at 14:52

Assuming that you have the following entity:

@Entity
@Table(name = "users")
public class User
{
   // ...
   @Column(name = "created_at")
   private LocalDate createdAt;
}

you can write the following query:

List<User> users = entityManager.createQuery(
   "select u from User u order by u.createdAt", User.class)
.setFirstResult( 10 )
.setMaxResults( 2 )
.getResultList();

Hibernate will generate the following sql:

select
    u.id as id1_3_,
    -- ...
    u.created_at as created_at_8_3_
from
    users u
order by
    u.created_at asc
limit ?
offset ?

How to fetsch rows with offset and count in Hibernate?

1 Answers1