The Grails Hibernate Dynamica/On Fly CriteriaBuilder

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