If you are new to using Criteria, you may read these previous post on createCriteria method and Criteria API.
This is a sample code that uses criteria api to retrieve all records but without using projections:
def criteria = Person.createCriteria() def result = criteria.list{}
This is a sample code that uses criteria api to retrieve records with filtering (WHERE Clause) but still without using projections:
def result = Person.createCriteria().list{ eq ('firstName', 'John') }
We could select which columns or properties to include by using projections in your criteria code:
Here is the counterpart of the sample codes above but this time using projections:
def result = Person.createCriteria().list { projections { property('firstName') property('lastName') } }
def result = Person.createCriteria().list { projections { property('firstName') property('lastName') } eq ('firstName', 'John') }
We can also use aggregate functions inside Grails Criteria Projections.
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]}"