3

I found it really cool that in Groovy SQL it is possible to do this:

def resultList = Domain.executeQuery('select * from languages where name = :name', [name: 'Groovy'])

I am trying to get the HQL "in" keyword to work with a dynamic list used as the argument, not a fixed list.

I also know that you can use the "?" operator for your SQL parameters instead of using named parameters. However according to this question:

Doing an "IN" query with Hibernate

The "in" keyword only supports named and positional parameters, not the "?" parameter. The problem is, when I try to do something like this:

def paramMap = [:]
paramMap.put("name", "groovy")

   def resultList = Domain.executeQuery('select * from languages where name = :name', paramMap)

I get an error saying that I have not specified the parameter name. So it is acting as if I have not actually specified the parameter "name". I have read the Groovy documentation on named parameters here:

http://groovy.codehaus.org/api/groovy/sql/Sql.html

But all of the examples using named parameters use a predefined parameter list. I have searched and have not found an example of using a dynamic parameter list map. How can I pass a dynamic map of parameters into Groovy named SQL query? If that is not possible, how can I use the "in" keyword with a dynamic list?

To be clear, I am constructing a dynamic SQL query and I would like to be able to add parameters to a map or list as I go along. I was previously using the "?" operator until I had to use the "in" clause, which does not work with "?".

Community
  • 1
  • 1
Jack Harkness
  • 810
  • 7
  • 10
  • this syntax should work as you have put it down. are you sure, you have not e.g. changed the name into e.g. ``:names`` and forgot about the key in the map? and on a side note: you should rely on the (GORM|http://grails.org/doc/latest/guide/GORM.html) docs instead of groovy sql ones. – cfrick Jun 10 '14 at 15:08

1 Answers1

0

Why not try find all with closures? 

def paramMap = [:]
paramMap.put("name", "groovy")
def resultList = Domain.findAll {
    paramMap.containsValue(name)
}

Here's the docs, hopefully it helps! http://grails.org/doc/latest/ref/Domain%20Classes/findAll.html

janDro
  • 1,426
  • 2
  • 11
  • 24