Grails Goodness: Using Groovy SQL

Hubert IkkinkMarch 24th, 2014Last Updated: March 23rd, 2014

0 43 1 minute read

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
    }
  
}