Grails createCriteria 查询

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值