LIMIT in Postgres not supported in HQL?

Question

I get an error using the following code in Grails running HQL to get the first 30 Item objects:

def items = Item.executeQuery('SELECT i FROM Item as i LIMIT 30 OFFSET 0') 

my database is Postgres. However, I received:

 org.hibernate.hql.ast.QuerySyntaxException: unexpected token: LIMIT near line 1, column ... 

Obviously, the error indicates that LIMIT is not supported by HQL. How can I make it work? In other words, how to get HQL to run native SQL that is well supported by Postgres?

Answer 1

you could do it grails / gorm using a list and

 def items = Item.list(offset:0, max:30) 

Since you are using HQL, I edited my answer based on the comment below; any approach will work

 def items = Item.executeQuery('SELECT i FROM Item as i', [max: 30, offset: 0]) 

Answer 2

 query.setMaxResults(..) 

Look at the hibernate API