Hibernate: how to select all rows but the last N rows?

Question

I am trying to run a Hibernate query that returns all the rows except the last 5 five rows, so my sql query is as follow:

session.createQuery("FROM Book 
WHERE id NOT IN(SELECT id FROM Book ORDER BY ID DESC LIMIT 5) 
ORDER BY title");

Unfortunately, the LIMIT not allowed in Hibernate, so I got an error saying:

HTTP Status 500 - org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LIMIT near line 1, column 78 [FROM models.Book WHERE id NOT IN(SELECT id FROM models.Book ORDER BY ID DESC LIMIT 5) ORDER BY title]

How I can fix this?

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

There is an answer here

How do you do a limit query in HQL?

Limit was never a supported clause in HQL. You are meant to use setMaxResults().

edited May 23 '17 at 12:23

Community

1
1

answered Oct 04 '16 at 13:44

Ash

2,562
11
31

I know that, but I can't use this method in my case, only if I looped the `LIMITED` results and then do the selection. I need something perform better :) – Mohammad Oct 04 '16 at 13:47

score 1 · Answer 2 · answered Oct 04 '16 at 13:49

1

Seems like you are asking hibernate pagination

First get count:

String countQ = "Select count (f.id) from Foo f";
Query countQuery = session.createQuery(countQ);
Long countResults = (Long) countQuery.uniqueResult();

Then calculate your limit start and end points:

Long start = countResult - limit;

Then use pagination future:

Session session = sessionFactory.openSession();
Query query = sess.createQuery("From Foo");
query.setFirstResult(start );
query.setMaxResults(limit);
List<Foo> fooList = fooList = query.list();

You might have to update queries but I believe you get the idea

answered Oct 04 '16 at 13:49

HRgiger

2,750
26
37

`setFirstResult` doesn't accept `Long`, only `'int` – parsecer Dec 17 '19 at 14:00
And this method doesn't return the correct (ordered by id) order – parsecer Dec 17 '19 at 14:05

score 0 · Answer 3 · edited May 23 '17 at 12:00

I have also asked similar kind of questions related to the limitation of "LIMIT" keyword in hibernate.

Hibernate: how to select last N rows in ascending order of id? (in single query)

I think there are only two ways left, though it is not the best performance.

Two queries (get the id list, then query with NOT IN id list)
One query with further data processing (eg. chop out last 5 data from a list of result)

hope it helps

Hibernate: how to select all rows but the last N rows?

3 Answers3