Grails Database Queries – Criteria Builder vs. Where Queries [updated]

Before Grails 2.0 arrived, Grails offered 3 ways of doing a database query:

  • dynamic finders
  • HQL queries
  • Hibernate Criteria Builder

If the database query is very simple, dynamic finders are a good choice. But for everything else, I used Criteria Builder which is just the Hibernate Criteria API with builder syntax.

Starting with Grails 2.0, there is another alternative: Where Queries.
Where Queries are similar to the Criteria Builder approach, but using a nicer “programmer friendly” syntax and your queries get checked at compile-time (!).
As it uses Detached Criterias under the hood, it should (hopefully) support everything that is possible with Criteria Builder.

Here is an example of a simple Where Query:

def query = Book.where {
   title == "Grails in Action"
}
Book grailsInAction = query.find()

The syntax is more natural for Groovy developers. You don’t need to have the Hibernate Criteria API in mind. Just write your query as if you would deal with a normal collection using Groovy code. Because it uses AST transformations (compile-time metaprogramming), your query gets compile time checked and you will get IDE support / code completion (e.g. using STS). Using Where Queries, you can refactor your domain model and you will almost instantly see whether or not the refactoring breaks existing query logic.

I asked myself if the Where Query approach is as powerful as Criteria Builder. Are there queries that you can’t implement using Where Queries?
And is the generated SQL identical to Criteria Builder’s SQL statements?

I started to expermiment with same common database queries which I wrote both using Criteria Builder and Where Queries:

Multiple restrictions and querying associations

This example shows how to use multiple restrictions in one query and how to query associations. In this case: list all books with a title that contains the word ‘grails’ (case insensitive) and that have an author whose last name starts with an ‘R’ character (again, case insensitive).

// Criteria Builder
Book.createCriteria().list {
	ilike('title', '%grails%')
	authors {
		ilike('lastName', 'r%')
	}
}

// Where Query
Book.findAll {
	title =~ '%grails%' && authors.lastName =~ 'r%'
}

The generated SQL statements are identical for both approaches:

SELECT THIS_.ID AS ID0_1_, THIS_.VERSION AS VERSION0_1_, THIS_.DATE_CREATED AS DATE3_0_1_, THIS_.ERSCHEINUNGSDATUM AS ERSCHEIN4_0_1_, THIS_.ISBN AS ISBN0_1_, THIS_.LAST_UPDATED AS LAST6_0_1_, THIS_.PUBLISHER_ID AS PUBLISHER7_0_1_, THIS_.TITLE AS TITLE0_1_, AUTHORS3_.BOOK_ID AS BOOK1_0_, AUTHORS_AL1_.ID AS AUTHOR2_, AUTHORS_AL1_.ID AS ID3_0_, AUTHORS_AL1_.VERSION AS VERSION3_0_, AUTHORS_AL1_.FIRST_NAME AS FIRST3_3_0_, AUTHORS_AL1_.LAST_NAME AS LAST4_3_0_ FROM BOOK THIS_ INNER JOIN AUTHOR_BOOKS AUTHORS3_ ON THIS_.ID=AUTHORS3_.BOOK_ID INNER JOIN AUTHOR AUTHORS_AL1_ ON AUTHORS3_.AUTHOR_ID=AUTHORS_AL1_.ID WHERE lower(this_.title) like ? and (lower(authors_al1_.last_name) like ?)

Pagination and sorting

This example show how to sort the results of the query and how to limit the result count, starting at a given offset. In this case: list all books with a title containing the word ‘grails’ (case insensitive), limit results to one result, starting at index 1, sorted by property ‘title’ ascending.

// Criteria Builder
Book.createCriteria().list {
	ilike('title', '%grails%')
	maxResults(1)
	firstResult(1)
	order('title', 'asc')
}

// Where Query
Book.findAll(max:1, offset:1, sort:'title', order:'asc'){
	title =~ '%grails%'
}

The generated SQL statements are identical for both approaches:

SELECT THIS_.ID AS ID1_0_, THIS_.VERSION AS VERSION1_0_, THIS_.DATE_CREATED AS DATE3_1_0_, THIS_.ERSCHEINUNGSDATUM AS ERSCHEIN4_1_0_, THIS_.ISBN AS ISBN1_0_, THIS_.LAST_UPDATED AS LAST6_1_0_, THIS_.PUBLISHER_ID AS PUBLISHER7_1_0_, THIS_.TITLE AS TITLE1_0_ FROM BOOK THIS_ WHERE LOWER(THIS_.TITLE) LIKE ? ORDER BY this_.title asc limit ? offset ?

Unfortunately, the sort order of the Where Query (sort:’title’) doesn’t get compile time checked.

Sorting by more than one property

This example shows how to sort by two properties. In this case: list all books, sorted by title and isbn.

// Criteria Builder
Book.createCriteria().list {
	order('title', 'asc')
	order('isbn', 'asc')
}

// Where Query
def query = Book.where {}.order('title', 'asc').order('isbn', 'asc')
query.list()

The generated SQL statements are identical for both approaches:

SELECT THIS_.ID AS ID3_0_, THIS_.VERSION AS VERSION3_0_, THIS_.DATE_CREATED AS DATE3_3_0_, THIS_.ERSCHEINUNGSDATUM AS ERSCHEIN4_3_0_, THIS_.ISBN AS ISBN3_0_, THIS_.LAST_UPDATED AS LAST6_3_0_, THIS_.PUBLISHER_ID AS PUBLISHER7_3_0_, THIS_.TITLE AS TITLE3_0_ FROM book this_ order by this_.title asc, this_.isbn asc

Although it is possible to use Where Queries here, you have to add the sort order to the query using Criteria API. So: no compile time checks, no IDE support -> no advantage over Criteria Builder approach.

Projections

This example shows how to use restrictions, which means retrieving just some fields and not domain objects. In this case: retrieve just the ‘isbn’ property of all books in the database.

// Criteria Builder
Book.createCriteria().list {
	projections {
		property('isbn')
	}
}

// Where Query
def query = Book.where {}.projections {
	property('isbn')
}
query.list()

Again, it is possible to use Where Queries here, but you have to add the projection to the query using Criteria API. So: no compile time checks, no IDE support -> no advantage over Criteria Builder approach.

Conclusion

Where Queries offer a nicer syntax and – which is imho a really great feature – get compile time checked.
Unfortunately, it lacks some advanced query features like multi sorting or projections. As Where Queries use the Criteria API under the hood, it is possible to do such things by adding them to your query using the Criteria syntax, but you are loosing compile time checks. This hopefully gets fixed with future Grails releases.
Personally, I’m going to use Where Queries whenever possible, falling back to Criteria Builder for queries that are not possible with Where Queries.

[update]
Updated “Sorting by more than one property” and “Projections”: it is possible to do such things by combining Where Queries with Criteria API. (thanks to Stephane Maldini)
[/update]

Short URL for this post: http://wp.me/p4nxik-Lz
This entry was posted in Groovy and Grails and tagged , , , , , . Bookmark the permalink.

7 Responses to Grails Database Queries – Criteria Builder vs. Where Queries [updated]

  1. To use multiple sort with detached queries (aka where queries) , you need to do :

    Book.where{}. order(‘title’, ‘asc’). order(‘isbn’, ‘asc’).list()

  2. Sönke Sothmann says:

    Thanks Stephane, I updated the article accordingly.

  3. Leo says:

    Useful article. Thanks for sharing!

  4. Pingback: An Army of Solipsists » Blog Archive » This Week in Grails (2011-51)

  5. Pingback: » Blog Archive

  6. soltesza says:

    Great article, many thanks!

    Where queries confused the hell out of me when I first encountered them (i didn’t understand why they were needed in the first place). Compile-time checking is a great feature of them.

  7. Richard Detsch says:

    What are the gradle depednecies for capabilities like findAll(query) and createCriteria()

Leave a Reply