How to set a limit to inner query in Hibernate?

Question

I have HQL like this:

from Table1 t1 where t1.name not in (select t2.name from Table2 t2 order by t2.date limit 10)

The problem is it doesn't understand limit keyword. Is there a way to run such query without splitting it into two subqueries?

score 12 · Accepted Answer · edited May 23 '17 at 12:01

12

look at How do you do a limit query in HQL?

you can't limit a query written in hql with hql. You need to make a call to setMaxResults on the Query object, which i guess will prevent you from applying a limit on a hql subquery.

This leave you with the option of

writting it as a sql-query or
trying to find another way to write your hql query so that you don't need a limit in a subquery.

edited May 23 '17 at 12:01

Community

1
1

answered Apr 29 '10 at 16:18

Thierry

5,270
33
39

7

There is a third option. Execute 2 separate HQL queries where the results of 1st will be fed into the 2nd using Java. – cherouvim Jun 30 '11 at 12:38
5

cherouvim's solution will lead to atomicity problems. – auspicacious Jul 18 '13 at 15:34

flyinaway · Answer 2 · 2015-11-19T13:59:30.700

1

If you submit the query as an SQLQuery and then add your class as an entity, you can use limit, as the query is submittet as sql. You have to use sql Syntax though.

String sql = "select * from Supplier limit 1";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Supplier.class);
List results = query.list();

--> (also working with subselects)

edited Nov 19 '15 at 13:59

answered Nov 19 '15 at 13:51

flyinaway

11
3

How to set a limit to inner query in Hibernate?

2 Answers2

Linked