Learn how to implement the equivalent of a SQL WHERE clause using an HQL query, criteria query, where query, and dynamic finder. You'll save time by learning GORM using the SQL concepts you already know.

GORM for SQLaddicts

Welcome to the GORM for SQLaddicts series. A collection of articles dedicated to experienced SQL programmers who want to learn how to query GORM, the Grails object-relational mapper.

Throughout this series you will learn how to

  1. WHERE - Add restrictions to a query
  2. FROM - Work with associations and joins
  3. SELECT - Return multiple properties and/or domain classes

Getting started

To follow along you'll need Grails 3.0.5 and Mercurial. With Grails and Mercurial installed you can download the source code and run the Grails console.

Setup

1
2
3
$ hg clone ssh://hg@bitbucket.org/erosa/gormtut
$ cd gormtut 
$ grails console

Once the Grails console window appears, copy & paste the following code into the Grails console.

1
2
3
4
5
6
7
import com.emmanuelrosa.gormtut.*

Vendor.withNewSession { session ->
    // Run GORM queries within this Closure.
    
    
}

Now you're set up to execute GORM queries. All you need to do is write the query within the Closure and run the code (Script->Run or Command-R).

Domain classes

The examples in this article reference the Product, Vendor, and Address domain classes. See the diagram below to see how the domain classes are related to each other.

Domain class diagram

You'll begin with HQL due to it's similarity to SQL. From there, you'll learn about criteria queries, where queries, and finally dynamic finders. Let's get started.

HQL

HQL is a natural way to begin learning GORM queries simply because it makes the most of the SQL you already know. The significant differences in HQL are noticeable in the GORM for SQLaddicts: SELECT clause and GORM for SQLAddicts: FROM Clause articles, but there are still some worth mentioning now.

Anatomy

The table below compares a SQL query to it's HQL equivalent. Take a look and see how many differences you can identify.

SQL HQL
SELECT *
FROM product FROM Product
WHERE name = '12 in teddy bear' WHERE name = '12 in teddy bear'

There are a whopping 2 differences: the Product in the from clause is case-sensitive and the select clause is missing.

Case sensitivity

An important difference between SQL and HQL is that in SQL you select from tables while in HQL you select from domain classes. Because you're dealing with classes names rather than tables names, the names are case-sensitive.

Columns vs Objects

So... how do I select properties?

You

The select clause, which is covered in the select clause article, can be used to return rows of domain class properties. The important thing to know for now is that while SQL queries return rows of columns as you're accustomed to, without a select clause HQL queries return domain class instances instead. Allow me to demonstrate.

SQL

Take the following SQL query.

SELECT *
FROM product
WHERE name = '12 inch teddy bear' 

To run the query, create the following script in the Grails console.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import com.emmanuelrosa.gormtut.*
import org.hibernate.transform.AliasToEntityMapResultTransformer

Vendor.withNewSession { session ->
    // Run GORM queries within this Closure.

    session.createSQLQuery("""
        SELECT *
        FROM product
        WHERE name = '12 inch teddy bear'
        """)
        .setResultTransformer(new AliasToEntityMapResultTransformer())
        .list()

}

You're using Hibernate's Session.createSQLQuery(String) to create a SQL query wthich is represented by a SQLQuery instance. The AliasToEntityMapResultTransformer is used to convert each row from a List into a Map, making it possible to access each column by name. Execute the script to see the output, which when formatted for clarity looks like this:

1
2
3
[
    ['ID':2, 'PRICE':8.99, 'NAME':'12 inch teddy bear', 'VERSION':0, 'DESCRIPTION':'12 inch teddy bear, comes with cap and jacket', 'VENDOR_ID':1]
]

The output is a List of Maps containing each of the selected columns. Without the AliasToEntityMapResultTransformer the output would have been a List of Lists. Now lets do the equivalent with a HQL query lacking a select clause.

HQL

To see what HQL does without a select clause take the HQL query...

FROM Product
WHERE name = '12 inch teddy bear' 

...and place it in the Grails console.

1
2
3
4
5
6
7
8
9
10
11
import com.emmanuelrosa.gormtut.*

Vendor.withNewSession { session ->
    // Run GORM queries within this Closure.

    Product.executeQuery("""
    FROM Product
    WHERE name = '12 inch teddy bear' 
    """)

}

Then execute the script. The output looks like this:

[com.emmanuelrosa.gormtut.Product : 2]

Executing an HQL query

The GormEntity trait adds a number of methods to domain classes for executing HQL queries. To keep things simple, I'll stick to executeQuery(String query), executeQuery(String query, Map args), find(String query) and find(String query, Map params).

Product.executeQuery("FROM Product WHERE name = '12 inch teddy bear'")

HQL parameters

In the example above the HQL query is represented as a String. And within the query there’s the String literal '12 inch teddy bear'. Such nested Strings can become difficult to read, so here’s a way to execute the same query using parameters instead.

Product.executeQuery('FROM Product WHERE name = :name', [name: '12 inch teddy bear'])

Get the first (or only) result

If your query returns a single object as the result, or you only want to use the first result, you can use the GormEntity.find(String) method.

Product.find('FROM Product WHERE name = :name', [name: '12 inch teddy bear'])

Restrictions

The example query shown earlier contains a simple equality restriction in the WHERE clause.

WHERE name = '12 inch teddy bear'

The following table lists various SQL and HQL restrictions. As you’ll see, they are identical.

Description SQL & HQL
equality name = ‘12 inch teddy bear
inequality name <> ‘12 inch teddy bear
like name like '%teddy bear%'
case-insensitive like lower(name) like lower('%Teddy Bear%')
between price between 4 and 8
not between price not between 4 and 8
in price in (3.49, 9.49)
not in not in (3.49, 9.49)
less than price < 4.99
greater than price > 4.99
less than or equal to price <= 4.99
greater than or equal to price >= 4.99
is null state is null
is not null state is not null

Collection associations

HQL has a size() function which can be used to count the number of items in a GORM hasMany association, such as the products association in the Vendor class. For example, to query the Vendors with more than two Products, you’d likely do something like this in SQL:

1
2
3
4
SELECT v.id 
FROM vendor as v INNER JOIN product as p ON p.vendor_id = v.id
GROUP BY v.id
HAVING count(p.id) > 2

Lets try it. Run the query in the Grails console to get the List of Vendor IDs.

1
2
3
4
5
6
7
8
...
session.createSQLQuery("""
    SELECT v.id 
    FROM vendor as v INNER JOIN product as p ON p.vendor_id = v.id
    GROUP BY v.id
    HAVING count(p.id) > 2
        """).list()
...

The output is [1, 3]. With HQL you can accomplish the same thing using size(). Plus, you'll get Vendor instances instead of their IDs. Run the following HQL query and see for yourself.

1
2
FROM Vendor 
WHERE size(products) > 2

The size() function does all the heavy lifting! Listed below are examples of restrictions using size().

Description HQL
equality size(products) = 0
inequality size(products) != 0
less than size(products) < 3
greater than size(products) > 3
greater than or equal to size(products) >= 3
less than or equal to size(products) <= 3
between size(products) between 3 and 5

Isn’t that cool! In fact, the from clause article in this series shows an example of using the size(someProperty) = 0 restriction in place of a left outer join. It's certainly a handy function.

Conjunctions

When joining restrictions with and and or, HQL works exactly like the SQL you’re used to. Enough with the trivial, let’s move on to one of the most powerful GORM query methods: criteria queries.

Criteria query

Thus far the transition into GORM queries has been straight-forward. The minor differences between SQL and HQL make it so you can leverage what you already know about SQL. Criteria queries provide a more programmer-friendly approach to GORM queries. Instead of declaring queries as Strings in your Groovy code, you can build them using Groovy. Take a look at the following comparison of a SQL query and its criteria query equivalent.

SQL Criteria query
SELECT *
FROM product Product.withCriteria {
WHERE name = '12 inch teddy bear'     eq('name', '12 inch teddy bear')
  }

In the listing above I aligned the lines of both queries so that each line's intent matches. Like with HQL, I'll skip over the equivalent of the select clause.

Criteria queries are created with a builder. The criteria query builder features methods used to assemble a query. The methods provide the means to specify restrictions, joins, sorting, etc. In the example above, HibernateCriteriaBuilder.eq(String propertyName, Object value) is used to add an equality restriction to the query.

The builder

The builder is actually a facade for a Hibernate Criteria. As such, it expects you to provide a Closure in which the builder methods are called.

1
2
3
4
def builder = Product.createCriteria()
def criteria = builder.buildCriteria {
    eq('name', '12 inch teddy bear')
}

GormEntity.createCriteria() returns a HibernateCriteriaBuilder. Then, HibernateCriteriaBuilder.buildCriteria(Closure) executes the provided closure to assemble a Hibernate Criteria, which it then returns. To continue building the query after calling buildCriteria(Closure) you'd have to use the Hibernate Criteria methods.

Unlike the traditional builder design pattern, a GORM criteria query does not allow you to call the builder methods directly. For example, the following code does not work:

1
2
3
def builder = Product.createCriteria()
    
builder.eq('name', '12 inch teddy bear') // Throws IllegalArgumentException

buildCriteria(Closure) is seldom used because you're unlikely to use the Hibernate Criteria that it returns. Instead, criteria queries can be built and executed by the builder. Speaking of executing queries, lets do just that.

Executing a criteria query

Go ahead and run the following criteria query in the Grails console.

1
2
3
def products = Product.withCriteria {
    eq('name', '12 inch teddy bear')
}

GormEntity.withCriteria(Closure) does the following:

  1. Creates a HibernateCriteriaBuilder.
  2. Uses the builder to assemble a Hibernate Criteria.
  3. Executes the Criteria and returns the results.

Lets do each step on its own so you can see what's happening.

Create the builder

First, create the HibernateCriteriaBuilder.

1
def builder = Product.createCriteria()

Now that you have a builder, use it to create a Hibernate Criteria.

Build the query

This is the point at which you actually create the query.

  1. First, call buildCriteria(Closure) on the builder.
  2. Within the Closure call eq(String propertyName, Object value) to get the Product whose name (the property) is '12 inch teddy bear' (the value).
1
2
3
4
...
def criteria = builder.buildCriteria {
    eq('name', '12 inch teddy bear')
}

Run the query

Finally, run the query by calling list() on the Criteria instance.

1
2
...
def products = criteria.list()

Like the HQL query, the criteria query doesn’t specify which domain properties to return. Instead it returns domain instances. The domain class on which the withCriteria method is executed determines which domain class instances to return.

Get first (or only) result

Sometimes your query returns a single result. Or you want only the first result. Instead of retrieving the first element in the resulting list, you can...

  1. Create the builder with createCriteria().
  2. Call HibernateCriteria.get(Closure) to construct and execute the query.
1
2
3
def product = Product.createCriteria().get {
    eq('name', '12 inch teddy bear')
}

Restrictions

Earlier, I listed various HQL restrictions. Here are their equivalents as criteria queries.

Description SQL Criteria
equality name = ‘12 inch teddy bear eq('name', '12 inch teddy bear')
inequality name <> ‘12 inch teddy bear ne('name', '12 inch teddy bear')
like name like '%teddy bear%' like('name', '%teddy bear%')
case-insensitive like lower(name) like lower('%Teddy Bear%') ilike('name', '%Teddy Bear%')
between price between 4 and 8 between('price', 4.0, 8.0)
not between price not between 4 and 8 not { between('price', 4.0, 8.0) }
in price in (3.49, 9.49) inList('price', [3.49, 9.49])
not in not in (3.49, 9.49) not { inList 'price', [3.49, 9.49] }
less than price < 4.99 lt('price', 4.99)
greater than price > 4.99 gt('price', 4.99)
less than or equal to price <= 4.99 le('price', 4.99)
greater than or equal to price >= 4.99 ge('price', 4.99)
is null state is null isNull('state')
is not null state is not null isNotNull('state')

Unlike HQL which uses expressions to create restrictions, criteria queries use method calls.

Like HQL, criteria queries provide a way to create restrictions based on the size of a collection. The table below compares some size-based HQL restrictions with their equivalents in a criteria query.

Description HQL Criteria
equality size(products) = 0 sizeEq('products', 0)
inequality size(products) != 0 sizeNe('products', 0)
less than size(products) < 3 sizeLt('products', 3)
greater than size(products) > 3 sizeGt('products', 3)
greater than or equal to size(products) >= 3 sizeGe('products', 3)
less than or equal to size(products) <= 3 sizeLe('products', 3)
between size(products) between 3 and 5 N/A

Like the prior list of restrictions, criteria queries rely on method calls, not expressions. That's why there are multiple size?(String propertyName, int value) criteria methods and only one size() HQL function.

What about between?

You may have noticed that criteria queries do not have the equivalent of HQL's expression size(products) between 3 and 5. You can work around this by using sizeGe() along with sizeLe() for an inclusive between, and sizeGt() along with sizeLt() for exclusive between. Either approach requires an and conjunction. So lets look at some conjunctions.

Conjunctions

You may be wondering how criteria queries handle and and or conjunctions. For example, examine the where clause in the following SQL query.

1
2
3
SELECT *
FROM product 
WHERE name like '%bear%' and price > 8

The like and greater than (>) restrictions must both evaluate to true for a given record to be selected. Lets look at how you can express the same restrictions in a criteria query. Begin with a query with only a single restriction.

1
2
3
Product.withCriteria {
    like('name', '%bear%')
}

Next, simply add the appropriate method call for the other restriction.

1
2
3
4
    ...
    like('name', '%bear%') // This is the restriction you had already created. 
    gt('price', 8.0)       // This is the new one. 
    ...

That's really all there is to it. By default, when a query contains more than one restriction they are joined by an and conjunction. An and conjunction is created with HibernateCriteriaBuilder.and(Closure).

1
2
3
4
5
6
Product.withCriteria {
    and {
        like('name', '%bear%')
        gt('price', 8.0)
    }
}

Similarly, an or conjunction is created with HibernateCriteriaBuilder.or(Closure). Again, starting with SQL…

1
2
3
SELECT *
FROM product
WHERE price < 4.99 or price > 8.99

The equivalent criteria query is...

1
2
3
4
5
6
Product.withCriteria {
    or {
        lt('price', 4.99)
        gt('price', 8.99)
    }
}

As you can see, restrictions built within the or(Closure) closure become the or conjunction. Finally, combining and with or

1
2
3
SELECT *
FROM Product
WHERE (price < 4.99 or price > 8.99) and name like '%bear%'

The equivalent criteria query is…

1
2
3
4
5
6
7
8
Product.withCriteria {
    or {
        lt('price', 4.99)
        gt('price', 8.99)
    }
    
    like('name', '%bear%')
}

Something that deluded me when I first learned about criteria queries is that they are not query definitions. They actually consist of Groovy code. In hindsight, I can't believe I overlooked this. But it means its possible to construct queries dynamically without resorting to the shenanigans you may be accustomed to with Strings of SQL statements. Up next is another builder approach to creating queries.

Where query

Like criteria queries, where queries use the builder design pattern. However, restrictions are created with Groovy expressions rather than builder method calls.

SQL Where query
SELECT *
FROM product Product.where {
WHERE name = '12 inch teddy bear'     name == '12 inch teddy bear'
  }.list()

GormEntity.where(Closure) expects a closure in which Groovy expressions are used to create restrictions. where(Closure) returns a DetachedCriteria. Finally, DetachedCriteria.list() executes the query and returns the results.

In comparison to criteria queries, it's worth pointing out is that with criteria queries you specify the property name as a String (ex. 'name'), while in a where query the property is specified like any other Groovy property.

Now it's your turn to create a where query. In the Grails console, first prepare the Closure that will contain the query. Since you'll be querying the Product domain class, call Product.where(Closure).

1
2
3
def builder = Product.where {

}

Then, add the equality restriction comparing the name property to the String literal '12 inch teddy bear'. Its just a matter of using Groovy's equality (==) operator.

1
2
3
def builder = Product.where {
    name == '12 inch teddy bear'
}

Finally, execute the query by calling DetachedCriteria.list().

1
2
...
builder.list()

Get first (or only) result

Sometimes your query returns a single result. Or you want only the first result. Instead of retrieving the first element in the resulting list, you can call DetachedCriteria.get().

1
2
3
def product = Product.where {
    name == '12 inch teddy bear'
}.get()

Restrictions

The following table lists various SQL restrictions and their equivalent where query restrictions.

Description SQL Where query
equality name = ‘12 inch teddy bear name == '12 inch teddy bear'
inequality name <> ‘12 inch teddy bear name != '12 inch teddy bear'
like name like '%teddy bear%' name ==~ '%teddy bear%'
case-insensitive like lower(name) like lower('%Teddy Bear%') name =~ '%Teddy Bear%'
between price between 4 and 8 price in 4.0..8.0
not between price not between 4 and 8 !(price in 4.0..8.0)
in price in (3.49, 9.49) price in [3.49, 9.49]
not in not in (3.49, 9.49) !(price in [3.49, 9.49])
less than price < 4.99 price < 4.99
greater than price > 4.99 price > 4.99
less than or equal to price <= 4.99 price <= 4.99
greater than or equal to price >= 4.99 price >= 4.99
is null state is null state == null
is not null state is not null state != null

Like HQL, and criteria queries, where queries can perform operations based on the size of a collection association. It's done with the Collection.size() method. The table below compares various size-based HQL restrictions with their where query equivalents.

Description HQL Where
equality size(products) = 0 products.size() == 0
inequality size(products) != 0 products.size() != 0
less than size(products) < 3 products.size() < 3
greater than size(products) > 3 products.size() > 3
greater than or equal to size(products) >= 3 products.size() >= 3
less than or equal to size(products) <= 3 products.size() <= 3
between size(products) between 3 and 5 N/A

Between is missing here too!

Where queries also do not have the equivalent of HQL's expression size(products) between 3 and 5. You can work around this by using the >= and <= operators for an inclusive between, or > and < for exclusive between. An example of an inclusive between is: products.size() >= 3 && products.size() <= 5. Either approach requires an and conjunction.

Conjunctions

Where queries handle and and or conjunctions using Groovy logical operators. When a query contains more than one restriction by default they are joined an and conjunction. For example, examine the where clause in the following SQL query.

1
2
3
SELECT *
FROM product 
WHERE name like '%bear%' and price > 8

Two conditions must be met to select a given product. The equivalent restriction can be expressed as a where query like this:

1
2
3
4
Product.where {
    name ==~ '%bear%'
    price > 8
}.list()

Which can also be expressed using the && operator like this:

1
2
3
Product.where {
    name ==~ '%bear%' && price > 8
}.list()

An or conjunction is created with the || operator. Again, starting with SQL…

1
2
3
SELECT *
FROM product
WHERE price < 4.99 or price > 8.99

The equivalent where query is as follows:

1
2
3
Product.where {
    price < 4.99 || price > 8.99
}.list()

As you can see, restrictions built within the || operator become the or conjunction. Finally, combining and with or

1
2
3
SELECT *
FROM Product
WHERE (price < 4.99 or price > 8.99) and name like '%bear%'

The equivalent where query is…

1
2
3
4
Product.where {
    price < 4.99 || price > 8.99
    name ==~ '%bear%'
}.list()

Dynamic finder

Dynamic finders provide a way to execute very simple queries.

SQL Dynamic finder
SELECT *  
FROM product Product.findAllByName('12 inch teddy bear')
WHERE name = '12 inch teddy bear'  

A Dynamic finder is a domain class method following a specific naming convention. The term dynamic finder comes from the fact that the method doesn’t actually exist. Thank you, Groovy MOP.

The method naming convention loosely defined is domainClass.findAllByPropertyNameInCamelCaseWithAComparison(and, some, parameters).

Using the query above as an example...

  1. findAllBy indicates that you want to execute a dynamic finder which returns one or more Products.
  2. Name specifies the property.
  3. The equals operator is used by default, so it's left unspecified.
  4. '12 inch teddy bear' is the property value to compare against.

Lets give a dynamic finder a go. Create a one to return all of the Products with a price of 3.49. In the Grails console, begin with the domain class and findAllBy.

1
Product.findAllBy

Next, append the property name in camel case.

1
Product.findAllByPrice

That completes the method name. Now you need to add the value 3.49 as a parameter.

1
Product.findAllByPrice(3.49)

Finally, run it to see the three matching Products.

1
2
3
4
5
[
    com.emmanuelrosa.gormtut.Product : 4, 
    com.emmanuelrosa.gormtut.Product : 5, 
    com.emmanuelrosa.gormtut.Product : 6
]

Get first (or only) result

If your query returns a single result, or you only want to use the first result begin the dynamic finder method with findBy rather than findAllBy:

1
def product = Product.findByName('12 inch teddy bear')

Like all the GORM queries discussed so far, the dynamic finder query does not specify the equivalent of a SELECT clause. In fact, dynamic finders do not support the notion. They always return a list of domain class instances.

Restrictions

The following table lists various SQL restrictions and the equivalent dynamic finder methods.

Description SQL Dynamic finder
equality name = ‘12 inch teddy bear Product.findAllByName('12 inch teddy bear')
inequality name <> ‘12 inch teddy bear Product.findAllByNameNotEqual('12 inch teddy bear')
like name like '%teddy bear%' Product.findAllByNameLike('%teddy bear%')
case-insensitive like lower(name) like lower('%Teddy Bear%') Product.findAllByNameIlike('%Teddy Bear%')
between price between 4 and 8 Product.findAllByPriceBetween(4, 8)
not between price not between 4 and 8 N/A
in price in (3.49, 9.49) Product.findAllByPriceInList([3.49, 9.49])
not in not in (3.49, 9.49) N/A
less than price < 4.99 Product.findAllByPriceLessThan( 4.99)
greater than price > 4.99 Product.findAllByPriceGreaterThan(4.99)
less than or equal to price <= 4.99 Product.findAllByPriceLessThanEquals(4.99)
greater than or equal to price >= 4.99 Product.findAllByPriceGreaterThanEquals(4.99)
is null state is null Address.findAllByStateIsNull()
is not null state is not null Address.findAllByStateIsNotNull()

Conjunctions

Dynamic finders support and and or conjunctions, but without order of precedence. For example, examine the where clause in the following SQL query.

1
2
3
SELECT *
FROM product 
WHERE name like '%bear%' and price > 8

The equivalent dynamic finder is…

Product.findAllByNameLikeAndPriceGreaterThan('%bear%', 8)

In the dynamic finder example above, the NameLike and PriceGreaterThan parts of the method are joined by an And. Then, the parameters are specified in the same order as the properties are declared in the method: name, then price.

An or conjunction works the same way:

Product.findAllByPriceLessThanOrPriceGreaterThan(4.99, 8.99)

It’s possible to combine and and or as long as order of precedence is not important. For example, the following SQL query cannot be represented as a dynamic finder:

1
2
3
SELECT *
FROM product
WHERE (price < 4.99 or price > 8.99) and name like '%bear%'

Conclusion

That concludes this article in the GORM for SQLaddicts series. Now that you know how to construct the equivalent of a SQL where clause using a criteria query, where query, and dynamic finder, you're ready to tackle the equivalent of the from clause. Ready?