(Quick Reference)

7 Querying with GORM

Version: 2023.3.0-SNAPSHOT

7.1Listing instances

7.2Retrieval by Database Identifier

7.3Dynamic Finders

7.4Where Queries

7.5Criteria

7.6Detached Criteria

7.7Hibernate Query Language (HQL)

7 Querying with GORM

GORM supports a number of powerful ways to query from dynamic finders, to criteria to Hibernate’s object oriented query language HQL. Depending on the complexity of the query you have the following options in order of flexibility and power:

Dynamic Finders
Where Queries
Criteria Queries
Hibernate Query Language (HQL)

In addition, Groovy’s ability to manipulate collections with GPath and methods like sort, findAll and so on combined with GORM results in a powerful combination.

However, let’s start with the basics.

7.1 Listing instances

Use the list() method to obtain all instances of a given class:

def books = Book.list()

The list() method supports arguments to perform pagination:

def books = Book.list(offset:10, max:20)

as well as sorting:

def books = Book.list(sort:"title", order:"asc")

Here, the sort argument is the name of the domain class property that you wish to sort on, and the order argument is either asc for *asc*ending or desc for *desc*ending.

7.2 Retrieval by Database Identifier

The second basic form of retrieval is by database identifier using the get(id) method:

def book = Book.get(23)

You can also obtain a list of instances for a set of identifiers using getAll():

def books = Book.getAll(23, 93, 81)

7.3 Dynamic Finders

GORM supports the concept of dynamic finders. A dynamic finder looks like a static method invocation, but the methods themselves don’t actually exist in any form at the code level.

Instead, a method is auto-magically generated using code synthesis at runtime, based on the properties of a given class. Take for example the Book class:

class Book {
    String title
    Date releaseDate
    Author author
}

class Author {
    String name
}

The Book class has properties such as title, releaseDate and author. These can be used by the findBy* and findAllBy* methods in the form of "method expressions":

def book = Book.findByTitle("The Stand")

book = Book.findByTitleLike("Harry Pot%")

book = Book.findByReleaseDateBetween(firstDate, secondDate)

book = Book.findByReleaseDateGreaterThan(someDate)

book = Book.findByTitleLikeOrReleaseDateLessThan("%Something%", someDate)

Method Expressions

A method expression in GORM is made up of the prefix such as findBy* followed by an expression that combines one or more properties. The basic form is:

Book.findBy(<<Property>><<Comparator>><<Boolean Operator>>)?<<Property>><<Comparator>>

The tokens marked with a '?' are optional. Each comparator changes the nature of the query. For example:

def book = Book.findByTitle("The Stand")

book =  Book.findByTitleLike("Harry Pot%")

In the above example the first query is equivalent to equality whilst the latter, due to the Like comparator, is equivalent to a SQL like expression.

The possible comparators include:

InList - In the list of given values
LessThan - less than a given value
LessThanEquals - less than or equal a give value
GreaterThan - greater than a given value
GreaterThanEquals - greater than or equal a given value
Like - Equivalent to a SQL like expression
Ilike - Similar to a Like, except case insensitive
NotEqual - Negates equality
InRange - Between the from and to values of a Groovy Range
Rlike - Performs a Regexp LIKE in MySQL or Oracle otherwise falls back to Like
Between - Between two values (requires two arguments)
IsNotNull - Not a null value (doesn’t take an argument)
IsNull - Is a null value (doesn’t take an argument)

Notice that the last three require different numbers of method arguments compared to the rest, as demonstrated in the following example:

def now = new Date()
def lastWeek = now - 7
def book = Book.findByReleaseDateBetween(lastWeek, now)

books = Book.findAllByReleaseDateIsNull()
books = Book.findAllByReleaseDateIsNotNull()

Boolean logic (AND/OR)

Method expressions can also use a boolean operator to combine two or more criteria:

def books = Book.findAllByTitleLikeAndReleaseDateGreaterThan(
                      "%Java%", new Date() - 30)

In this case we’re using And in the middle of the query to make sure both conditions are satisfied, but you could equally use Or:

def books = Book.findAllByTitleLikeOrReleaseDateGreaterThan(
                      "%Java%", new Date() - 30)

You can combine as many criteria as you like, but they must all be combined with And or all Or. If you need to combine And and Or or if the number of criteria creates a very long method name, just convert the query to a Criteria or HQL query.

Querying Associations

Associations can also be used within queries:

def author = Author.findByName("Stephen King")

def books = author ? Book.findAllByAuthor(author) : []

In this case if the Author instance is not null we use it in a query to obtain all the Book instances for the given Author.

Pagination and Sorting

The same pagination and sorting parameters available on the list() method can also be used with dynamic finders by supplying a map as the final parameter:

def books = Book.findAllByTitleLike("Harry Pot%",
               [max: 3, offset: 2, sort: "title", order: "desc"])

7.4 Where Queries

The where() method builds on the support for Detached Criteria by providing an enhanced, compile-time checked query DSL for common queries. The where method is more flexible than dynamic finders, less verbose than criteria and provides a powerful mechanism to compose queries.

Basic Querying

The where() method accepts a closure that looks very similar to Groovy’s regular collection methods. The closure should define the logical criteria in regular Groovy syntax, for example:

def query = Person.where {
   firstName == "Bart"
}
Person bart = query.find()

The returned object is a DetachedCriteria instance, which means it is not associated with any particular database connection or session. This means you can use the where method to define common queries at the class level:

import grails.gorm.*

class Person {
    static DetachedCriteria<Person> simpsons = where {
         lastName == "Simpson"
    }
    ...
}
...
Person.simpsons.each { Person p ->
    println p.firstname
}

Query execution is lazy and only happens upon usage of the DetachedCriteria instance. If you want to execute a where-style query immediately there are variations of the findAll and find methods to accomplish this:

def results = Person.findAll {
     lastName == "Simpson"
}
def results = Person.findAll(sort:"firstName") {
     lastName == "Simpson"
}
Person p = Person.find { firstName == "Bart" }

Each Groovy operator maps onto a regular criteria method. The following table provides a map of Groovy operators to methods:

Operator Criteria Method Description

==

Equal to

!=

Not equal to

>

Greater than

<

Less than

>=

Greater than or equal to

⇐

Less than or equal to

in

inList

Contained within the given list

==~

Like a given string

=~

ilike

Case insensitive like

It is possible use regular Groovy comparison operators and logic to formulate complex queries:

def query = Person.where {
    (lastName != "Simpson" && firstName != "Fred") || (firstName == "Bart" && age > 9)
}
def results = query.list(sort:"firstName")

The Groovy regex matching operators map onto like and ilike queries unless the expression on the right hand side is a Pattern object, in which case they map onto an rlike query:

def query = Person.where {
     firstName ==~ ~/B.+/
}

Note that rlike queries are only supported if the underlying database supports regular expressions

A between criteria query can be done by combining the in keyword with a range:

def query = Person.where {
     age in 18..65
}

Finally, you can do isNull and isNotNull style queries by using null with regular comparison operators:

def query = Person.where {
     middleName == null
}

Query Composition

Since the return value of the where method is a DetachedCriteria instance you can compose new queries from the original query:

DetachedCriteria<Person> query = Person.where {
     lastName == "Simpson"
}
DetachedCriteria<Person> bartQuery = query.where {
     firstName == "Bart"
}
Person p = bartQuery.find()

Note that you cannot pass a closure defined as a variable into the where method unless it has been explicitly cast to a DetachedCriteria instance. In other words the following will produce an error:

def callable = {
    lastName == "Simpson"
}
def query = Person.where(callable)

The above must be written as follows:

import grails.gorm.DetachedCriteria

def callable = {
    lastName == "Simpson"
} as DetachedCriteria<Person>
def query = Person.where(callable)

As you can see the closure definition is cast (using the Groovy as keyword) to a DetachedCriteria instance targeted at the Person class.

Conjunction, Disjunction and Negation

As mentioned previously you can combine regular Groovy logical operators (|| and &&) to form conjunctions and disjunctions:

def query = Person.where {
    (lastName != "Simpson" && firstName != "Fred") || (firstName == "Bart" && age > 9)
}

You can also negate a logical comparison using !:

def query = Person.where {
    firstName == "Fred" && !(lastName == 'Simpson')
}

Property Comparison Queries

If you use a property name on both the left hand and right side of a comparison expression then the appropriate property comparison criteria is automatically used:

def query = Person.where {
   firstName == lastName
}

The following table described how each comparison operator maps onto each criteria property comparison method:

Operator

Criteria Method

Description

eqProperty

Equal to

neProperty

Not equal to

gtProperty

Greater than

ltProperty

Less than

geProperty

Greater than or equal to

⇐

leProperty

Less than or equal to

Querying Associations

Associations can be queried by using the dot operator to specify the property name of the association to be queried:

def query = Pet.where {
    owner.firstName == "Joe" || owner.firstName == "Fred"
}

You can group multiple criterion inside a closure method call where the name of the method matches the association name:

def query = Person.where {
    pets { name == "Jack" || name == "Joe" }
}

This technique can be combined with other top-level criteria:

def query = Person.where {
     pets { name == "Jack" } || firstName == "Ed"
}

For collection associations it is possible to apply queries to the size of the collection:

def query = Person.where {
       pets.size() == 2
}

The following table shows which operator maps onto which criteria method for each size() comparison:

Operator

Criteria Method

Description

sizeEq

The collection size is equal to

sizeNe

The collection size is not equal to

sizeGt

The collection size is greater than

sizeLt

The collection size is less than

sizeGe

The collection size is greater than or equal to

⇐

sizeLe

The collection size is less than or equal to

Query Aliases and Sorting

If you define a query for an association an alias is automatically generated for the query. For example the following query:

def query = Pet.where {
    owner.firstName == "Fred"
}

Will generate an alias for the owner association such as owner_alias_0. These generated aliases are fine for most cases, but are not useful if you want to later sort or use a projection on the results. For example the following query will fail:

// fails because a dynamic alias is used
Pet.where {
    owner.firstName == "Fred"
}.list(sort:"owner.lastName")

If you plan to sort the results then an explicit alias should be used and these can be defined by simply declaring a variable in the where query:

def query = Pet.where {
    def o1 = owner (1)
    o1.firstName == "Fred" (2)
}.list(sort:'o1.lastName') (3)

1	Define an alias called `o1`
2	Use the alias in the query itself
3	Use the alias to sort the results

By assigning the name of an association to a local variable it will automatically become an alias usable within the query itself and also for the purposes of sorting or projecting the results.

Subqueries

It is possible to execute subqueries within where queries. For example to find all the people older than the average age the following query can be used:

final query = Person.where {
  age > avg(age)
}

The following table lists the possible subqueries:

Method

Description

avg

The average of all values

sum

The sum of all values

max

The maximum value

min

The minimum value

count

The count of all values

property

Retrieves a property of the resulting entities

You can apply additional criteria to any subquery by using the of method and passing in a closure containing the criteria:

def query = Person.where {
  age > avg(age).of { lastName == "Simpson" } && firstName == "Homer"
}

Since the property subquery returns multiple results, the criterion used compares all results. For example the following query will find all people younger than people with the surname "Simpson":

Person.where {
    age < property(age).of { lastName == "Simpson" }
}

More Advanced Subqueries in GORM

The support for subqueries has been extended. You can now use in with nested subqueries

def results = Person.where {
    firstName in where { age < 18 }.firstName
}.list()

Criteria and where queries can be seamlessly mixed:

def results = Person.withCriteria {
    notIn "firstName", Person.where { age < 18 }.firstName
 }

Subqueries can be used with projections:

def results = Person.where {
    age > where { age > 18 }.avg('age')
}

Correlated queries that span two domain classes can be used:

def employees = Employee.where {
    region.continent in ['APAC', "EMEA"]
    }.id()
    def results = Sale.where {
    employee in employees && total > 100000
    }.employee.list()

And support for aliases (cross query references) using simple variable declarations has been added to where queries:

def query = Employee.where {
    def em1 = Employee
    exists Sale.where {
        def s1 = Sale
        def em2 = employee
        return em2.id == em1.id
    }.id()
}
def results = query.list()

Other Functions

There are several functions available to you within the context of a query. These are summarized in the table below:

Method

Description

second

The second of a date property

minute

The minute of a date property

hour

The hour of a date property

day

The day of the month of a date property

month

The month of a date property

year

The year of a date property

lower

Converts a string property to lower case

upper

Converts a string property to upper case

length

The length of a string property

trim

Trims a string property

Currently functions can only be applied to properties or associations of domain classes. You cannot, for example, use a function on a result of a subquery.

For example the following query can be used to find all pet’s born in 2011:

def query = Pet.where {
    year(birthDate) == 2011
}

You can also apply functions to associations:

def query = Person.where {
    year(pets.birthDate) == 2009
}

Batch Updates and Deletes

Since each where method call returns a DetachedCriteria instance, you can use where queries to execute batch operations such as batch updates and deletes. For example, the following query will update all people with the surname "Simpson" to have the surname "Bloggs":

DetachedCriteria<Person> query = Person.where {
    lastName == 'Simpson'
}
int total = query.updateAll(lastName:"Bloggs")

Note that one limitation with regards to batch operations is that join queries (queries that query associations) are not allowed.

To batch delete records you can use the deleteAll method:

DetachedCriteria<Person> query = Person.where {
    lastName == 'Simpson'
}
int total = query.deleteAll()

7.5 Criteria

Criteria is an advanced way to query that uses a Groovy builder to construct potentially complex queries. It is a much better approach than building up query strings using a StringBuilder.

Criteria can be used either with the createCriteria() or withCriteria(closure) methods.

The builder uses Hibernate’s Criteria API. The nodes on this builder map the static methods found in the Restrictions class of the Hibernate Criteria API. For example:

def c = Account.createCriteria()
def results = c {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {
        like("holderFirstName", "Fred%")
        like("holderFirstName", "Barney%")
    }
    maxResults(10)
    order("holderLastName", "desc")
}

This criteria will select up to 10 Account objects in a List matching the following criteria:

balance is between 500 and 1000
branch is 'London'
holderFirstName starts with 'Fred' or 'Barney'

The results will be sorted in descending order by holderLastName.

If no records are found with the above criteria, an empty List is returned.

Conjunctions and Disjunctions

As demonstrated in the previous example you can group criteria in a logical OR using an or { } block:

or {
    between("balance", 500, 1000)
    eq("branch", "London")
}

This also works with logical AND:

and {
    between("balance", 500, 1000)
    eq("branch", "London")
}

And you can also negate using logical NOT:

not {
    between("balance", 500, 1000)
    eq("branch", "London")
}

All top level conditions are implied to be AND’d together.

Querying Associations

Associations can be queried by having a node that matches the property name. For example say the Account class had many Transaction objects:

class Account {
    ...
    static hasMany = [transactions: Transaction]
    ...
}

We can query this association by using the property name transactions as a builder node:

def c = Account.createCriteria()
def now = new Date()
def results = c.list {
    transactions {
        between('date', now - 10, now)
    }
}

The above code will find all the Account instances that have performed transactions within the last 10 days. You can also nest such association queries within logical blocks:

def c = Account.createCriteria()
def now = new Date()
def results = c.list {
    or {
        between('created', now - 10, now)
        transactions {
            between('date', now - 10, now)
        }
    }
}

Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days.

Querying with Projections

Projections may be used to customise the results. Define a "projections" node within the criteria builder tree to use projections. There are equivalent methods within the projections node to the methods found in the Hibernate Projections class:

def c = Account.createCriteria()

def numberOfBranches = c.get {
    projections {
        countDistinct('branch')
    }
}

When multiple fields are specified in the projection, a List of values will be returned. A single value will be returned otherwise.

Transforming Projection Results

If the raw value or simple object array returned by the criteria method doesn’t suit your needs, the result can be transformed with a ResultTransformer. Let’s say we want to transform the criteria results into a Map so that we can easily reference values by key:

def c = Account.createCriteria()

def accountsOverview = c.get {
    resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
    projections {
        sum('balance', 'allBalances')
        countDistinct('holderLastName', 'lastNames')
    }
}

// accountsOverview.allBalances
// accountsOverview.lastNames

Note that we’ve added an alias to each projection as an additional parameter to be used as the key. For this to work, all projections must have aliases defined, otherwise the corresponding map entry will not be built.

We can also transform the result into an object of our choosing via the Transformers.aliasToBean() method. In this case, we’ll transform it into an AccountsOverview:

class AccountsOverview {
    Number allBalances
    Number lastNames
}

def c = Account.createCriteria()

def accountsOverview = c.get {
    resultTransformer(Transformers.aliasToBean(AccountsOverview))
    projections {
        sum('balance', 'allBalances')
        countDistinct('holderLastName', 'lastNames')
    }
}

// accountsOverview instanceof AccountsOverview

Each alias must have a corresponding property or explicit setter on the bean otherwise an exception will be thrown.

SQL Projections

The criteria DSL provides access to Hibernate’s SQL projection API.

// Box is a domain class...
class Box {
    int width
    int height
}

// Use SQL projections to retrieve the perimeter and area of all of the Box instances...
def c = Box.createCriteria()

def results = c.list {
    projections {
      sqlProjection '(2 * (width + height)) as perimeter, (width * height) as area', ['perimeter', 'area'], [INTEGER, INTEGER]
    }
}

The first argument to the sqlProjection method is the SQL which defines the projections. The second argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL. The third argument is a list of org.hibernate.type.Type instances which correspond to the projected values expressed in the SQL. The API supports all org.hibernate.type.Type objects but constants like INTEGER, LONG, FLOAT etc. are provided by the DSL which correspond to all of the types defined in org.hibernate.type.StandardBasicTypes.

Consider that the following table represents the data in the BOX table.

width	height
2	7
2	8
2	9
4	9

The query above would return results like this:

[[18, 14], [20, 16], [22, 18], [26, 36]]

Each of the inner lists contains the 2 projected values for each Box, perimeter and area.

Note that if there are other references in scope wherever your criteria query is expressed that have names that conflict with any of the type constants described above, the code in your criteria will refer to those references, not the type constants provided by the DSL. In the unlikely event of that happening you can disambiguate the conflict by referring to the fully qualified Hibernate type. For example StandardBasicTypes.INTEGER instead of INTEGER.

If only 1 value is being projected, the alias and the type do not need to be included in a list.

def results = c.list {
    projections {
      sqlProjection 'sum(width * height) as totalArea', 'totalArea', INTEGER
    }
}

That query would return a single result with the value of 84 as the total area of all of the Box instances.

The DSL supports grouped projections with the sqlGroupProjection method.

def results = c.list {
    projections {
        sqlGroupProjection 'width, sum(height) as combinedHeightsForThisWidth', 'width', ['width', 'combinedHeightsForThisWidth'], [INTEGER, INTEGER]
    }
}

The first argument to the sqlGroupProjection method is the SQL which defines the projections. The second argument represents the group by clause that should be part of the query. That string may be single column name or a comma separated list of column names. The third argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL. The fourth argument is a list of org.hibernate.type.Type instances which correspond to the projected values expressed in the SQL.

The query above is projecting the combined heights of boxes grouped by width and would return results that look like this:

[[2, 24], [4, 9]]

Each of the inner lists contains 2 values. The first value is a box width and the second value is the sum of the heights of all of the boxes which have that width.

Using SQL Restrictions

You can access Hibernate’s SQL Restrictions capabilities.

def c = Person.createCriteria()

def peopleWithShortFirstNames = c.list {
    sqlRestriction "char_length(first_name) <= 4"
}

SQL Restrictions may be parameterized to deal with SQL injection vulnerabilities related to dynamic restrictions.

def c = Person.createCriteria()

def peopleWithShortFirstNames = c.list {
    sqlRestriction "char_length(first_name) < ? AND char_length(first_name) > ?", [maxValue, minValue]
}

Note that the parameter there is SQL. The first_name attribute referenced in the example refers to the persistence model, not the object model like in HQL queries. The Person property named firstName is mapped to the first_name column in the database and you must refer to that in the sqlRestriction string.

Also note that the SQL used here is not necessarily portable across databases.

Using Scrollable Results

You can use Hibernate’s ScrollableResults feature by calling the scroll method:

def results = crit.scroll {
    maxResults(10)
}
def f = results.first()
def l = results.last()
def n = results.next()
def p = results.previous()

def future = results.scroll(10)
def accountNumber = results.getLong('number')

To quote the documentation of Hibernate ScrollableResults:

A result iterator that allows moving around within the results by arbitrary increments. The Query / ScrollableResults pattern is very similar to the JDBC PreparedStatement / ResultSet pattern and the semantics of methods of this interface are similar to the similarly named methods on ResultSet.

Contrary to JDBC, columns of results are numbered from zero.

Setting properties in the Criteria instance

If a node within the builder tree doesn’t match a particular criterion it will attempt to set a property on the Criteria object itself. This allows full access to all the properties in this class. This example calls setMaxResults and setFirstResult on the Criteria instance:

import org.hibernate.FetchMode as FM
...
def results = c.list {
    maxResults(10)
    firstResult(50)
    fetchMode("aRelationship", FM.JOIN)
}

Querying with Eager Fetching

In the section on Eager and Lazy Fetching we discussed how to declaratively specify fetching to avoid the N+1 SELECT problem. However, this can also be achieved using a criteria query:

def criteria = Task.createCriteria()
def tasks = criteria.list{
    eq "assignee.id", task.assignee.id
    join 'assignee'
    join 'project'
    order 'priority', 'asc'
}

Notice the usage of the join method: it tells the criteria API to use a JOIN to fetch the named associations with the Task instances. It’s probably best not to use this for one-to-many associations though, because you will most likely end up with duplicate results. Instead, use the 'select' fetch mode:

import org.hibernate.FetchMode as FM
...
def results = Airport.withCriteria {
    eq "region", "EMEA"
    fetchMode "flights", FM.SELECT
}

Although this approach triggers a second query to get the flights association, you will get reliable results - even with the maxResults option.

fetchMode and join are general settings of the query and can only be specified at the top-level, i.e. you cannot use them inside projections or association constraints.

An important point to bear in mind is that if you include associations in the query constraints, those associations will automatically be eagerly loaded. For example, in this query:

def results = Airport.withCriteria {
    eq "region", "EMEA"
    flights {
        like "number", "BA%"
    }
}

the flights collection would be loaded eagerly via a join even though the fetch mode has not been explicitly set.

Method Reference

If you invoke the builder with no method name such as:

c { ... }

The build defaults to listing all the results and hence the above is equivalent to:

c.list { ... }

Method Description

list

This is the default method. It returns all matching rows.

get

Returns a unique result set i.e. just one row. The criteria has to be formed in a way that it only queries one row. This method is not to be confused with a limit to just the first row.

scroll

Returns a scrollable result set.

listDistinct

If subqueries or associations are used one may end up with the same row multiple times in the result set. This allows listing only distinct entities and is equivalent to DISTINCT_ROOT_ENTITY of the CriteriaSpecification class.

count

Returns the number of matching rows.

Combining Criteria

You can combine multiple criteria closures in the following way:

def emeaCriteria = {
    eq "region", "EMEA"
}

def results = Airport.withCriteria {
    emeaCriteria.delegate = delegate
    emeaCriteria()
    flights {
        like "number", "BA%"
    }
}

This technique requires that each criteria must refer to the same domain class (i.e. Airport). A more flexible approach is to use Detached Criteria, as described in the following section.

7.6 Detached Criteria

Detached Criteria are criteria queries that are not associated with any given database session/connection. Supported since Grails 2.0, Detached Criteria queries have many uses including allowing you to create common reusable criteria queries, execute subqueries and execute batch updates/deletes.

Building Detached Criteria Queries

The primary point of entry for using the Detached Criteria is the DetachedCriteria class which accepts a domain class as the only argument to its constructor:

import grails.gorm.*
...
def criteria = new DetachedCriteria(Person)

Once you have obtained a reference to a detached criteria instance you can execute where queries or criteria queries to build up the appropriate query. To build a normal criteria query you can use the build method:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}

Note that methods on the DetachedCriteria instance do not mutate the original object but instead return a new query. In other words, you have to use the return value of the build method to obtain the mutated criteria object:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
def bartQuery = criteria.build {
    eq 'firstName', 'Bart'
}

Executing Detached Criteria Queries

Unlike regular criteria, Detached Criteria are lazy, in that no query is executed at the point of definition. Once a Detached Criteria query has been constructed then there are a number of useful query methods which are summarized in the table below:

Method

Description

list

List all matching entities

get

Return a single matching result

count

Count all matching records

exists

Return true if any matching records exist

deleteAll

Delete all matching records

updateAll(Map)

Update all matching records with the given properties

As an example the following code will list the first 4 matching records sorted by the firstName property:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
def results = criteria.list(max:4, sort:"firstName")

You can also supply additional criteria to the list method:

def results = criteria.list(max:4, sort:"firstName") {
    gt 'age', 30
}

To retrieve a single result you can use the get or find methods (which are synonyms):

Person p = criteria.find() // or criteria.get()

The DetachedCriteria class itself also implements the Iterable interface which means that it can be treated like a list:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
criteria.each {
    println it.firstName
}

In this case the query is only executed when the each method is called. The same applies to all other Groovy collection iteration methods.

You can also execute dynamic finders on DetachedCriteria just like on domain classes. For example:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
def bart = criteria.findByFirstName("Bart")

Using Detached Criteria for Subqueries

Within the context of a regular criteria query you can use DetachedCriteria to execute subquery. For example if you want to find all people who are older than the average age the following query will accomplish that:

def results = Person.withCriteria {
     gt "age", new DetachedCriteria(Person).build {
         projections {
             avg "age"
         }
     }
     order "firstName"
 }

Notice that in this case the subquery class is the same as the original criteria query class (i.e. Person) and hence the query can be shortened to:

def results = Person.withCriteria {
     gt "age", {
         projections {
             avg "age"
         }
     }
     order "firstName"
 }

If the subquery class differs from the original criteria query then you will have to use the original syntax.

In the previous example the projection ensured that only a single result was returned (the average age). If your subquery returns multiple results then there are different criteria methods that need to be used to compare the result. For example to find all the people older than the ages 18 to 65 a gtAll query can be used:

def results = Person.withCriteria {
    gtAll "age", {
        projections {
            property "age"
        }
        between 'age', 18, 65
    }

    order "firstName"
}

The following table summarizes criteria methods for operating on subqueries that return multiple results:

Method

Description

gtAll

greater than all subquery results

geAll

greater than or equal to all subquery results

ltAll

less than all subquery results

leAll

less than or equal to all subquery results

eqAll

equal to all subquery results

neAll

not equal to all subquery results

Batch Operations with Detached Criteria

The DetachedCriteria class can be used to execute batch operations such as batch updates and deletes. For example, the following query will update all people with the surname "Simpson" to have the surname "Bloggs":

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
int total = criteria.updateAll(lastName:"Bloggs")

Note that one limitation with regards to batch operations is that join queries (queries that query associations) are not allowed within the DetachedCriteria instance.

To batch delete records you can use the deleteAll method:

def criteria = new DetachedCriteria(Person).build {
    eq 'lastName', 'Simpson'
}
int total = criteria.deleteAll()

7.7 Hibernate Query Language (HQL)

GORM classes also support Hibernate’s query language HQL, a very complete reference for which can be found in the Hibernate documentation of the Hibernate documentation.

GORM provides a number of methods that work with HQL including find, findAll and executeQuery.

An example of a query can be seen below:

def results =
      Book.findAll("from Book as b where b.title like 'Lord of the%'")

Named Parameters

In this case the value passed to the query is hard coded, however you can equally use named parameters:

def results =
      Book.findAll("from Book as b " +
                   "where b.title like :search or b.author like :search",
                   [search: "The Shi%"])

def author = Author.findByName("Stephen King")
def books = Book.findAll("from Book as book where book.author = :author",
                         [author: author])

Multiline Queries

Use the triple quoted strings to separate the query across multiple lines:

def results = Book.findAll("""
from Book as b,
     Author as a
where b.author = a and a.surname = :surname""", [surname:'Smith'])

Pagination and Sorting

You can also perform pagination and sorting whilst using HQL queries. To do so simply specify the pagination options as a Map at the end of the method call and include an "ORDER BY" clause in the HQL:

def results =
      Book.findAll("from Book as b where " +
                   "b.title like 'Lord of the%' " +
                   "order by b.title asc",
                   [max: 10, offset: 20])

<< 6Persistence Basics

8Advanced GORM Features >>

Grace Data Hibernate

Provides integration between Grace and Hibernate through GORM

7 Querying with GORM

Table of Contents

7 Querying with GORM

7.1 Listing instances

7.2 Retrieval by Database Identifier

7.3 Dynamic Finders

Method Expressions

Boolean logic (AND/OR)

Querying Associations

Pagination and Sorting

7.4 Where Queries

Basic Querying

Query Composition

Conjunction, Disjunction and Negation

Property Comparison Queries

Querying Associations

Query Aliases and Sorting

Subqueries

More Advanced Subqueries in GORM

Other Functions

Batch Updates and Deletes

7.5 Criteria

Conjunctions and Disjunctions

Querying Associations

Querying with Projections

Transforming Projection Results

SQL Projections

Using SQL Restrictions

Using Scrollable Results

Setting properties in the Criteria instance

Querying with Eager Fetching

Method Reference

Combining Criteria

7.6 Detached Criteria

Building Detached Criteria Queries

Executing Detached Criteria Queries

Using Detached Criteria for Subqueries

Batch Operations with Detached Criteria

7.7 Hibernate Query Language (HQL)

Named Parameters

Multiline Queries

Pagination and Sorting