Grails Goodness: Using Groovy SQL
Grails Goodness: Using Groovy SQL
Join the DZone community and get the full member experience.
Join For FreeDownload "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.
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 servicePersonService
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.
Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.
Published at DZone with permission of Hubert Klein Ikkink , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}