createCriteria
Purpose
Creates and 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"
) }
In order to use pagination you would have to make another query to retrieve the total number of matching results. A better way is to pass the necessary pagination values as arguments to the criteria method as shown below:
def c = Account.createCriteria() def results = c.list (max: 10, offset: 10) { like(
"holderFirstName", "Fred%") and { between("balance", 500, 1000) eq("branch","London") } order("holderLastName", "desc"
) }
This will return a
PagedResultList
which has a getTotalCount() method to return the total number of matching records for pagination.
println "Rendering ${results.size()} Accounts of ${results.totalCount}"
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.
Method reference:
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.
|
The
listDistinct()
method does not work well with the pagination options
maxResult
and
firstResult
. If you need distinct results with pagination, we currently recommend that you use HQL. You can find out more information from
this blog post
.
If you invoke the builder with no method name like so
c { … }
the
list()
method will be invoked automatically. In other words, it's the equivalent of
c.list { … }
Below is a node reference for each criterion method:
Node
|
Description
|
Example
|
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")
|
eq (case-insensitive)
|
A version of eq that supports an optional 3rd Map parameter to specify that the query be case-insensitive.
|
eq("branch", "london", [ignoreCase: true])
|
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 property is contained within the specified list of values. Can also be chained with the not method where a property is not contained within the specified list of values. Note: 'in' is a groovy reserve word, so it must be escaped by quotes.
|
'in'("holderAge",[18..65]) or not{'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")
|
order
|
Order the results by a particular property
|
order("holderLastName", "desc")
|
rlike
|
Similar to like, but uses a regex. Only supported on Oracle and MySQL.
|
rlike("holderFirstName",/Steph.+/)
|
sizeEq
|
Where a collection property's size equals a particular value
|
sizeEq("transactions", 10)
|
sizeGt
|
Where a collection property's size is greater than a particular value
|
sizeGt("transactions", 10)
|
sizeGe
|
Where a collection property's size is greater than or equal to a particular value
|
sizeGe("transactions", 10)
|
sizeLt
|
Where a collection property's size is less than a particular value
|
sizeLt("transactions", 10)
|
sizeLe
|
Where a collection property's size is less than or equal to a particular value
|
sizeLe("transactions", 10)
|
sizeNe
|
Where a collection property's size is not equal to a particular value
|
sizeNe("transactions", 10)
|
sqlRestriction
|
Use arbitrary SQL to modify the resultset
|
sqlRestriction "char_length( first_name ) = 4"
|
With dynamic finders, you have access to options such as 'max', 'sort', etc. These are available to criteria queries as well, but they are named differently:
Name
|
Description
|
Example
|
order(string, string)
|
Specifies both the sort column (the first argument) and the sort order (either 'asc' or 'desc').
|
order "age","desc"
|
firstResult(int)
|
Specifies the offset for the results. A value of 0 will return all records up to the maximum specified.
|
firstResult 20
|
maxResults(int)
|
Specifies the maximum number of records to return.
|
maxResults 10
|
cache(boolean)
|
Tells Hibernate whether to cache the query or not (if the query cache is enabled).
|
cache true
|
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:
Name
|
Description
|
Example
|
property
|
Returns the given property in the returned results
|
property("firstName")
|
distinct
|
Returns results using a single or collection of distinct property names
|
distinct("lastName") or distinct(['firstName', 'lastName'])
|
avg
|
Returns the average value of the given property
|
avg("age")
|
count
|
Returns the count of the given property name
|
count("branch")
|
countDistinct
|
Returns the distinct count of the given property name
|
countDistinct("branch")
|
groupProperty
|
Groups the results by the given property
|
groupProperty("lastName")
|
max
|
Returns the maximum value of the given property
|
max("age")
|
min
|
Returns the minimum value of the given property
|
min("age")
|
sum
|
Returns the sum of the given property
|
sum("balance")
|
rowCount
|
Returns count of the number of rows returned
|
rowCount()
|
def query = {
if(commodityName){
like("commodityName","%${commodityName}%")
}
if(commodityCode){
eq("commodityCode",commodityCode)
}
order("commoditySpell", "desc")
}
//TODO 查询分页
params.offset=pageStart;
params.max =pageNum;
def medicinalBasicList = MedicinalBasic.createCriteria().list(params,query);
if(commodityName){
like("commodityName","%${commodityName}%")
}
if(commodityCode){
eq("commodityCode",commodityCode)
}
order("commoditySpell", "desc")
}
//TODO 查询分页
params.offset=pageStart;
params.max =pageNum;
def medicinalBasicList = MedicinalBasic.createCriteria().list(params,query);