Grails - Hibernate Criteria Builder

Hibernate Criteria Builder

Description

A builder for creating criteria-based queries analogous to those found in the Hibernate Criteria API, the nodes on this builder map the the static methods found in the Restrictions class of the Hibernate Criteria API. Example Usage:

def c = Account.createCriteria()
def results = c {
	like("holderFirstName", "Fred%")
	and {
		between("balance", 500, 1000)
		eq("branch", "London")
	}
	maxResults(10)
	order("holderLastName", "desc")
}

As you can see, the mapping from Groovy "camel case" to the database's naming scheme (usually underscored instead of camel case) is handled transparently for you.

Setting properties in the Criteria instance

If a node within the builder tree doesn't match a particular criterion it will attempt to set a property on the Criteria object itself. Thus allowing full access to all the properties in this class. The below example calls "setMaxResults" and "setFirstResult" on the Criteria instance:

import org.hibernate.FetchMode as FM
....
def results = c.list {
	maxResults(10)
	firstResult(50)
	fetchMode("aRelationship", FM.EAGER)
}

Querying Associations

Associations can be queried by having a node that matches the property name. For example say the Account class had many Transaction objects:

class Account {
    …
    def hasMany = [transactions:Transaction]
    Set transactions
    …
}

We can query this association by using the property name "transaction" as a builder node:

def c = Account.createCriteria()
def now = new Date()
def results = c.list {
       transactions {
            between('date',now-10, now)
       }
}

The above code will find all the accounts that have performed transactions within the last 10 days. You can also nest such association queries within logical blocks:

def c = Account.createCriteria()
def now = new Date()
def results = c.list {
     or {
        between('created',now-10,now)
        transactions {
             between('date',now-10, now)
        }
     }
}

Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days.

Querying with Projections

Projections to be used to customise the results. To use projections you need to define a "projections" node within the criteria builder tree. There are equivalent methods within the projections node to the methods found in the Hibernate Projections class:

def c = Account.createCriteria()def numberOfBranches = c.get {
	projections {
		countDistinct('branch')
	}
}

Using Scrollable Results

You can use Hibernate's ScrollableResults feature by calling the scroll method:

def results = crit.scroll {
      maxResults(10)
}
def f = results.first()
def l = results.last()
def n = results.next()
def p = results.previous()def future = results.scroll(10)
def accountNumber = results.getLong('number')

To quote the documentation of Hibernate ScrollableResults:

A result iterator that allows moving around within the results by arbitrary increments. The Query / ScrollableResults pattern is very similar to the JDBC PreparedStatement/ ResultSet pattern and the semantics of methods of this interface are similar to the similarly named methods on ResultSet.
Contrary to JDBC, columns of results are numbered from zero.

Method Reference

If you invoke the builder with no method name such as:

c { … }

defaults to

c.list { … }

Method	Description
list	This is the default method. It returns all matching rows.
get	Returns a unique result set, i.e. just one row. The criteria has to be formed that way, that it only queries one row. This method is not to be confused with a limit to just the first row.
scroll	Returns a scrollable result set
listDistinct	If subqueries or associations are used, one may end up with the same row multiple times in the result set. In Hibernate one would do a "CriteriaSpecification.DISTINCT_ROOT_ENTITY". In grails one can do it even simpler by just using this method.

Node Reference

Node	Description	Example
and	Logical AND operator	and {
between("balance", 500, 1000)
eq("branch", "London")
}
between	Where the property value is between to distinct values	between("balance", 500, 1000)
eq	Where a property equals a particular value	eq("branch", "London")
eqProperty	Where one property must equal another	eqProperty("lastTransaction","firstTransaction")
gt	Where a property is greater than a particular value	gt("balance",1000)
gtProperty	Where a one property must be greater than another	gtProperty("balance","overdraft")
ge	Where a property is greater than or equal to a particular value	ge("balance",1000)
geProperty	Where a one property must be greater than or equal to another	geProperty("balance","overdraft")
idEq	Where an objects id equals the specified value	idEq(1)
ilike	A case-insensitive 'like' expression	ilike("holderFirstName","Steph%")
in	Where a one property is contained within the specified list of values
note: 'in' is a groovy reserve word, we must escape it by quotes.	'in'("holderAge",[18..65])
isEmpty	Where a collection property is empty	isEmpty("transactions")
isNotEmpty	Where a collection property is not empty	isNotEmpty("transactions")
isNull	Where a property is null	isNull("holderGender")
isNotNull	Where a property is not null	isNotNull("holderGender")
lt	Where a property is less than a particular value	lt("balance",1000)
ltProperty	Where a one property must be less than another	ltProperty("balance","overdraft")
le	Where a property is less than or equal to a particular value	le("balance",1000)
leProperty	Where a one property must be less than or equal to another	leProperty("balance","overdraft")
like	Equivalent to SQL like expression	like("holderFirstName","Steph%")
ne	Where a property does not equals a particular value	ne("branch", "London")
neProperty	Where one property does not equal another	neProperty("lastTransaction","firstTransaction")
not	Negates an expression, logical NOT	not {
between("balance", 500, 1000)
}
or	Logical OR operator	or {
between("balance", 500, 1000)
eq("branch", "London")
}
order	Order the results by a particular property	order("holderLastName", "desc")
sizeEq	Where a collection property's size equals a particular value	sizeEq("transactions", 10)

Samples

Here is a sample of a search function inside of a controller:

def search = {
            if (request.method == 'POST'){
                def criteria = Network.createCriteria();                def results = criteria {
                    and{
                        if(params.licenseId && params.licenseId != ''){
                            eq("licenseId",Integer.parseInt(params.licenseId))
                        }
                        if(params.initialName && params.initialName != ''){
                            like("initialName", '%' + params.initialName + '%')
                        }
                        if(params.country.id){
                            eq("country.id",Long.parseLong(params.country.id))
                        }
                        if(params.defaultOperator.id){
                            eq("defaultOperator.id",Long.parseLong(params.defaultOperator.id))
                        }
                        if(params.networkStartDate_year && params.networkStartDate_month && params.networkStartDate_day){
                            String input = "${params.networkStartDate_year}/${params.networkStartDate_month}/${params.networkStartDate_day}"
                            Date inputDate = new SimpleDateFormat("yyyy/MM/dd")
                                .parse(input)
                            between("networkStartDate",inputDate - 10, inputDate + 10)   
                        }
                        if(params.technologyType){
                            like("technologyType", params.technologyType)
                        }
                    }
                    firstResult(20)
                    maxResults(20)
                    order("licenseId")
                }
                render(view:'list',model:[networkList : results])
            }
    }