2

Hibernate version: 5.2

I am trying to use subqueries to do, and use setMaxResults(int).

session.createQuery(
"FROM ( SELECT * FROM tickets ORDER BY id DESC limit 3) sub ORDER BY id ASC"
);

However, HQL subqueries can occur only in the select or where clauses, and limit can't be used in hibernate.

How can I do it in hibernate?

Update - To make it clear

For eg, there are 10 data entries from id=1 to id=10.

I want to select last 3 data in ascending order of id by only one query + without further data processing.

The result from db would be id=8 to id=10

Thank You.

Pika
  • 507
  • 1
  • 6
  • 16
  • 1
    Possible duplicate of [Hibernate Select Top and Bottom n Rows with Criteria](http://stackoverflow.com/questions/6887643/hibernate-select-top-and-bottom-n-rows-with-criteria) – nbrooks Oct 18 '16 at 04:20
  • Thank you for your reply. That post just get last bottom n rows but not in ascending order of id. And I don't want two queries and then mix it. I want one single query to get data from db without processing the data. – Pika Oct 18 '16 at 04:29

2 Answers2

1

Assuming you have the ids 1 to 10 and you want the last N=3.

Taken your approach

SELECT * FROM tickets ORDER BY id DESC

would return the ids in order from 10 to 1. You then want to get the last N=3 in ascending order. Which means you want the ids 3 to 1 in ascending order.

What would be wrong with selecting the first N ids in ascending order?

session.createQuery(
    "FROM tickets ORDER BY id ASC"
).setMaxResults(n);
SubOptimal
  • 22,518
  • 3
  • 53
  • 69
  • Thank You for your reply. This is not what I mean. I have updated the question to make it clear. – Pika Oct 18 '16 at 06:14
0

You can use Query setFirstResult(int startPosition), Query setMaxResults(int maxResult) to implement it. Typically used in the pagination.

Opportunistic, you can get the first 3 record descendingly here, so you can only use the Query setMaxResults(int maxResult).

Liping Huang
  • 4,378
  • 4
  • 29
  • 46
  • I think it only works on constant database. for eg, QUERY.setFirstResult(6).setMaxResults(3). Did I get you wrong?In case of dynamic database, is it possible to get int startPosition without an extra query? – Pika Oct 18 '16 at 06:47