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?

Answer 1

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.

Answer 2

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)