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:
- 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
