def hqlQueryString = "from Person where 1 = 1" def hqlNamedParams = [:] if (firstNameToSearch != null) { hqlQueryString = hqlQueryString + ' and firstName = :firstNameToSearch ' hqlNamedParams= hqlNamedParams + [firstNameToSearch:firstNameToSearch] } if (lastNameToSearch != null) { hqlQueryString = hqlQueryString + ' and lastName = :lastNameToSearch ' hqlNamedParams= hqlNamedParams + [lastNameToSearch:lastNameToSearch] } def result = Person.executeQuery(hqlQueryString, hqlNamedParams)As you could see, it could be dirty and cumbersome to maintain. It is prone to errors too. Criteria is a more elegant alternative. Here is how the new code will look like:
def criteria = Person.createCriteria() def result = criteria.list { if (firstNameToSearch != null) { eq('firstName', firstNameToSearch) } if (lastNameToSearch != null) { eq('lastName', lastNameToSearch) } }The code now is more readable and easy to understand even at just one glance.
This will return all Person instances
def criteria = Person.createCriteria() def result = criteria.list{}
This will return all Person instances with first name John
def criteria = Person.createCriteria() def result = criteria.list{ eq ('firstName', 'John') }
The get method will return a single row given the closure criteria. Note that get will throw an exception when the criteria matches more than 1 row. If no row is matched, null value is returned.
This example will get the Person instance givn the driver's license.
def criteria = Person.createCriteria() def result = criteria.get{ eq ('driverslicenseNo', 'C1922-8DFG-1155') }
The scroll method will return a scrollable result set. This is useful when you need to work with large number of rows as the result set will only transfer data on as required basis.
Here is an example of iterating through all person with last name Doe.
def criteria = Person.createCriteria() def result = criteria.scroll{ eq ('lastName', 'Doe') } while (result.next()) { def person = result.get()[0] println "Hello ${person.firstName} ${person.lastName}" }
We can use and and or operators to construct complex logic.
def criteria = Person.createCriteria() def result = criteria.list { or { and { eq('lastName', 'Doe') gt('age', 15) } and { eq('lastName', 'Smith') gt('age', 18) } } }
We can perform pagination by passing offset and max parameters. The max parameter is the maximum number of rows to be returned while the offset parameter is the number of rows to skip before retrieving the first result.
If we wish to retrieve 10 rows at a time, here is the code for retrieving the first page:
def criteria = Person.createCriteria() def result = criteria.list (max:10, offset:0) { eq ('firstName', 'John') }
And here is the code for retrieving the second page. Note that the only difference is the value for offset.
def criteria = Person.createCriteria() def result = criteria.list (max:10, offset:0) { eq ('firstName', 'John') }
Here is an alternative way of retrieving the first and second page using firstResult and maxResults
def criteria = Person.createCriteria() def result = criteria.list { eq ('firstName', 'John') firstResult(0) maxResults(10) }
def criteria = Person.createCriteria() def result = criteria.list { eq ('firstName', 'John') firstResult(10) maxResults(10) }
It is highly recommended to have a sort by clause when doing pagination. Here is an example on how to sort by last name:
def criteria = Person.createCriteria() def result = criteria.list { eq ('firstName', 'John') order('lastName', 'asc') firstResult(0) maxResults(10) }
def criteria = Person.createCriteria() def result = criteria.list { projections { property('firstName') property('lastName') } }
Instead of having a list of person instances, it will return a 2 dimensional list. Here is the example code on how to use the result data:
def firstPerson = result[0] def firstName = firstPerson[0] def lastName = firstPerson[1] println "First Name = ${firstName}" println "Last Name = ${lastName}"
Similar to SQL, it is also possible to use aggregate functions inside criteria.
def criteria = Person.createCriteria() def result = criteria.list { projections { distinct('lastName') } } println "Here are the list of unique last names" result.each { lastName -> println "${lastName}" }
def criteria = Person.createCriteria() def result = criteria.list { projections { avg('age') } } println "The average age is ${result[0]}"
def criteria = Person.createCriteria() def result = criteria.list { projections { count() } } println "The number of rows is ${result[0]}"
def criteria = Purchase.createCriteria() def result = criteria.list { projections { sum('price') } } println "The sum of all price ${result[0]}"
def criteria = Person.createCriteria() def result = criteria.list { projections { max('age') min('age') } } println "The maximum age is ${result[0][0]}" println "The minimum age is ${result[0][1]}"
def criteria = Person.createCriteria() def result = criteria.list { eq('lastName', 'Doe') ge('age', 13) le('age', 17) }
def criteria = Person.createCriteria() def result = criteria.list { eqProperty('firstName', 'lastName') }
def criteria = Person.createCriteria() def result = criteria.list { isNull('firstName') }