GORM Recipes

GORM Recipes

Tastes like chicken, acts like SQL

Project Setup getting the code

Overview

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.

Project Classes

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()

Basic Queries the easy selects

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 HQL 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.

The HQL version of the query makes use of named parameter substitution, which is the safest way to use parameters with HQL queries. 

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

// GORM
def artist = Artist.findByName('Lucy Sparks')
println artist

// HQL
artist = Artist.find('from Artist where name = :name',[name: 'Lucy Sparks'])
println artist


(5: Lucy Sparks)
(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. 

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

// GORM
def artist = Artist.findByHomeAndStyle('El Paso, TX','Classic')
println artist

// HQL
artist = Artist.find('from Artist where home = :home and style = :style',
    [home: 'El Paso, TX', style: 'Classic'])
println artist

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

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. 

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

// GORM
def artist = Artist.findAllByStyle('Modern')
println artist

// HQL
artist = Artist.findAll('from Artist where style = :style',[style: 'Modern'])
println artist

[(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()

// GORM
def artist = Artist.findAllByStyle('Modern',
    [sort: 'name', max: 2, offset: 1, order: 'asc'])
println artist

// HQL
artist = Artist.findAll('from Artist where style = :style order by name asc',
    [style: 'Modern'],
    [max: 2, offset: 1])
println artist

[(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 style, the sort key contains the name of the domain class property to sort by. The HQL syntax follows a more SQL-like convention where you need to add an order by clause to the end of the query. The HQL query will ignore the sort and order keys in the options map.

The valid values for the options map 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)

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 and HQL are pretty similar. 

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

// GORM
def artist = Artist.countByStyle('Modern')
println artist

// HQL
artist = Artist.executeQuery('select count(*) from Artist where style = :style',
    [style: 'Modern'])
println artist

4
[4]

Notice There is a huge difference in the output of these two queries. In the GORM query, the result is a simple Long numeric value of the number of matching rows, or zero if no rows match. For the HQL query, a call to executeQuery will always return a java.util.List instance. In this case, the list will contain a single value, which is the Long count of the number of matching rows. So for the HQL version, you need to either index into the result or call the first() method on the list to get the numeric value.

Advanced Queries using aggregate functions and other complexity

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. 

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

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

// 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, the where method is building a detached query, and we're 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.

Note the GORM query doesn't directly return a java.util.List. The results variables contains a instance of a grails.gorm.DetachedCriteria which gets executed when we call the list() method, which returns the actual list we want.

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, the GORM syntax gets downright convoluted, 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
def c = Work.createCriteria()

def results = c.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
def c = Work.createCriteria()

def results = c.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()

// GORM

def artist = Artist.get(1)
println artist.works

// HQL
def results = Work.executeQuery('select w from Work as w where w.artist = :artist', [artist: artist])
println results

[(3: Mars Alive), (1: Falling Star), (2: Blue Moon)]
[(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 HQL version returns a java.util.List.

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
def results = Artist.findAll(sort: 'name') {
 works.size() >= 5
}
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)]

Find the Oldest Artist

GORM has multiple ways to get the result of this query. First, I'll demonstrate the convoluted way. 

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

// GORM
def c = Artist.createCriteria()

def results = c.get {
   eq "birthDate", { projections { min "birthDate" } }
}

println results

(4: Todd Dander)

In this example, I use the Criteria Builder to create a query which matches the birthDate to the min birthDate from a sub-query. The get method of Criteria Builder returns a single instance. I could also use the list method, which would return a java.util.List of results. In this case, it would be a list with one element.

This works, but it's not the friendliest way to get the desired result. Here's a cleaner version, including HQL. 

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

// GORM
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 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. 

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

// GORM
def results = Work.findAll(max: 1, sort: 'price', order: 'desc') {
   artist.style == 'Modern'
}
println results

// HQL
results = Artist.executeQuery('from Work as w where w.artist.style = :style order by price desc',
    [style: 'Modern'],[max: 1])
println results

[(18: Diamond Storm)]
[(18: Diamond Storm)]

Notice I'm using a String literal in the GORM version of the query. It is possible to use a variable, but there is a catch. Below is an example of what you expect works (but doesn't) and what will work. 

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

def style = 'Modern'

// WON'T WORK
def results = Work.findAll(max: 1, sort: 'price', order: 'desc') {
   artist.style == style
}

// WORKS
def results = Work.findAll(max: 1, sort: 'price', order: 'desc') {
   artist.style == "$style"
}

println results

[(18: Diamond Storm)]

The first attempt will actually throw an org.hibernate.QueryException, so you'll need to comment it out to try the second one. Using a GString for the parameter substitution will work.

Find the Average Price of a Work for a Style

The GORM version requires using the Criteria Builder API again, as we need to use projections for the aggregate function. We also need to use the createAlias() function to get access to the properties of the Artist object from the Work. 

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

// GORM
def c = Work.createCriteria()
def results = c.get {

    createAlias('artist','artistAlias')
    eq('artistAlias.style', 'Modern')

    projections {
        avg('price')
    }
}
println results

// HQL
results = Work.executeQuery('select avg(price) from Work where artist.style = :style',
    [style: 'Modern'])
println results

1034.6666666666667
[1034.6666666666667]

The results from the GORM query is a java.lang.Double value and the HQL version returned a java.util.List with the Double value as a single member. GORM did this because we used the get method with the criteria. If we used list instead, the result would be the same as the HQL query.

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

This is a basic LIKE query which is simple in both GORM and HQL, with one catch. The queries looks like this. 

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

// GORM
def results = Work.findAllByTitleIlike('%moon%')
println results

// HQL
results = Work.executeQuery('from Work where lower(title) like lower(:title)', [title: '%moon%'])
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. 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 HQL version, there isn't a way of specifying a case-insensitive search with the executeQuery syntax, so I'm using the lower() function in HQL to convert both the criteria and the column value to lowercase for the comparison.

Summary lessons learned and other observations

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. But as soon as things get more complicated, I found it is often easier to drop down and use the HQL syntax. This is especially true for aggregate functions.

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.