Grails指南24查询高阶

import grails.gorm.*

...

def criteria = new DetachedCriteria(Person)

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

def bartQuery = criteria.build {

eq 'firstName', 'Bart'

}

########################

方法，描述

list，列出所有已匹配结果

get，返回第一个匹配结果

count，返回所有匹配结果总数

exists，如果存在匹配记录，返回true

deleteAll，删除所有匹配记录

updateAll(Map)，使用Map中的值更新匹配记录中相应的值

########################

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

def results = criteria.list(max:4, sort:"firstName")

def results = criteria.list(max:4, sort:"firstName") {

gt 'age', 30

}

Person p = criteria.find() // or criteria.get()

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

criteria.each {

println it.firstName

}

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

def bart = criteria.findByFirstName("Bart")

########################

def results = Person.withCriteria {

gt "age", new DetachedCriteria(Person).build {

projections {

avg "projections"

}

}

order "firstName"

}

由于同属Person，因此可简写为：

def results = Person.withCriteria {

gt "age", {

projections {

avg "age"

}

}

order "firstName"

}

projections投射，确保了只有一个结果返回，即年龄平均值。其他情况如：

def results = Person.withCriteria {

gtAll "age", {

projections {

property "age"

}

between 'age', 18, 65

}

order "firstName"

}

########################

方法，描述

gtAll，大于所有子查询结果

geAll，大于等于所有子查询结果

ltAll，小于所有子查询结果

leAll，小于等于所有子查询结果

eqAll，等于所有子查询结果

neAll，不等于所有子查询结果

########################

批量更新及批量删除：

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

int total = criteria.updateAll(lastName:"Bloggs")

def criteria = new DetachedCriteria(Person).build {

eq 'lastName', 'Simpson'

}

int total = criteria.deleteAll()

########################

HQL:

def results = Book.findAll("from Book as b where b.title like 'Lord of the%'")

def results = Book.findAll("from Book as b " + "where b.title like :search or b.author like :search", [search: "The Shi%"])

def author = Author.findByName("Stephen King")

def books = Book.findAll("from Book as book where book.author = :author", [author: author])

def results = Book.findAll("from Book as b where " +

"b.title like 'Lord of the%' " +

"order by b.title asc",

[max: 10, offset: 20])