difference between Criteria Query(createCriteraia, withCriteria) and where Queries.

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

Posted on 21. December 2011 by 

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:

1 2 3 4

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).

1 2 3 4 5 6 7 8 9 10 11 12

// 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:

1

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.

1 2 3 4 5 6 7 8 9 10 11 12

// 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:

1

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.

1 2 3 4 5 6 7 8 9

// 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:

1

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.

1 2 3 4 5 6 7 8 9 10 11 12

// 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.

Change locale of grails application

For change the locale of any grails application, means change the printing message language, we just write these 3 files in resources.groovy

beans = {
   localeResolver(org.springframework.web.servlet.i18n.SessionLocaleResolver) {
      defaultLocale = new Locale("de","DE")
      java.util.Locale.setDefault(defaultLocale)
   }
}

difference between render and respond

First things that comes in mind

• Respond was introduced in Grails v2.3.
• Render is used by Grails to render different forms of responses from simple text response, to views to templates.
• Respond automatically attempts to return the most appropriate type for the requested content type.
• Respond is a better version of render, and can do everything render does.

Example

In case you want to send data to the view in "xml" or in "json" format then you will have to do the following

• render : // renders text for a specified content-type/encoding
  render(text: "some xml", contentType: "text/xml", encoding: "UTF-8"
  render(text: "some json", contentType: "text/json", encoding: "UTF-8"
• respond :  by using respond, it will automatically select the correct return type
respond( text: "some text in json or xml" [formats : ['xml' , 'json']]) it will automatically send the required respond depending upon the type of request.