Groovy

Grails Goodness: Using Groovy SQL

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.

Hubert Ikkink

My name is Hubert A. Klein Ikkink also known as mrhaki. I work at the great IT company JDriven. Here I work on projects with Groovy & Grails, Gradle and Spring. At JDriven we focus on SpringSource technologies. All colleagues want to learn new technologies, support craftmanship and are very eager to learn. This is truly a great environment to work in.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button