The HibernateCriteriaBuilder is a great Grails feature, that you should know when working with the Grails framework. It is an alternative way to create dynamic or static queries with a groovy syntax based on the Criteria API of Hibernate.
Although there is some documentation about it in the Grails Pages, this does not demonstrate some advanced features like joins or pagination. There are several blog posts about the HibernateCriteriaBuilder, but most of them only show a simple example. I want to share my experience with a more complex example that demonstrates some features like:
def example = new Parcel(parcelNumber: '1234%');
http://www.viaboxxsystems.de/the-grails-hibernatecriteriabuilder
Although there is some documentation about it in the Grails Pages, this does not demonstrate some advanced features like joins or pagination. There are several blog posts about the HibernateCriteriaBuilder, but most of them only show a simple example. I want to share my experience with a more complex example that demonstrates some features like:
- query attributes of associated entities using joins
- pagination parameters and their consequences
- reuse of the criteria definition for similar domain classes
Query attributes of associated entities using joins
The example code uses a parcel instance as an example to carry parameter values to construct the criteria.def example = new Parcel(parcelNumber: '1234%'); def crit = Parcel.createCriteria() def parcels = crit.list { ilike("parcelNumber", example.parcelNumber) gt("parcelPrice", example.parcelPrice) recipient { ilike("lastName", example.recipient.lastName) } depot { address { ilike("city", example.depot.address.city) ilike("postalCode", example.depot.address.postalCode) } } }
Dynamic query parameters
We extend the example to include the filtering only when the value of the example object is not empty.def example = new Parcel(parcelNumber: '1234%');
def crit = Parcel.createCriteria() def parcels = crit.list { if (example.parcelNumber) ilike("parcelNumber", example.parcelNumber) if (example.parcelPrice) gt("parcelPrice", example.parcelPrice) if (example.recipient) { recipient { if (example.recipient?.lastName) { ilike("lastName", example.recipient.lastName) } } } if (example.depot?.address) { depot { address { if (example.depot.address.city) { ilike("city", example.depot.address.city) } if (example.depot.address.postalCode) { ilike("postalCode", example.depot.address.postalCode) } } } } }
Reuse of the criteria definition
We have 2 domain classes with the same properties: Parcel and ParcelArchive. We want to reuse the criteria definition, but we must be careful, because extracting it with your IDE ("extract method" refactoring) could break your code. You can use the groovy with.{closure} to extract the criteria:// search Parcel def example = new Parcel(parcelNumber: '1234%'); def crit = Parcel.createCriteria() def parcels = crit.list { buildSearchCriteria(example, crit) } // search ParcelArchive def example = new ParcelArchive(parcelNumber: '1234%'); def crit = ParcelArchive.createCriteria() def parcels = crit.list { buildSearchCriteria(example, crit) } // criteria method for both private static void buildSearchCriteria(def example, HibernateCriteriaBuilder crit) { crit.with { // make the criteriaBuilder the receiver, because the definitions have been extracted a method if (example.parcelNumber) { ilike("parcelNumber", example.parcelNumber) if (example.parcelPrice) { gt("parcelPrice", example.parcelPrice) } if (example.recipient) { recipient { if (example.recipient?.lastName) { ilike("lastName", example.recipient.lastName) } } } if (example.depot?.address) { depot { address { if (example.depot.address.city) i { like("city", example.depot.address.city) } if (example.depot.address.postalCode) { ilike("postalCode", example.depot.address.postalCode) } } } } } } }
Check and create criteria dynamically
package com.pritom
import grails.orm.HibernateCriteriaBuilder import org.codehaus.groovy.grails.commons.GrailsApplication class DataService {
/** * Now if you want to search schoolName against a Student you
* have to something like below: */ def searchStudentBySchoolName() { def key = "discpline.school.name"; def value = "Some Value"; def criteria = Student.createCriteria(); criteria.list { buildSearchCriteria(criteria, key.trim("."), value); } } /** * isDomainClass() ? Somehow check that the name is a domain class * Otherwise it is a property in the domain. * * Implement getDomainClassByName() to get the domain class dynamically */ def buildSearchCriteria(HibernateCriteriaBuilder criteria, String[] fields, String value) { criteria.with { for (Integer index2 = 0; index2 < fields.size() - 1; index2++) { if(fields[index2] != null && fields[index2 + 1] != null && isDomainClass(fields[index2]) && !isDomainClass(fields[index2 + 1])) { def domainClass = getDomainClassByName(fields[index2].getMetaClass().getTheClass()); def domainCriteria = domainClass.createCriteria(); 'in' (fields[index2], domainCriteria.list { like(fields[index2 + 1], "%" + value + "%") }); } else if(fields[index2] != null && fields[index2 + 1] != null && isDomainClass(fields[index2]) && isDomainClass(fields[index2 + 1])) { def domainClass = getDomainClassByName(fields[index2]).getMetaClass().getTheClass(); String current2 = fields[index2]; fields[index2] = null; def domainCriteria = domainClass.createCriteria(); 'in' (current2, domainCriteria.list{ buildSearchCriteria(domainCriteria, fields, value) }); } } } } }
/* The domain classes are below: */
class Student { Integer id; String studentId; Integer age; det belongsTo = [ discipline: Discipline ] }
class Discipline { Integer id; String disciplineName;
def belongsTo = [ school: School ] } class School { Integer id; String schoolName; }
http://www.viaboxxsystems.de/the-grails-hibernatecriteriabuilder