grails 查询

grails手册的API先粘下Mark先：

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

def MyTrade = Trade.createCriteria()
        PagedResultList pagedResultList
        pagedResultList = MyTrade.list(max: params.max ?: 10, offset: params.offset ?: 0) {
            eq('status', 5 as byte)
            eq('returnStatus', 0 as byte)
            eq('type',12 as byte)
            gt("status",0 as byte)
            if (start != null) {
                gt("dateCreated", start)
            }
            if (end != null) {
                lt("dateCreated", end)
            }
            if (tradeCode)
            {
                eq('tradeId',tradeCode)
            }
            if(kdNum){
                List<MikuOrdersLogistics> kdlist=MikuOrdersLogistics.findAllByWlnumber(kdNum)
                if (kdlist){
                    'in'("tradeId", kdlist*.tradeId)
                }
            }
            if(userName){
                List<Logistics> logisticsesList=Logistics.findAllByContactNameLike("%"+userName+"%")
                if(logisticsesList){
                    'in'("consigneeId", logisticsesList*.id)
                }
            }
            if(isdf){
                if ("1".equals(isdf)){
                   not {
                       'in'("payType", [6 as byte,9 as byte,10 as byte])
                   }
                }
                else if("2".equals(isdf)){
                    'in'("payType", [6 as byte,9 as byte,10 as byte])
                }
            }
            if (StringUtils.isNotBlank(mobile)) {
                def logisticss = Logistics.findAllByMobile(mobile)
                Profile profile = Profile.findByMobile(mobile)
                if (profile && logisticss) {
                    or {
                        eq("buyerId", profile.id)
                        'in'("consigneeId", logisticss*.id)
                    }
                } else if (profile) {
                    eq('buyerId', profile.id)
                } else if (logisticss) {
                    "in"('consigneeId', logisticss*.id)
                } else {
                    eq("id", -1L)
                }
            }
            if (totalFeeOp && targetTotalFee)
            {
                List<Trade> tlist=Trade.findAllByStatus(4 as byte)
                List<Trade> moneyList=new ArrayList<Trade>()
                tlist.each {
                    Trade tobj->
                        if (totalFeeOp.equals(">") && tobj.totalFee>targetTotalFee)
                        {
                            moneyList.add(tobj)
                        }
                        else if (totalFeeOp.equals("<") && tobj.totalFee<targetTotalFee)
                        {
                            moneyList.add(tobj)
                        }
                        else if(totalFeeOp.equals("=") && tobj.totalFee==targetTotalFee)
                        {
                            moneyList.add(tobj)
                        }
                }
                if (moneyList.size())
                {
                    "in"('id', moneyList*.id)
                }
                else{
                    eq("id", -1L)
                }
            }
            order('id', 'desc')
            order('lastUpdated', 'desc')
        }

PagedResultList pagedResultList
        pagedResultList=NineLabPitemLog.createCriteria().list(max: params.max ?: 10, offset: params.offset ?: 0){
            order('lastUpdated', 'desc')
            if(prodType)
            {
                eq("prodType",Byte.parseByte(prodType))
            }
            if(status)
            {
                eq("status",Byte.parseByte(status))
            }
            if(itemName)
            {
                ilike("itemName","%" + itemName + "%")
            }
            if(itemCode)
            {
                eq("code",itemCode)
            }
        }