{"id":36288,"date":"2015-02-02T07:00:01","date_gmt":"2015-02-02T05:00:01","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=36288"},"modified":"2015-02-01T20:43:49","modified_gmt":"2015-02-01T18:43:49","slug":"grails-tutorial-for-beginners-criteria-api","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2015\/02\/grails-tutorial-for-beginners-criteria-api.html","title":{"rendered":"Grails Tutorial for Beginners – Criteria API"},"content":{"rendered":"

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.<\/p>\n

Introduction<\/h2>\n

This previous post<\/a> 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:
\n 
\n <\/p>\n

def hqlQueryString = \"from Person where 1 = 1\"\r\ndef hqlNamedParams = [:]\r\nif (firstNameToSearch != null) {\r\n    hqlQueryString = hqlQueryString + ' and firstName = :firstNameToSearch '\r\n    hqlNamedParams= hqlNamedParams + [firstNameToSearch:firstNameToSearch]\r\n}\r\nif (lastNameToSearch != null) {\r\n    hqlQueryString = hqlQueryString + ' and lastName = :lastNameToSearch '\r\n    hqlNamedParams= hqlNamedParams + [lastNameToSearch:lastNameToSearch]\r\n}\r\ndef result = Person.executeQuery(hqlQueryString, hqlNamedParams)<\/pre>\n
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:<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    if (firstNameToSearch != null) {\r\n        eq('firstName', firstNameToSearch)\r\n    }\r\n    if (lastNameToSearch != null) {\r\n        eq('lastName', lastNameToSearch)\r\n    }\r\n}<\/pre>\n
The code now is more readable and easy to understand even at just one glance.<\/p>\n
Criteria methods<\/h2>\n
Here are the available methods for a criteria instance <\/p>\n
list<\/h3>\n
The list<\/b> method will return all matching rows of the given closure criteria.<\/p>\n
This will return all Person instances<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list{}<\/pre>\n
This will return all Person instances with first name John<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list{ \r\n    eq ('firstName', 'John')\r\n}<\/pre>\n
get<\/h3>\n
The get<\/b> method will return a single row given the closure criteria. Note that get<\/b> will throw an exception when the criteria matches more than 1 row. If no row is matched, null value is returned.<\/p>\n
This example will get the Person instance givn the driver’s license.<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.get{\r\n    eq ('driverslicenseNo', 'C1922-8DFG-1155')\r\n}<\/pre>\n
scroll<\/h3>\n
The scroll<\/b> 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.
 
<\/div> <\/div><\/p>\n
Here is an example of iterating through all person with last name Doe.<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.scroll{\r\n    eq ('lastName', 'Doe')\r\n}\r\nwhile (result.next()) {\r\n    def person = result.get()[0]\r\n    println \"Hello ${person.firstName} ${person.lastName}\"\r\n}<\/pre>\n
AND OR Operators<\/h2>\n
We can use and<\/b> and or<\/b> operators to construct complex logic.<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    or {\r\n        and {\r\n            eq('lastName', 'Doe')\r\n            gt('age', 15)\r\n        }\r\n        and {\r\n            eq('lastName', 'Smith')\r\n            gt('age', 18)\r\n        }\r\n    }\r\n}<\/pre>\n
Pagination<\/h2>\n
We can perform pagination by passing offset<\/b> and max<\/b> parameters. The max<\/b> parameter is the maximum number of rows to be returned while the offset<\/b> parameter is the number of rows to skip before retrieving the first result.<\/p>\n
If we wish to retrieve 10 rows at a time, here is the code for retrieving the first page:<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list (max:10, offset:0) {\r\n    eq ('firstName', 'John')\r\n}<\/pre>\n
And here is the code for retrieving the second page. Note that the only difference is the value for offset.<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list (max:10, offset:0) {\r\n    eq ('firstName', 'John')\r\n}<\/pre>\n
Here is an alternative way of retrieving the first and second page using firstResult<\/b> and maxResults<\/b><\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    eq ('firstName', 'John')\r\n    firstResult(0)\r\n    maxResults(10)\r\n}<\/pre>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    eq ('firstName', 'John')\r\n    firstResult(10)\r\n    maxResults(10)\r\n}<\/pre>\n
It is highly recommended to have a sort by<\/b> clause when doing pagination. Here is an example on how to sort by last name:<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    eq ('firstName', 'John')\r\n    order('lastName', 'asc')\r\n    firstResult(0)\r\n    maxResults(10)\r\n}<\/pre>\n
Projection<\/h2>\n
It is possible to control the resulting columns of a query. For example:<\/p>\n
def criteria = Person.createCriteria()\r\ndef result = criteria.list {\r\n    projections {\r\n        property('firstName')\r\n        property('lastName')\r\n    }\r\n}<\/pre>\n
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:<\/p>\n
def firstPerson = result[0]\r\ndef firstName = firstPerson[0]\r\ndef lastName = firstPerson[1]\r\nprintln \"First Name = ${firstName}\"\r\nprintln \"Last Name = ${lastName}\"<\/pre>\n
Aggregate Functions<\/h2>\n
Similar to SQL, it is also possible to use aggregate functions inside criteria.<\/p>\n