In a previous post we learned how we can use Hibernate native SQL queries in our Grails application. We can also execute custom SQL with Groovy SQL. We must create a new instance of groovy.sql.Sql in our code to execute SQL code. The easiest way is to use a javax.sql.DataSource as a constructor argument for the groovy.sql.Sql class. In a Grails application context we already have a DataSource and we can use it to inject it into our code. We must use the name dataSource to reference the default datasource in a Grails application.
In the following sample we invoke a custom query (for Firebird) using Groovy SQL. Notice we define a property dataSource in the Grails service PersonService and Grails will automatically inject a DataSource instance.
package com.mrhaki.grails
import groovy.sql.Sql
import groovy.sql.GroovyRowResult
class PersonService {
// Reference to default datasource.
def dataSource
List<GroovyRowResult> allPersons(final String searchQuery) {
final String searchString = "%${searchQuery.toUpperCase()}%"
final String query = '''\
select id, name, email
from person
where upper(email collate UNICODE_CI_AI) like :search
'''
// Create new Groovy SQL instance with injected DataSource.
final Sql sql = new Sql(dataSource)
final results = sql.rows(query, search: searchString)
results
}
}We can even make the groovy.sql.Sql instance a Spring bean in our Grails application. Then we can inject the Sql instance in for example a Grails service. In grails-app/conf/spring/resources.groovy we define the Sql bean:
// File: grails-app/conf/spring/resources.groovy
beans = {
// Create Spring bean for Groovy SQL.
// groovySql is the name of the bean and can be used
// for injection.
groovySql(groovy.sql.Sql, ref('dataSource'))
}Now we can rewrite our previous sample and use the bean groovySql:
package com.mrhaki.grails
import groovy.sql.GroovyRowResult
class PersonService {
// Reference to groovySql defined in resources.groovy.
def groovySql
List<GroovyRowResult> allPersons(final String searchQuery) {
final String searchString = "%${searchQuery.toUpperCase()}%"
final String query = '''\
select id, name, email
from person
where upper(email collate UNICODE_CI_AI) like :search
'''
// Use groovySql bean to execute the query.
final results = groovySql.rows(query, search: searchString)
results
}
}Code written with Grails 2.3.7.
| Reference: | Grails Goodness: Using Groovy SQL from our JCG partner Hubert Ikkink at the JDriven blog. |
