How and When to Use Various GORM Querying Options

Grails Object Relational Mapper (GORM) is a really nice ORM. What started as a Groovy DSL on top of Hibernate baked into Grails, has evolved into a persistence agnostic, Grails-independent library for working with databases.

GORM uses good API design and some Groovy magic to be both novice and expert friendly. It does this via five increasingly powerful data querying mechanisms:

1. Dynamic finders
2. Where clauses
3. Criteria
4. HQL
5. SQL

In this post I’ll cover how each mechanism works and, perhaps even more importantly, when to use each one. But first, a disclaimer: information presented below is based on GORM documentation and confirmed by my own experiments. It is current as of Grails version 2.3.7. If I missed or misrepresented anything, please let me know.

With that out of the way, here’s summary of when each method should be used:

The Setup

1. Define Domain Model

Before we can get into GORM, we’ll need to define a few domain objects to work with:

Here, we have a Company and a Store. Company manufactures Products, which are sold at Stores. Details of each sale (which Product got sold and which Store sold it) are recorded in a Transaction. Here’s the code:

class Company {
    String name
    String location
}

class Store {
    String name
    String city
    String state
}

class Product {
    String name
    Company manufacturer
    BigDecimal salesPrice
}

class Transaction {
    Product product
    Store store
    Date salesDate
    Integer quantity
}

2. Change GORM Defaults (optional)

This is definitely a matter of personal preference, but I dislike that (1) GORM assumes all properties to be non-nullable by default and (2) it fails silently on error. I also find it useful to see the SQL generated by Hibernate while I’m developing and testing. So, I’ll make the following tweaks to Config.groovy and DataSource.groovy:

// *** in Config.groovy ***
// 1. make all properties nullable by default
grails.gorm.default.constraints = {
    '*'(nullable: true)
}

// 2. turn off silent GORM errors
grails.gorm.failOnError = true


// *** in DataSource.groovy ***
// 3. enable logging of Hibernate's SQL queries
test {
    dataSource {
        logSql = true
        // .... other settings
    }
}

development {
    dataSource {
        logSql = true
        // .... other settings
    }
}

Ok, we’re finally ready to query some data.

1. Dynamic Finders

The simplest way of querying in GORM is by using dynamic finders. Dynamic finders are methods on a domain object that start with findBy, findAllBy, and countBy. For example, we can use dynamic finders to get a list of products filtered in different ways:

Company ACME = Company.findByName('ACME')
Product.findAllByManufacturer(ACME)
Product.findAllByManufacturerAndSalesPriceBetween(ACME, 200, 500)

We can also get counts:

Product.countByManufacturer(ACME)

The interesting thing about dynamic finders methods is that they don’t actually exist on the domain object. Instead, GORM uses Groovy’s Meta Object Programming (MOP) hooks to intercept calls to them and construct queries on the fly.

Another thing to note about dynamic finders is that they’re lazily loaded by default. This can be changed by specifying how specific objects should be fetched:

Product fluxCapacitor = Product.findByName('flux capacitor')
Transaction.findAllByProduct(fluxCapacitor, [fetch: [product: 'eager', store: 'eager']])

2. Where Clauses

Originally introduced in Grails 2.0, the where clause gives us another simple option for querying data. Here’s how the examples above can be done using it:

// Product.findAllByManufacturer(ACME)
Product.where {
    manufacturer == ACME
}.list()

// Product.findAllByManufacturerAndSalesPriceBetween(ACME, 200, 500)
Product.where {
    manufacturer == ACME && (salesPrice > 200 && salesPrice < 800)
}.list()

// Product.countByManufacturer(ACME)
Product.where {
    manufacturer == ACME
}.count()

Complex filters

Although the where clause can do the same stuff as dynamic finders, it’s definitely more powerful. For instance, you can define more complex filter conditions.

Imagine that you want to get a list of all sales with either 1 item sold or sales of a specific product over a specific date range. Doing that with a dynamic finder could look like findAllByQuantityOrProductAndSalesDateBetween, but this doesn’t actually work. Instead, we’ll use a where clause:

Transaction.where {
    quantity == 1 || 
    (product == fluxCapacitor && 
        (salesDate >= '1/1/2014' && salesDate <= '1/10/2014')
    )
}.list()

Querying Associations

Another place where the where clause helps is when querying associations. For example, to get the list of transactions for a specific manufacturer we can do this:

Transaction.where {
    product.manufacturer.name == 'ACME'
}.list()

Note that product.manufacturer references an associated object. The query above will result in the following SQL joins:

FROM transaction this_ 
  INNER JOIN product product_al1_ ON this_.product_id = product_al1_.id
  INNER JOIN company manufactur2_ ON product_al1_.manufacturer_id = manufactur2_.id
WHERE manufactur2_.name = ?

Property Comparisons and Subqueries

There are two other use cases where the where clause can be useful: property comparison and subqueries:

// find stores named after the city they're located in
Store.where {
    name == city
}.list()

// find the largest sales of the flux capacitor 
Transaction.where {
    quantity == max(quantity) && product == fluxCapacitor
}.list()

I should note that subqueries for the where clause are limited to projections (i.e. aggregates like min, max, or avg).

3. Criteria

The two methods we’ve covered so far are certainly straight-forward, but can be limiting. For example, imagine that you wanted to get a list of all products and the stores they were sold in for a given manufacturer.

To do this efficiently, you’d want all product and store information to be retrieved in one shot (eagerly). Unfortunately, where clauses don’t (yet) allow you to specify which objects should be eagerly fetched. Fortunately, there is a way to do just this by using Criteria:

Transaction.createCriteria().list {
    fetchMode 'product', FetchMode.JOIN
    fetchMode 'store', FetchMode.JOIN

    product {
        manufacturer {
            eq 'id', ACME.id
        }
    }
}

In this example we’re using fetchMode of JOIN to indicate that both product and store properties should be eagerly retrieved. We’re also using a nested condition to get at the right manufacturer.

Keep in mind that GORM’s Criteria is actually a DSL for Hibernate’s criteria builder. Therefore, it allows you to build up quite sophisticated queries.

Projections

Aside from eager joins, Criteria can also be useful for projections. Projections are a way to further shape a data set and are typically used for aggregate functions like sum(), count(), and average().

For example, here’s a projection that gets product quantities sold for a given manufacturer:

Transaction.createCriteria().list {
    projections {
        groupProperty 'product'
        sum 'quantity'
    }

    product {
        manufacturer {
            eq 'id', ACME.id
        }
    }
}

Note that we’re creating a projections clause and specifying both the aggregate (sum) and the grouping (via groupProperty).

4. HQL

Dynamic finders, where clauses, and criteria give us a lot of power over how and what we can query. However, there are a few situations where we need even more power and that’s where HQL comes in.

But before we talk about HQL and its use cases, there is one important thing to realize. If you’re using the first 3 ways of querying, GORM will always give you strongly typed domain objects (unless you’re using counts or projections). This is not necessarily true if you’re using HQL.

Find() and FindAll()

GORM gives you two ways of using HQL. The first is to use it in combination with find() or findAll() methods of the domain object. If you’re using it this way, you’re essentially limited to specifying the WHERE clause. For example:

Transaction.findAll('from Transaction as t where t.product.manufacturer.id = :companyId', [companyId: 1])

Here, we’re asking for all transactions for a given manufacturer. Note that, like the other methods we’ve discussed so far, using HQL this way still allows GORM to give you back domain objects.

On a separate note, this example uses named maps ([companyId: 1]) to pass in query parameters. Although you can also use positional maps, I definitely prefer named maps because they’re explicit and you can use the same parameter multiple times in your query without having to specify it multiple times.

ExecuteQuery()

Up to now, every querying method we’ve used returned strongly typed domain objects. This is great, but sometimes you need something different. That’s where executeQuery() comes in.

GORM allows you to execute arbitrary HQL using executeQuery(). For example, here’s a query that returns names of the Store, the Product, and the Manufacturer sold over a given time period:

String query = $/
    select
    s.name,
    m.name,
    p.name
    from Transaction as t
    inner join t.product as p
    inner join t.store as s
    inner join p.manufacturer as m
    where t.product.manufacturer.id = :companyId
    and t.salesDate between :startDate and :endDate
/$

List queryResults = Transaction.executeQuery(query,
        [companyId: ACME.id, startDate: new Date('1/1/2014'), endDate: new Date('1/31/2014')]
)

What’s really noteworthy here is that we can shape the return set however we want. Obviously this makes it impossible for GORM to give us the right domain objects, but in certain instances the tradeoff is justified.

Another point I should make is that the data set returned by this query is a List of Arrays. To make it more useful, we could post-process it and convert it to a List of Maps with named properties:

Transaction.executeQuery(query,
        [companyId: ACME.id, startDate: new Date('1/1/2014'), endDate: new Date('1/31/2014')]
).collect {
    [
            storeName: it[0],
            manufacturerName: it[1],
            productName: it[2]
    ]
}

The output of this query can, for instance, be easily serialized into JSON and rendered as a response from a controller.

5. SQL

For better or worse, quite a few devs believe that using an ORM means never having to look at SQL. While this may be true for a large majority of queries, certain situations require it.

Consider a query which wants to compare sales of all products for a given manufacturer to a similar time period last year. This query requires joining the Transaction table to itself (over different time ranges).

HQL joins (including self-joins) are possible if there’s an association defined between objects. In other words, we’d need to modify our Transaction class like this:

class Transaction {
    Product product
    Store store
    Date salesDate
    Integer quantity

    Transaction baseline
}

If we did that, we could then define the following HQL query:

String query = $/
    select
    t1.product.name,
    sum(t1.quantity),
    sum(t2.quantity)
    from Transaction as t1
    inner join t1.baseline as t2
    where t1.product.manufacturer.id = :companyId
    and t1.salesDate between :startDate and :endDate
    and t2.salesDate between :baselineStartDate and :baselineEndDate
    group by t1.product.name
/$

Now, while this is possible to do, I find the solution distasteful. After all, doing this forces us to pollute the domain object with almost arbitrary associations just to make the query work.

The other option is to use native SQL:

String query = $/
SELECT p.name,
sum(t1.quantity),
sum(t2.quantity)
FROM transaction t1
LEFT OUTER JOIN transaction t2 ON t1.product_id = t2.product_id
INNER JOIN product p ON t1.product_id = p.id
WHERE p.manufacturer_id = :companyId
AND t1.sales_date between :startDate and :endDate
AND t2.sales_date between :baselineStartDate and :baselineEndDate
GROUP BY p.name
/$

new Transaction()
.domainClass
.grailsApplication
.mainContext
.sessionFactory
.currentSession
.createSQLQuery(query)
        .setLong('companyId', 1)
        .setDate('startDate', new Date('1/1/2014'))
        .setDate('endDate', new Date('1/31/2014'))
        .setDate('baselineStartDate', new Date('1/1/2013'))
        .setDate('baselineEndDate', new Date('1/31/2013'))
        .list()

There are a couple of things to note here. First, in order to execute this query we need to get a hold of Hibernate’s current session and call its createSQLQuery() method. The two ways to do is are (1) get sessionFactory injected into our class by Grails or (2) new up the domain class inside of the method and walk a long chain of dependencies to get it.

I’m using option 2 here because I put the method which implements this query inside my domain class and I wanted to keep it static:

class Transaction {
   ...
   static List

If you were putting this method somewhere other than your domain class (like controller or service), I would recommend using option 1.

The other thing I want to point out is that because we’re using the actual Hibernate method, we cannot pass it a map of parameters. Instead, we have to use Hibernate’s strongly types set*() methods.

Database Specific SQL

Aside from complex-yet-still-generic SQL queries, we sometimes need to take advantage of database specific constraints. For example, Postgres allows storing data as arrays, maps (hstore), or JSON. Certain types of queries using these data types are difficult, if not impossible, to write using HQL.

Performance Tuning

There’s one other reason to use native SQL from GORM: performance tuning. Though Hibernate is typically pretty good about how it creates the necessary SQL, it’s definitely not perfect. So, there are rare instances where hand-tuned SQL can give you a significant performance boost.

Final Thought

Querying options supported by GORM are all appropriate under the right circumstances. I personally try to use the simplest option wherever possible (less code to test and maintain). On the other hand, if the unthinkable happens and either HQL (or SQL) is required, it’s good to understand how to make it work.

You may also like:

• How To Navigate JSON Trees in Postgres using Recursive CTEs
• Postgres Query Plan Visualization
• XML Databases
• Good Devs Don’t Like Magic
• Database Source Control Revisited

• database
• GORM
• Grails
• Hibernate

Did you love / hate / were unmoved by this post?
Then show your support / disgust / indifference by following me on Twitter!