createCriteria

Purpose

Creates an returns an instance of Grails' HibernateCriteriaBuilder that can be used to construct criteria queries

Examples

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

Description

Criteria queries are a type safe, advanced way to query that uses a Groovy builder to construct potentially complex queries. It is a much better alternative to using StringBuffer.

Refer to the user guide section on Criteria for usage instructions. Below is a node reference for each criterion method:

NodeDescriptionExample
betweenWhere the property value is between to distinct values
between("balance", 500, 1000)
eqWhere a property equals a particular value
eq("branch", "London")
eqPropertyWhere one property must equal another
eqProperty("lastTransaction","firstTransaction")
gtWhere a property is greater than a particular value
gt("balance",1000)
gtPropertyWhere a one property must be greater than another
gtProperty("balance","overdraft")
geWhere a property is greater than or equal to a particular value
ge("balance",1000)
gePropertyWhere a one property must be greater than or equal to another
geProperty("balance","overdraft")
idEqWhere an objects id equals the specified value
idEq(1)
ilikeA case-insensitive 'like' expression
ilike("holderFirstName","Steph%")
inWhere 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])
isEmptyWhere a collection property is empty
isEmpty("transactions")
isNotEmptyWhere a collection property is not empty
isNotEmpty("transactions")
isNullWhere a property is null
isNull("holderGender")
isNotNullWhere a property is not null
isNotNull("holderGender")
ltWhere a property is less than a particular value
lt("balance",1000)
ltPropertyWhere a one property must be less than another
ltProperty("balance","overdraft")
leWhere a property is less than or equal to a particular value
le("balance",1000)
lePropertyWhere a one property must be less than or equal to another
leProperty("balance","overdraft")
likeEquivalent to SQL like expression
like("holderFirstName","Steph%")
neWhere a property does not equals a particular value
ne("branch", "London")
nePropertyWhere one property does not equal another
neProperty("lastTransaction","firstTransaction")
orderOrder the results by a particular property
order("holderLastName", "desc")
sizeEqWhere a collection property's size equals a particular value
sizeEq("transactions", 10)
sizeGtWhere a collection property's size is greater than a particular value
sizeGt("transactions", 10)
sizeGeWhere a collection property's size is greater than or equal to a particular value
sizeGe("transactions", 10)
sizeLtWhere a collection property's size is less than a particular value
sizeLt("transactions", 10)
sizeLeWhere a collection property's size is less than or equal to a particular value
sizeLe("transactions", 10)
sizeNeWhere a collection property's size is not equal to a particular value
sizeNe("transactions", 10)

Criteria also support the notion of projections. A projection is used to change the nature of the results. For example the following query using a projection to count the number of distinct branch names there are for each Account:

def c = Account.createCriteria()
def branchCount = c.get {
	projections {
		countDistinct "branch"
	}
}

The following table summarizes the different projections and what they do:

NameDescriptionExample
propertyReturns the given property in the returned results
property("firstName")
distinctReturns results using a single or collection of distinct property names
distinct("lastName") or distinct(['firstName', 'lastName'])
avgReturns the average value of the given property
avg("age")
countReturns the count of the given property name
count("branch")
countDistinctReturns the distinct count of the given property name
countDistinct("branch")
groupPropertyGroups the results by the given property
groupProperty("lastName")
maxReturns the maximum value of the given property
max("age")
minReturns the minimum value of the given property
min("age")
sumReturns the sum of the given property
sum("balance")
rowCountReturns count of the number of rows returned
rowCount()