How to get n rows at a time with JPQL?

Question

I need some help with JPQL. I need to transform this PostgreSQL statement into JPQL, and I have no idea how.

SELECT * FROM public.user_table 
ORDER BY id ASC 
OFFSET 5 ROWS
FETCH FIRST 5 ROW ONLY

I tried writing it in this way "SELECT u FROM user_table e ORDER BY id OFFSET 5 ROWS FETCH FIRST 5 ROW ONLY", but it doesn't work.

have you tried with native SQl command : Limit 0,5 ? (where 0 is the start point, in your case i think is 5) — Andrea_86, Feb 17 '22 at 17:05
Does this answer your question? [Specifying Result limit using JPA Specification](https://stackoverflow.com/questions/42804797/specifying-result-limit-using-jpa-specification) — pringi, Feb 18 '22 at 09:44

score 0 · Accepted Answer · answered Mar 11 '22 at 18:50

JPA is an abstraction over the database and is generalized for all databases. And we have stuffs which are database specific, like;

OFFSET 5 ROWS
FETCH FIRST 5 ROW ONLY

For info, IBM DB2 does not support these.

So JPQL, in general, will not usually support them.

I guess native query is the way or you can work with pageable; PageRequest is a good implementation of pageable.

1 Answers1