4

I am having error using the following code in Grails executing HQL to get the first 30 Item objects:

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

my backend database is Postgres. However, I got:

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

Obviously, the error tells that LIMIT is not supported by HQL. How do I make it work? In other words, how do I make HQL execute native SQL that is well supported by Postgres?

hko19
  • 1,054
  • 4
  • 16
  • 25

2 Answers2

6

you could do it the Grails/GORM way using list also

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

Since you are using HQL I have edited my answer based on comment below; either approach would work

def items = Item.executeQuery('SELECT i FROM Item as i', [max: 30, offset: 0])
Aaron Saunders
  • 33,180
  • 5
  • 60
  • 80
  • better to use [max: 30, offset: 0] map as parameter as we talking about executeQuery method. Item.executeQuery('SELECT i FROM Item as i', [max: 30, offset: 0]) – Oleksandr Nov 04 '10 at 14:29
3
query.setMaxResults(..)

Have a look at the hibernate API

cherouvim
  • 31,725
  • 15
  • 104
  • 153