GORM Tutorial

Overview

New Based on some recent project work at my awesome employer, Credera, I've spent a lot more time with GORM and have updated these recipes from my lessons learned. One of the biggest changes from the prior version is the use of where queries to simplify things. I've dropped the HQL queries from cases where the where queries make more sense.

GORM recipes demonstrates how to effectively use the GORM persistence provider in a Grails 2.0 application. GORM makes it exceptionally easy to do the simple things, but for a new user, you can quickly hit a wall when you want to do something which would be simple to do in SQL. These recipes should help you get over that wall and help you resist that urge to drop to raw SQL to do things.

The Grails 2.0 documentation should be a starting point for anyone jumping in to Grails. It is much improved over the 1.x branch and can probably answer your questions if you actually read it. The weakness is finding what you need or the right example of how a particular feature works. These recipes are an extension to the Querying with GORM section and are an attempt to help with these weaknesses.

The Git Stuff

These recipes use a simple Grails application hosted on GitHub as a testbed for the queries. The application defines two domain classes and populates them with sample data to query against. The two domain classes are in the files Artist.groovy and Work.groovy.

The domain classes have a one-to-many relationship, with the parent domain class being the Artist, and each Artist has many Works. The fields in the models are very simple: Strings, Integers and Dates. Each of these classes also defines a simple toString() method which returns the id and name or title field for the instance. This is to keep the output simple.

The project does not define any Controllers or Views. It is meant to be run from the Grails console simply to execute the GORM queries below. The BootStrap.groovy class seeds the in-memory database with the data for the queries.

To run these recipes, first clone the master branch of this Git repository. You can then run the Grails console from the root project directory in order to execute the queries. For each recipe, I'll attempt to demonstrate multiple ways of getting the same result, usually via GORM and HQL queries.

Important The Grails console does not execute the BootStrap::init() method by default. In order to run these examples, you'll need to add the following lines to the top of your code. It imports the domain classes and seeds the database with the sample data. The BootStrap::init() method tests to see if the seed data has been generated, so it can safely be run multiple times.

import gorm.recipes.*
new BootStrap().init()

Find an Artist by ID

This is the simplest query case. It uses the numeric ID column of the row to return the correct domain class. The default, implicit name of the ID column is id, but that can be changed via constraints. Numbering starts at one for the default H2 database engine. This query will always either return an instance of the domain class, or null if the ID is not valid.

import gorm.recipes.*
new BootStrap().init()

def artist = Artist.get(1)
println artist

(1: Bill Conner)

Find an Artist by Name

The syntax demonstrates both the dynamic finders in GORM as well as the corresponding GORM where syntax. Each of these will only return a single instance of the Artist, or null if the criteria doesn't match. If, for some reason, the query returns multiple rows, only the first matching row is returned. This can lead to subtle bugs. If your query can return multiple rows, use the findAll() syntax as shown in later recipe.

import gorm.recipes.*
new BootStrap().init()

def criteria = 'Lucy Sparks'

// GORM Dynamic Finder
def artist = Artist.findByName(criteria)
println artist

// GORM Where
def query = Artist.where {
    name == criteria
}

artist = query.find()

println artist

(5: Lucy Sparks)
(5: Lucy Sparks)

Note that the where query is being executed in a two-step process. First, the query is built using the where clause, then executed via the find() method. This can be simplified to a single operation, as follows, but there are benefits to the two-step process which I'll highlight below.

import gorm.recipes.*
new BootStrap().init()

def criteria = 'Lucy Sparks'

// GORM Where plus execute in one action
def artist = Artist.find {
    name == criteria
}

println artist

(5: Lucy Sparks)

Find an Artist by Style and Home

This expands on the above example to show how to use multiple columns to find the desired Artist. It also will only return a single row. The GORM findBy... syntax supports a dynamic query by adding the names of the domain class properties to the method. GORM parses the method name and builds the appropriate query. Full details on building dynamic finders are in the Grails documentation.

The where query works the same as above. We just use the the additional criteria via the boolean && logic in the query. The where query syntax can to be used to build very sophisticated queries which remain readable. Since we're using find() with the query, only one row is returned.

Important The find() query does not behave the same as the dynamic finder in cases where more than one row might be returned by the query. If you use the find() query with a criteria that would return more than one row, you will get an exception, versus the dynamic finder, which will return the first row that matches.

import gorm.recipes.*
new BootStrap().init()

def homeCriteria = 'El Paso, TX'
def styleCriteria = 'Classic'

// GORM Dynamic Finder
def artist = Artist.findByHomeAndStyle(homeCriteria,styleCriteria)
println artist

// GORM Where
def query = Artist.where {
    home == homeCriteria && style == styleCriteria
}

artist = query.find()
println artist

(10: Lucy Peters)
(10: Lucy Peters)

Find all Artists by Style

The previous examples have all been single-row queries. The results were either a single instance of Artist or null. In this example, we'll grab all the Artists with a specific style. The results of these will return a java.util.List instance. The list will be empty if no results are found, otherwise it wll contain the Artist instances that matched the criteria.

Note there is no guarantee on the order of the results since we didn't provide any sorting instructions

import gorm.recipes.*
new BootStrap().init()

def styleCriteria = 'Modern'

// GORM Dynamic Finder
def artistList = Artist.findAllByStyle(styleCriteria)
println artistList

// GORM Where
def query = Artist.where {
    style == styleCriteria
}

artistList = query.list()
println artistList

[(1: Bill Conner), (5: Lucy Sparks), (6: Richard Corsin), (9: Clint Fallow)]
[(1: Bill Conner), (5: Lucy Sparks), (6: Richard Corsin), (9: Clint Fallow)]

Grails provides simple syntax to sort the results, specify the maximum number of results or specify an offset value for use in pagination. In the below example, I'll sort the above query by name, grabbing only two rows, starting with the second result found.

import gorm.recipes.*
new BootStrap().init()

def styleCriteria = 'Modern'

// GORM Dynamic Finder
def artistList = Artist.findAllByStyle(styleCriteria, [sort: 'name', max: 2, offset: 1, order: 'asc'])
println artistList

// GORM Where
def query = Artist.where {
    style == styleCriteria
}

artistList = query.list(sort: 'name', max: 2, offset: 1, order: 'asc')
println artistList

[(9: Clint Fallow), (5: Lucy Sparks)]
[(9: Clint Fallow), (5: Lucy Sparks)]

Note the subtle difference between the options map for the two styles of queries. With the GORM dynamic finder, the sort and pagination criteria are passed in a map as the final parameter to the dynamic finder. The GORM where instead passes those values at the execution of the query.

The valid values for the sort and pagination options are:

sort : the name of the domain class property to sort on (String)
order : the sort order, either asc or desc (String)
max : the maximum number of rows to return in the query (Integer)
offset : the number of rows to skip over before starting to return the result (Integer)

The one-line version of the GORM where query would look like this. Note the use of findAll() instead of list() to get the results.

import gorm.recipes.*
new BootStrap().init()

def styleCriteria = 'Modern'

// GORM Where
def artistList = Artist.findAll(sort: 'name', max: 2, offset: 1, order: 'asc') {
    style == styleCriteria
}

println artistList

[(9: Clint Fallow), (5: Lucy Sparks)]

Find the Count of all Artists with a specific Style

This query would be solved by using the count aggregate function in SQL. The syntax for GORM dynamic finder and GORM where query are pretty similar. For the latter, we use the count() method of the query object.

import gorm.recipes.*
new BootStrap().init()

def styleCriteria = 'Modern'

// GORM Dynamic Finder
def artistCount = Artist.countByStyle('Modern')
println artistCount

// GORM Where
def query = Artist.where {
    style == styleCriteria
}

artistCount = query.count()
println artistCount

4
4

Find the Artist Styles (DISTINCT)

In SQL, this would be getting the distinct values of the style column of the Artist table. It is still relatively simple in GORM, but can be convoluted. This is a case where using HQL with GORM makes life simpler.

import gorm.recipes.*
new BootStrap().init()

// GORM Where
def query = Artist.where {}.projections { distinct 'style' }
def results = query.list()
println results

// HQL
results = Artist.executeQuery('select distinct style from Artist')
println results

[Classic, Abstract, Modern]
[Classic, Abstract, Modern]

The GORM syntax is downright ugly. In this case, we're using an empty where query and tacking a projection on to the end. This is enough to make a SQL person pull their hair out and it was the least painful syntax I could find. If anyone has a cleaner example, I'll be happy to add it.

The HQL implementation looks much cleaner and closer to what a SQL person would expect. The result is the list of Strings which are the distinct styles for the artists.

How many Works are there for each Style (GROUP BY)

This is a classic group by scenario in SQL. As you can see below, with GORM we need to drop down to using the Criteria Builder API, while the HQL syntax stays clean. The complexity comes from needing to use a property of the Artist domain class while querying against a Work.

import gorm.recipes.*
new BootStrap().init()

// GORM Criteria Builder
def criteria = Work.createCriteria()

def results = criteria.list {

    createAlias('artist','artistAlias')

    projections {
       groupProperty('artistAlias.style')
       rowCount()
    }
}

println results

// HQL
results = Work.executeQuery('select w.artist.style, count(w) from Work as w group by w.artist.style')
println results

[[Classic, 13], [Abstract, 13], [Modern, 15]]
[[Classic, 13], [Abstract, 13], [Modern, 15]]

For the GORM query, we again use a projection, but because of how we want to do the grouping, we need to make use of the createAlias method to make the properties of the Artist class available to the query of the Work class.

The HQL variant has a much lower complexity level. We can directly use the properties of the associated Artist class via normal dot notation in the query.

The values returned in the above queries are not sorted. To add sorting to the queries, we need to make some slight modifications.

import gorm.recipes.*
new BootStrap().init()

// GORM Criteria Builder
def criteria = Work.createCriteria()

def results = criteria.list {

    createAlias('artist','artistAlias')

    projections {
       groupProperty('artistAlias.style')
       rowCount('cnt')
    }

    order('cnt','desc')
}

println results

// HQL
results = Work.executeQuery('select w.artist.style, count(w) as cnt from Work as w group by w.artist.style order by cnt desc')
println results

[[Modern, 15], [Classic, 13], [Abstract, 13]]
[[Modern, 15], [Classic, 13], [Abstract, 13]]

Here we sorted by descending order of the count value. In both cases, we aliased the result of the count function to use in the sort. For the GORM syntax, the alias name was created by the parameter to the rowCount() method. We then used it in the order() method.

In the HQL version, we used the simple as syntax to create the alias and use it in the order by clause.

Find all the Work for an Artist

Given an Artist, the query returns a list of all their Work.

import gorm.recipes.*
new BootStrap().init()

// get an artist
def artistCriteria = Artist.get(1)

// GORM via entity association (hasMany)
println artistCriteria.works

// GORM Where
def query = Work.where {
    artist == artistCriteria
}

def results = query.list()
println results

[(2: Blue Moon), (3: Mars Alive), (1: Falling Star)]
[(1: Falling Star), (2: Blue Moon), (3: Mars Alive)]

Notice that while the result contents are equivalent between GORM and HQL, the format is different. The works property of the Artist will return a java.util.Set by default, and the GORM where query returns a java.util.List.

The advantage to the where query is we could sort the results, whereas to sort the results of the works property, we would need to tweak the Works class to implement the Comparable interface.

Find all the Artists with 5 or more Work

This query gets a list of all the Artist who have five or more Works. This one also specifies a sort by the Artist name.

import gorm.recipes.*
new BootStrap().init()

// GORM Where
def query = Artist.where {
 works.size() >= 5
}

def results = query.list(sort: 'name')
println results

//HQL
results = Artist.executeQuery('from Artist as a where a.works.size >= 5 order by name')
println results

[(8: Leslie Derby), (10: Lucy Peters), (6: Richard Corsin), (4: Todd Dander)]
[(8: Leslie Derby), (10: Lucy Peters), (6: Richard Corsin), (4: Todd Dander)]

The where query demonstrations some of the operations available when building the query. In this case, we compare the size of the associated collection. You can check out the where query section of the Grails documentation to see all the options available when querying against associations.

Find the Oldest Artist

GORM has multiple ways to get the result of this query. First, I'll demonstrate the GORM where query. Note the use of the <= operator instead of a == comparison. Grails doesn't like the use of straight equality check against a date in this case.

import gorm.recipes.*
new BootStrap().init()

// GORM Where
def query = Artist.where {
    birthDate <= min(birthDate)
}

def results = query.find()
println results

(4: Todd Dander)

Since I'm only expecting a single result, I can use the find() method instead of list() on the query.

import gorm.recipes.*
new BootStrap().init()

// GORM "cheat"
def results = Artist.findAll(max: 1, sort: 'birthDate')
println results

// HQL
results = Artist.executeQuery('from Artist order by birthDate', [max: 1])
println results

[(4: Todd Dander)]
[(4: Todd Dander)]

Both the GORM and HQL versions return a list with a single member. I'm taking advantage of the default ascending sort order. If I wanted to find the youngest Artist, I would need to add the order option.

Note that even though I'm only getting a single result in the GORM query due to setting the max option, I still have to use findAll() instead of find()

Find the most expensive Work for a Style

Queries the Work, but must join to the Artist to get the style. The easiest way to do this is via a GORM where query and pass in the sort options to get the desired result. Since we are setting the max paramter to 1, we can use find() instead of list().

import gorm.recipes.*
new BootStrap().init()


def styleCriteria = 'Modern'

def query = Work.where {
    artist.style == styleCriteria
}

def result = query.find(max: 1, sort: 'price', order: 'desc')
println result

(18: Diamond Storm)

If we only wanted to see the price of the most expensive work for a style, we can easily modify the above query with a projection:

import gorm.recipes.*
new BootStrap().init()


def styleCriteria = 'Modern'

def query = Work.where {
    artist.style == styleCriteria
}.projections {
    max 'price'
}

def result = query.find()
println result

Find the Average Price of a Work for a Style

By using a projection, we can make use of a GORM where query to get the desired result. Since we are expecting a single value back from the query, we can use find() with the query instead of list().

import gorm.recipes.*
new BootStrap().init()

def styleCriteria = 'Modern'

// GORM Where
def query = Work.where {
    artist.style == styleCriteria
}.projections {
    avg('price')
}

def results = query.find() as Integer
println results

The results from the GORM where query is a java.lang.Double value. We can use the as Integer suffix to cast it back to an Integer value, as seen here on the call to the find() method.

Find all Work with "moon" in the Title (LIKE)

This is a basic LIKE query which is simple in both types of GORM queries.

import gorm.recipes.*
new BootStrap().init()

def titleCriteria = '%moon%'

// GORM Dynamic Finder
def results = Work.findAllByTitleIlike(titleCriteria)
println results

// GORM Where
def query = Work.where {
    title =~ titleCriteria
}

results = query.list()
println results

[(2: Blue Moon), (22: Moon Nuts), (28: Watching the Moon), (29: Moonrise Over Shame), (39: Spin the Moon)]
[(2: Blue Moon), (22: Moon Nuts), (28: Watching the Moon), (29: Moonrise Over Shame), (39: Spin the Moon)]

GORM's dynamic finders directly support the LIKE operation as one of the keywords it will parse from the query. In this case, we use Ilike to specify we want a case-insensitive search. The criteria uses the standard SQL percent character wildcard.

For the GORM where we make use of the case-insensitive like comparision operation, =~ using the same criteria for the dynamic finder.

Observations

Grails 2.0 with GORM can be extremely powerful and extremely frustrating, especially for someone with a SQL background. Like all good things in Java, there are always multiple ways to get a job done. With the simple things in GORM, the dynamic finders offer a concise, easy-to-use way of executing common queries. Beyond the simple things, the GORM where queries offer an extremely power query syntax for handling the types of queries I would have been inclined to use SQL for instead.

If you want to see what's going with the queries, you can easily turn on the SQL tracing. Edit the development database properties in DataSource.groovy and change logSql and formatSql to true. You'll now see the generated SQL dumped to the Grails console output.

Why Me?

I'm no Grails Jedi. I like to learn new things, and I like to write. This document is the love child of those two endeavors. As I was learning Grails, I was thrilled with the simplicity of GORM but would run into scenarios which I could handle easily in SQL but left me all tangled up in GORM.

Resisting the urge to drop down to SQL, I worked through the documentation and various blogs to come up with these recipes which covered the cases I wanted to solve and knew should be possible.

If you are a Grails / GORM Jedi, and have some cleaner tips for solving some of these queries, you can contact me via my associated blog post. This post can also be used to convey any other constructive feedback or questions.