If you implement search form functionality using Hibernate Criteria Builder in Grails then the resulting code usually looks something like this:
def criteria = Account.createCriteria();
def results = criteria {
if (seachCmd.accountNo !=null && searchCmd.accountNo != ''){
eq("accountNo", searchCmd.accountNo)
}
if (searchCmd.ownerName != null && searchCmd.ownerName != '') {
owner {
ilike("name", "${searchCmd.ownerName}%")
}
}
}
The if-not-null check needs to be repeated for every search field. Inspired by this blog http://www.zorched.net/2009/09/02/drying-grails-criteria-queries/ you can implement some additional methods for HibernateCriteriaBuilder which simplify this:
HibernateCriteriaBuilder.metaClass.whenNotNullIlike = {field, value ->
if (value != null && value != '') {
ilike(field, "$value%")
}
}
HibernateCriteriaBuilder.metaClass.whenNotNullEq = {field, value ->
if (value != null && value !='') {
eq(field, value)
}
}
So now the resulting criteria building code will look like this:
def criteria = Account.createCriteria();
def results = criteria {
whenNotNullEq("accountNo", searchCmd.accountNo)
owner {
whenNotNullIlike("name", searchCmd.ownerName)
}
}
Of course if you include the association (owner in this case) then additional join will be done even if no conditions for this association are checked. So resulting query is not that optimal.