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).

Answer 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")));
}