0

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

parsecer
  • 4,758
  • 13
  • 71
  • 140

1 Answers1

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")));
}
XtremeBaumer
  • 6,275
  • 3
  • 19
  • 65
  • 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 – parsecer Dec 17 '19 at 14:43
  • @parsecer there are a few possibilities as can be seen [here](https://stackoverflow.com/questions/4193705/sql-server-select-last-n-rows), 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(){}` with `Collections.sort()` – XtremeBaumer Dec 17 '19 at 14:54
  • Well I can just use `Collections.reverse(result)`, but I would imagine it to be quite slow for large tables. – parsecer Dec 17 '19 at 14:59
  • 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 – XtremeBaumer Dec 17 '19 at 15:02