Grails Tutorial for Beginners - Criteria API

Jan 26, 2015 Beginner comments

This tutorial will show how to use Criteria API to search data from the database. Criteria is a powerful API and an elegant alternative to HQL.

Introduction

This previous post showed that HQL is a powerful way to perform complex queries. However, it is not very elegant if an HQL statement is not fixed and needs to be concatenated. For example: if we need to query the database only if a parameter is not null, our code will look like this:

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.

Criteria methods

Here are the available methods for a criteria instance

list

The list method will return all matching rows of the given closure criteria.

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

get

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

scroll

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

AND OR Operators

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

Pagination

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

Projection

It is possible to control the resulting columns of a query. For example:

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

Aggregate Functions

Similar to SQL, it is also possible to use aggregate functions inside criteria.

distinct - here is an example on how to retrieve all unique last names in the database

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

avg - here is an example on how to get the average age of all people

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

count - here is an example on how to get the number of records in the person table

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

sum - here is an example on how to use sum

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

max and min - here is an example of how to use max and min

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]}"

Other Criterion Examples

Here are other examples on how to filter results.

Property value comparison - a domain property can be compared to a particular value. Here are some related criterions:eq, gt, ge, lt, le and ne.
Here is a sample code that list all teenagers with surname Doe:
```
def criteria = Person.createCriteria()
def result = criteria.list {
    eq('lastName', 'Doe')
    ge('age', 13)
    le('age', 17)
}
```
Property to property comparison - a domain property can be compared to another property. Here are some related criterions:eqProperty, gtProperty, geProperty, ltProperty, leProperty and neProperty.
Here is a sample code that list all person whose first name is the same as their last name:
```
def criteria = Person.createCriteria()
def result = criteria.list {
    eqProperty('firstName', 'lastName')
}
```
Test null value - isNull and isNotNull can be used to check if a domain property is null or not. Here is an example that list all person that are encoded without first name:
```
def criteria = Person.createCriteria()
def result = criteria.list {
    isNull('firstName')
}
```

For other criterion, refer to the latest Grails doc.

List of Tutorials for Beginners

Beginners Tutorial

Example Applications

If you like my posts, like my Facebook page please :)

Links