Grails Domain createCriteria Examples

Feb 07, 2015 Domain comments

This will show how to use a Grails domain's createCriteria method to search for records in the database using the criteria API.

Retrieve all records

When no criterions are passed, the criteria list will return all records with no WHERE condition.

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

Simple condition

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'

Mix with code

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!

AND OR operators

Logical operators and and or are supported.

def result = Person.createCriteria().list{ 
    or {
        and {
            eq('lastName', 'Doe')
            gt('age', 15)
        }
        and {
            eq('lastName', 'Smith')
            gt('age', 18)
        }
    }
}

Pagination

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

Projections

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

• distinct
  

  def result = Person.createCriteria().list {
      projections {
          distinct('lastName')
      }
  }
  println "Here are the list of unique last names"
  result.each { lastName ->
      println "${lastName}"
  }
  

• avg
  

  def result = Person.createCriteria().list {
      projections {
          avg('age')
      }
  }
  println "The average age is ${result[0]}"
  

• count
  

  def result = Person.createCriteria().list {
      projections {
          count()
      }
  }
  println "The number of rows is ${result[0]}"
  

• sum
  

  def result = Person.createCriteria().list {
      projections {
          sum('price')
      }
  }
  println "The sum of all price ${result[0]}"
  

• max
  

  def result = Person.createCriteria().list {
      projections {
          max('age')
      }
  }
  println "The maximum age is ${result[0][0]}"
  

• min
  

  def result = Person.createCriteria().list {
      projections {
          min('age')
      }
  }
  println "The minimum age is ${result[0][0]}"