领域类之GORM查询

GORM支持一下查询

动态查询

Where查询

条件查询

Hibernate查询语言（HQL）

先讲两个基础查询：

list

def books = Book.list()

def books = Book.list(offset:10, max:20)

def books = Book.list(sort:"title", order:"asc")

根据ID查询

def book = Book.get(23)

def books = Book.getAll(23, 93, 81)

动态查询

class Book {    String title    Date releaseDate    Author author}

class Author {    String name}

通过findBy和findAllBy按照一定方法表达式来查询

def book = Book.findByTitle("The Stand")
book = Book.findByTitleLike("Harry Pot%")
book = Book.findByReleaseDateBetween(firstDate, secondDate)
book = Book.findByReleaseDateGreaterThan(someDate)
book = Book.findByTitleLikeOrReleaseDateLessThan("%Something%", someDate)

方法表达式

Book.findBy([Property][Comparator][Boolean Operator])?[Property][Comparator]

说明：? 可选择性

comparators 比较器类型：

• InList - In the list of given values
• LessThan - less than a given value
• LessThanEquals - less than or equal a give value
• GreaterThan - greater than a given value
• GreaterThanEquals - greater than or equal a given value
• Like - Equivalent to a SQL like expression
• Ilike - Similar to a Like, except case insensitive
• NotEqual - Negates equality
• Between - Between two values (requires two arguments)
• IsNotNull - Not a null value (doesn't take an argument)
• IsNull - Is a null value (doesn't take an argument)

def now = new Date()def lastWeek = now - 7def book = Book.findByReleaseDateBetween(lastWeek, now)
books = Book.findAllByReleaseDateIsNull()books = Book.findAllByReleaseDateIsNotNull()

布尔逻辑(AND/OR)

def books = Book.findAllByTitleLikeAndReleaseDateGreaterThan(                      "%Java%", new Date() - 30)

def books = Book.findAllByTitleLikeOrReleaseDateGreaterThan(                      "%Java%", new Date() - 30)

关联查询

def author = Author.findByName("Stephen King")
def books = author ? Book.findAllByAuthor(author) : []

分页和排序

def books = Book.findAllByTitleLike("Harry Pot%",               [max: 3, offset: 2, sort: "title", order: "desc"])

Where 查询

Where查询比动态查询灵活，比条件查询简洁。

基本查询

def query = Person.where {   firstName == "Bart"}Person bart = query.find()

class Person {    static simpsons = where {         lastName == "Simpson"    }    …}…Person.simpsons.each {    println it.firstname}

上面查询是延迟查询，如果需要及时加载查询，可以用findAll 和find来查询

def results = Person.findAll {     lastName == "Simpson"}def results = Person.findAll(sort:"firstName") {     lastName == "Simpson"}Person p = Person.find { firstName == "Bart" }

OperatorCriteria MethodDescription==eqEqual to!=neNot equal to>gtGreater than<ltLess than>=geGreater than or equal to<=leLess than or equal toininListContained within the given list==~likeLike a given string=~ilikeCase insensitive like

查询组合

def query = Person.where {     lastName == "Simpson"}def bartQuery = query.where {     firstName == "Bart"}Person p = bartQuery.find()

备注：

闭包不能放在where里面，如下会报错

def callable = {    lastName == "Simpson"}def query = Person.where(callable)

可以修改成为：

import grails.gorm.DetachedCriteria
def callable = {    lastName == "Simpson"} as DetachedCriteria<Person>def query = Person.where(callable)

连词,析取和否定

def query = Person.where {    (lastName != "Simpson" && firstName != "Fred") || (firstName == "Bart" && age > 9)}

def query = Person.where {    firstName == "Fred" && !(lastName == 'Simpson')}

属性比较查询

def query = Person.where {   firstName == lastName}

OperatorCriteria MethodDescription==eqPropertyEqual to!=nePropertyNot equal to>gtPropertyGreater than<ltPropertyLess than>=gePropertyGreater than or equal to<=lePropertyLess than or equal to

关联查询

def query = Pet.where {    owner.firstName == "Joe" || owner.firstName == "Fred"}

def query = Person.where {    pets { name == "Jack" || name == "Joe" }}

 子查询

final query = Person.where {  age > avg(age)}

MethodDescriptionavgThe average of all valuessumThe sum of all valuesmaxThe maximum valueminThe minimum valuecountThe count of all valuespropertyRetrieves a property of the resulting entities其他的功能

MethodDescriptionsecondThe second of a date propertyminuteThe minute of a date propertyhourThe hour of a date propertydayThe day of the month of a date propertymonthThe month of a date propertyyearThe year of a date propertylowerConverts a string property to upper caseupperConverts a string property to lower caselengthThe length of a string propertytrimTrims a string property

def query = Pet.where {    year(birthDate) == 2011}

def query = Person.where {    year(pets.birthDate) == 2009}

批量更新和删除

def query = Person.where {    lastName == 'Simpson'}int total = query.updateAll(lastName:"Bloggs")

def query = Person.where {    lastName == 'Simpson'}int total = query.deleteAll()

条件查询

条件查询是先进的查询方法，使用groovy创建复杂的查询。使用createCriteria或者withCriteria方法。如果查询数据没有记录，返回空List。

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

连接和析取

or {    between("balance", 500, 1000)    eq("branch", "London")}

and {    between("balance", 500, 1000)    eq("branch", "London")}

not {    between("balance", 500, 1000)    eq("branch", "London")}

联合查询

class Account {    …    static hasMany = [transactions: Transaction]    …}

def c = Account.createCriteria()def now = new Date()def results = c.list {    transactions {        between('date', now - 10, now)    }}

def c = Account.createCriteria()def now = new Date()def results = c.list {    or {        between('created', now - 10, now)        transactions {            between('date', now - 10, now)        }    }}

投影查询

def c = Account.createCriteria()
def numberOfBranches = c.get {    projections {        countDistinct('branch')    }}

SQL投影

// Box is a domain class…class Box {    int width    int height}

// Use SQL projections to retrieve the perimeter and area of all of the Box instances…def c = Box.createCriteria()
def results = c.list {    projections {      sqlProjection '(2 * (width + height)) as perimiter, (width * height) as area', ['perimeter', 'area'], [INTEGER, INTEGER]    }}

box Table：

widthheight27282949
查询结果：

[[18, 14], [20, 16], [22, 18], [26, 36]]

使用sql限制

def c = Person.createCriteria()
def peopleWithShortFirstNames = c.list {    sqlRestriction "char_length(first_name) <= 4"}

def c = Person.createCriteria()
def peopleWithShortFirstNames = c.list {    sqlRestriction "char_length(first_name) < ? AND char_length(first_name) > ?", [maxValue, minValue]}

使用可滚动结果集

def results = crit.scroll {    maxResults(10)}def f = results.first()def l = results.last()def n = results.next()def p = results.previous()
def future = results.scroll(10)def accountNumber = results.getLong('number')

Setting properties in the Criteria instance

If a node within the builder tree doesn't match a particular criterion it will attempt to set a property on the Criteria object itself. This allows full access to all the properties in this class. This example calls setMaxResults and setFirstResult on theCriteria instance:

import org.hibernate.FetchMode as FM…def results = c.list {    maxResults(10)    firstResult(50)    fetchMode("aRelationship", FM.JOIN)}

Querying with Eager Fetching

In the section on Eager and Lazy Fetching we discussed how to declaratively specify fetching to avoid the N+1 SELECT problem. However, this can also be achieved using a criteria query:

def criteria = Task.createCriteria()def tasks = criteria.list{    eq "assignee.id", task.assignee.id    join 'assignee'    join 'project'    order 'priority', 'asc'}

Notice the usage of the join method: it tells the criteria API to use a JOIN to fetch the named associations with the Task instances. It's probably best not to use this for one-to-many associations though, because you will most likely end up with duplicate results. Instead, use the 'select' fetch mode:

import org.hibernate.FetchMode as FM…def results = Airport.withCriteria {    eq "region", "EMEA"    fetchMode "flights", FM.SELECT}

Although this approach triggers a second query to get the flights association, you will get reliable results - even with the maxResults option.

fetchMode and join are general settings of the query and can only be specified at the top-level, i.e. you cannot use them inside projections or association constraints.

An important point to bear in mind is that if you include associations in the query constraints, those associations will automatically be eagerly loaded. For example, in this query:

def results = Airport.withCriteria {    eq "region", "EMEA"    flights {        like "number", "BA%"    }}

the flights collection would be loaded eagerly via a join even though the fetch mode has not been explicitly set.

Method Reference

If you invoke the builder with no method name such as:

c { … }

The build defaults to listing all the results and hence the above is equivalent to:

c.list { … }

MethodDescriptionlistThis is the default method. It returns all matching rows.getReturns 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.scrollReturns a scrollable result set.listDistinctIf subqueries or associations are used, one may end up with the same row multiple times in the result set, this allows listing only distinct entities and is equivalent to DISTINCT_ROOT_ENTITY of the CriteriaSpecification class.countReturns the number of matching rows.

离线查询

构造离线查询

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'}

执行离线查询的方法，如下：

MethodDescriptionlistList all matching entitiesgetReturn a single matching resultcountCount all matching recordsexistsReturn true if any matching records existdeleteAllDelete all matching recordsupdateAll(Map)Update all matching records with the given properties

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}

Hibernate查询语言

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 ?", ["The Shi%"])

def author = Author.findByName("Stephen King")def books = Book.findAll("from Book as book where book.author = ?",                         [author])

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