The Grails HibernateCriteriaBuilder

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

Example Domain

We want to query for parcels by filtering some of the parcel's attributes: parcelNumber, parcelPrice and
by some of the attributes of the associated Recipient (the person who gets the parcel) and the associated Depot (where the parcel is kept).

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

Pagination

Calling the list-Method without parameters, returns an ArrayList<Parcel> with all parcels that match the query criteria. Hibernate executes a kind of "SELECT p.* FROM Parcel p" statement.

When the application needs to restrict the maximum number of results returned and has to support paging, there is another variant of the list method that expects a Map of paginate parameters:

def parcels = crit.list('max':100,'sort':'parcelNumber','order':'asc') {...}

Pagination Parameters:

• max - The maximum number to list
• offset - The offset from the first result to list from
• order - The order to list by, either "desc" or "asc"
• sort - The property name to sort by
• ignoreCase - Whether to ignore the case when sorting. Default is true.
• fetch - The fetch policy for the object's associations as a Map

Consequences:

• Hibernate executes 2 queries: SELECT COUNT(*) to retrieve the totalCount and another SELECT to load the objects
• The result of the list(Map) method is an instance of PagedResultList, that implements List and offers a 'totalCount' property

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) ilike("city", example.depot.address.city)
             if(example.depot.address.postalCode) ilike("postalCode", example.depot.address.postalCode)
           }
        }
      }
    }
  }
}