Hibernate: get last N rows ordered by id ASC

Question

I have a table like this

id name age 1 John 54 2 Emily 68 3 Tom 235 4 Jack 68 5 Harry 12 6 Ron 68 7 ... 8 ... 9 ... 68 ... ... ... ... ... ... ... ... ... ... ... 68 ... ... ...

Right now I use this code to get people with a certain age:

CriteriaBuilder cb = session.getCriteriaBuilder(); CriteriaQuery<Person> cr = cb.createQuery(Person.class); Root<Person> root = cr.from(Person.class); cr.select(root).where(cb.equal(root.get("age"), 68)); Query<Person> query = session.createQuery(cr); List<Person> result = query.getResultList(); session.close();

It would return all people with age = 68.

I can get N first 68-age people by adding this:

query.setMaxResults(N);

However I'm not sure how to get N last?

I tried

int total = ((Long) count.uniqueResult()).intValue(); int start = total - N; query.setFirstResult((start)); query.setMaxResults(N);

But it didn't return exactly N last (N last being N last rows when the table is sorted by id ASC).

XtremeBaumer · Accepted Answer · 2019-12-17 14:36:27Z

1

Simply turn the order of the result around. You can do it by adding this:

cr.orderBy(cb.desc(root.get("id")));

and if you need it variable, pass a boolean into the method:

if (selectFirst) { cr.orderBy(cb.asc(root.get("id"))); } else { cr.orderBy(cb.desc(root.get("id"))); }

answered Dec 17, 2019 at 14:36

XtremeBaumer

6,4553 gold badges26 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

parsecer Over a year ago

But then the result list would have elements in reverse order - from last to first, wouldn't it? I'd want to get N last elements that are in the result list in the same order they are in the database

XtremeBaumer Over a year ago

@parsecer there are a few possibilities as can be seen here, but the issue is mostly that hibernate won't support it. That is why you would manually need to sort that list again. Collections.sort(result). This reuqires you to either implement the Comparable interface within Person class or implement a new Comparator<Person>(){} with Collections.sort()

parsecer Over a year ago

Well I can just use Collections.reverse(result), but I would imagine it to be quite slow for large tables.

XtremeBaumer Over a year ago

You are right, that would be easier. I doubt that you will query enough results for the reverse to be the slow part. If anything is becoming slow, then its the query itself

Collectives™ on Stack Overflow

Hibernate: get last N rows ordered by id ASC

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related