def criteria = Person.createCriteria() def result = criteria.list{} result.each { person -> println "First Name: ${person.firstName}" println "Last Name: ${person.lastName}" }The above code will retrieve all person records and print their details
Adding filters is simple and intuitive
def result = Person.createCriteria().list{ eq ('firstName', 'John') }
This is equivalent to the SQL:
select * from person where first_name = 'John'
We can have code inside the closure passed to the list method
def result = Person.createCriteria().list{ if (firstNameToSearch != null) { eq('firstName', firstNameToSearch) } if (lastNameToSearch != null) { eq('lastName', lastNameToSearch) } }A criterion can be added depending on certain conditions. From the code above, first name criterion will only be added when the user passed a value to firstNameToSearch. Same behavior is true with lastNameToSearch. This is excellent for building results for search forms!
def result = Person.createCriteria().list{ or { and { eq('lastName', 'Doe') gt('age', 15) } and { eq('lastName', 'Smith') gt('age', 18) } } }
Paginated results are supported by specifying max and offset
def result = Person.createCriteria().list (max:10, offset:20) { eq ('lastName', 'Doe') }
or by using firstResult and maxResults
def result = Person.createCriteria().list { eq ('lastName', 'Doe') order('lastName', 'asc') firstResult(20) maxResults(10) }
We could select which properties to include by using projections
def result = Person.createCriteria().list { projections { property('firstName') property('lastName') } } def firstPerson = result[0] def firstName = firstPerson[0] def lastName = firstPerson[1] println "First Name = ${firstName}" println "Last Name = ${lastName}"We could also use aggregate functions inside projections
def result = Person.createCriteria().list { projections { distinct('lastName') } } println "Here are the list of unique last names" result.each { lastName -> println "${lastName}" }
def result = Person.createCriteria().list { projections { avg('age') } } println "The average age is ${result[0]}"
def result = Person.createCriteria().list { projections { count() } } println "The number of rows is ${result[0]}"
def result = Person.createCriteria().list { projections { sum('price') } } println "The sum of all price ${result[0]}"
def result = Person.createCriteria().list { projections { max('age') } } println "The maximum age is ${result[0][0]}"
def result = Person.createCriteria().list { projections { min('age') } } println "The minimum age is ${result[0][0]}"