5.4 Querying with GORM - Reference Documentation
Authors: Graeme Rocher, Peter Ledbrook, Marc Palmer, Jeff Brown, Luke Daley, Burt Beckwith
Version: null
5.4 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.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.Listing instances
Use the list method to obtain all instances of a given class:def books = Book.list()
def books = Book.list(offset:10, max:20)
def books = Book.list(sort:"title", order:"asc")
sort argument is the name of the domain class property that you wish to sort on, and the order argument is either asc for ascending or desc for descending.Retrieval by Database Identifier
The second basic form of retrieval is by database identifier using the get method:def book = Book.get(23)
def books = Book.getAll(23, 93, 81)
5.4.1 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 theBook class:class Book {
String title
Date releaseDate
Author author
}class Author {
String name
}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]def book = Book.findByTitle("The Stand")book = Book.findByTitleLike("Harry Pot%")
Like comparator, is equivalent to a SQL like expression.The possible comparators include:
InList- In the list of given valuesLessThan- less than a given valueLessThanEquals- less than or equal a give valueGreaterThan- greater than a given valueGreaterThanEquals- greater than or equal a given valueLike- Equivalent to a SQL like expressionIlike- Similar to aLike, except case insensitiveNotEqual- Negates equalityBetween- 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)
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)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)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) : []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"])
5.4.2 Criteria
Criteria is a type safe, 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 aStringBuffer.Criteria can be used either with the createCriteria or withCriteria 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")
}Account objects in a List matching the following criteria:
balanceis between 500 and 1000branchis 'London'holderFirstNamestarts with 'Fred' or 'Barney'
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 anor { } block:or {
between("balance", 500, 1000)
eq("branch", "London")
}and {
between("balance", 500, 1000)
eq("branch", "London")
}not {
between("balance", 500, 1000)
eq("branch", "London")
}Querying Associations
Associations can be queried by having a node that matches the property name. For example say theAccount class had many Transaction objects:class Account {
…
static hasMany = [transactions: Transaction]
…
}transaction as a builder node:def c = Account.createCriteria()
def now = new Date()
def results = c.list {
transactions {
between('date', now - 10, now)
}
}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)
}
}
}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')
}
}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"
}Note that the parameter there is SQL. Thefirst_nameattribute referenced in the example refers to the persistence model, not the object model like in HQL queries. ThePersonproperty namedfirstNameis mapped to thefirst_namecolumn in the database and you must refer to that in thesqlRestrictionstring.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')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 callssetMaxResults 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'
}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 }
flights association, you will get reliable results - even with the maxResults option.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:fetchModeandjoinare 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.
def results = Airport.withCriteria {
eq "region", "EMEA"
flights {
like "number", "BA%"
}
}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 { … }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 that 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. |
5.4.3 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%'")Positional and Named Parameters
In this case the value passed to the query is hard coded, however you can equally use positional parameters:def results =
Book.findAll("from Book as b where b.title like ?", ["The Shi%"])def author = Author.findByName("Stephen King") def books = Book.findAll("from Book as book where book.author = ?", [author])
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 line continuation character 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 = ?", ['Smith'])Triple-quoted Groovy multiline Strings will NOT work with HQL queries.
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])
