How to get all rows starting from given id , and limited by a given limit using javax.persistence?

Question

When I have 20 rows (id = 5.......id=25) I want to get 3 first rows starting from id=4 (meaning row 5, row 6 and row 7)

I thought that I can use :

  String queryStr = "FROM MyQueue x";
  Query q = DbHandler.createQuery(queryStr);
  q.setFirstResult(idToStart);
  q.setMaxResults(maxRecords);

but the setFirstResult is counting the number of the row and not the id.

Another thing was to do it using "WHERE id > 4" - but how can I promise that it will take the needed rows, and not some others like row 17...

How do you define "*3 first rows*"? Tables do not have an inherent order—if you desire a particular ordering, you must specify it (using an `ORDER BY` clause). — eggyal, Jan 11 '16 at 15:49

score 0 · Answer 1 · answered Jan 11 '16 at 15:48

In SQL, you would guarantee this using ORDER BY:

SELECT x.*
FROM MyQueue x
WHERE x.id >= 4
ORDER BY x.id
LIMIT 3;

The ORDER BY is very important if you want to guarantee that you get the next three rows, by id value; otherwise the ordering in the result set in indeterminate (it might seem like it works and then stop working, for instance). You only have 20 rows, so performance is not an issue. If you had a larger number of rows, then you would want an index on id to optimize performance.

How to get all rows starting from given id , and limited by a given limit using javax.persistence?

1 Answers1