Leanpub: Publish Early, Publish Often

Grails Object Relational Mapping (GORM)

Getting First or Last Instance of Domain Classes

Since Grails 2 we can get the first or last instance of a domain class with the first() and last() method on domain classes. We can optionally use an argument to set the column name to sort by. If we don’t specify a column name the id column is used.

import com.mrhaki.grails.Course

createCourse 'Grails University Getting Started', Date.parse('yyyy-MM-dd', '2013-11-01')
createCourse 'Grails University RESTful Applications'
createCourse 'Spock Sessions', Date.parse('yyyy-MM-dd', '2013-09-01')

void createCourse(final String name, final Date start = null) {
    new Course(name: name, start: start).save()
}

assert Course.first().name == 'Grails University Getting Started'
assert Course.first().start.format('yyyy-MM-dd') == '2013-11-01'

assert Course.last().name == 'Spock Sessions'
assert Course.last('name').name == 'Spock Sessions'

assert Course.last('start').name == 'Grails University Getting Started'
assert Course.first(sort: 'start').start == null

Code written with Grails 2.2.4.

Original blog post written on September 09, 2013.

Using Hibernate Native SQL Queries

Sometimes we want to use Hibernate native SQL in our code. For example we might need to invoke a selectable stored procedure, we cannot invoke in another way. To invoke a native SQL query we use the method createSQLQuery() which is available from the Hibernate session object. In our Grails code we must then first get access to the current Hibernate session. Luckily we only have to inject the sessionFactory bean in our Grails service or controller. To get the current session we invoke the getCurrentSession() method and we are ready to execute a native SQL query. The query itself is defined as a String value and we can use placeholders for variables, just like with other Hibernate queries.

In the following sample we create a new Grails service and use a Hibernate native SQL query to execute a selectable stored procedure with the name organisation_breadcrumbs. This stored procedure takes one argument startId and will return a list of results with an id, name and level column.

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

import com.mrhaki.grails.Organisation

class OrganisationService {

    // Auto inject SessionFactory we can use
    // to get the current Hibernate session.
    def sessionFactory

    List<Organisation> breadcrumbs(final Long startOrganisationId) {

        // Get the current Hiberante session.
        final session = sessionFactory.currentSession

        // Query string with :startId as parameter placeholder.
        final String query = '''\
        select id, name, level 
        from organisation_breadcrumbs(:startId) 
        order by level desc'''

        // Create native SQL query.
        final sqlQuery = session.createSQLQuery(query)

        // Use Groovy with() method to invoke multiple methods
        // on the sqlQuery object.
        final results = sqlQuery.with {
            // Set domain class as entity. 
            // Properties in domain class id, name, level will
            // be automatically filled.
            addEntity(Organisation)

            // Set value for parameter startId.
            setLong('startId', startOrganisationId)

            // Get all results.
            list()
        }

        results
    }

}

In the sample code we use the addEntity() method to map the query results to the domain class Organisation. To transform the results from a query to other objects we can use the setResultTransformer() method. Hibernate (and therefore Grails if we use the Hibernate plugin) already has a set of transformers we can use. For example with the org.hibernate.transform.AliasToEntityMapResultTransformer each result row is transformed into a Map where the column aliases are the keys of the map.

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

import org.hibernate.transform.AliasToEntityMapResultTransformer

class OrganisationService {

    def sessionFactory

    List<Map<String,Object>> breadcrumbs(final Long startOrganisationId) {
        final session = sessionFactory.currentSession

        final String query = '''\
        select id, name, level 
        from organisation_breadcrumbs(:startId) 
        order by level desc'''

        final sqlQuery = session.createSQLQuery(query)

        final results = sqlQuery.with {
            // Assign result transformer.
            // This transformer will map columns to keys in a map for each row.
            resultTransformer = AliasToEntityMapResultTransformer.INSTANCE             

            setLong('startId', startOrganisationId)

            list()
        }

        results
    }

}

Finally we can execute a native SQL query and handle the raw results ourselves using the Groovy Collection API enhancements. The result of the list() method is a List of Object[] objects. In the following sample we use Groovy syntax to handle the results:

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

class OrganisationService {

    def sessionFactory

    List<Map<String,String>> breadcrumbs(final Long startOrganisationId) {
        final session = sessionFactory.currentSession

        final String query = '''\
        select id, name, level 
        from organisation_breadcrumbs(:startId) 
        order by level desc'''

        final sqlQuery = session.createSQLQuery(query)

        final queryResults = sqlQuery.with {
            setLong('startId', startOrganisationId)
            list()
        }

        // Transform resulting rows to a map with key organisationName.
        final results = queryResults.collect { resultRow -&gt
            [organisationName: resultRow[1]]
        }

        // Or to only get a list of names.
        //final List<String> names = queryResults.collect { it[1] }

        results
    }

}

Code written with Grails 2.3.7.

Original blog post written on March 18, 2014.

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.

Original blog post written on March 19, 2014.

Refactoring Criteria Contents

Grails adds a Hibernate criteria builder DSL so we can create criteria’s using a builder syntax. When we have criteria with a lot of restrictions or conditional code inside the builder we can refactor this easily. We look at a simple criteria to show this principle, but it can be applied to more complex criteria’s.

// Original criteria builder.
def list = Domain.withCriteria {
    ilike 'title', 'Groovy%'
    le 'postedDate', new Date()
}

// Refactor
def list2 = Domain.withCriteria {
    title delegate, 'Groovy%'
    alreadyPosted delegate
}

private void title(builder, String query) {
    builder.ilike 'title', query
}

private void alreadyPosted(builder) {
    builder.le 'postedDate', new Date()
}

Code written with Grails 1.2.2.

Original blog post written on June 18, 2010.