grailshqlgrails-3.0.9

Grails HQL Named Parameters Not Working


Platform | Grails Version: 3.0.7 | Groovy Version: 2.4.4 | JVM Version: 1.8.0_51

I seem unable to use named parameters in a Grails HQL query. The following works (inside a Grails service class).

    def query = 'from Event event ' +
                'where event.eventName like ? ' +
                'or event.address.town like ?'
    def results = Event.executeQuery(query, ["%London%", "%London%"], [offset: 5, max: 5])

However, the following doesn't work:

    def query = 'from Event event ' +
                'where event.eventName like :searchEventName ' +
                'or event.address.town like :searchTownName'
    def results = Event.executeQuery(query, [searchEventName, "%London%", searchTownName, "%London%"], [offset: 5, max: 5])

The error shown is :

Class
groovy.lang.MissingPropertyException
Message
null
Caused by
No such property: searchEventName for class: com.mchq.event.SearchService

Going against what I think is correct, I then declare the properties ...

    def searchEventName, searchTownName
    def query = 'from Event event ' +
                'where event.eventName like :searchEventName ' +
                'or event.address.town like :searchTownName'
    def results = Event.executeQuery(query, [searchEventName, "%London%", searchTownName, "%London%"], [offset: 5, max: 5])

and the error reported is :

Class
java.lang.IllegalArgumentException
Message
null
Caused by
No positional parameters in query: from Event event where event.eventName like :searchEventName or event.address.town like :searchTownName

As one last test, I try the following having removed the variable declarations and the named param list.

    def query = 'from Event event ' +
                'where event.eventName like :searchEventName ' +
                'or event.address.town like :searchTownName'
    def results = Event.executeQuery(query, [offset: 5, max: 5])

And quite rightly, the message comes back as :

Class
org.hibernate.QueryException
Message
null
Caused by
Not all named parameters have been set: [searchEventName, searchTownName] [from Event event where event.eventName like :searchEventName or event.address.town like :searchTownName]

Can anybody tell me the mistake I am making? Positional parameters seem to be working just find but passing a map of named parameters in causing this strange error


Solution

  • You are making big typo as in case of named query we pass map not list. See code below:

    def query = 'from Event event ' +
                    'where event.eventName like :searchEventName ' +
                    'or event.address.town like :searchTownName'
        def results = Event.executeQuery(query, [searchEventName: "%London%", searchTownName: "%London%"], [offset: 5, max: 5])
    

    Hope it helps!