领域类之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 - 7
def 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" }

Operator	Criteria Method	Description
==	eq	Equal to
!=	ne	Not equal to
>	gt	Greater than
<	lt	Less than
>=	ge	Greater than or equal to
<=	le	Less than or equal to
in	inList	Contained within the given list
==~	like	Like a given string
=~	ilike	Case 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
}

Operator	Criteria Method	Description
==	eqProperty	Equal to
!=	neProperty	Not equal to
>	gtProperty	Greater than
<	ltProperty	Less than
>=	geProperty	Greater than or equal to
<=	leProperty	Less 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)
}

Method	Description
avg	The average of all values
sum	The sum of all values
max	The maximum value
min	The minimum value
count	The count of all values
property	Retrieves a property of the resulting entities

其他的功能

Method	Description
second	The second of a date property
minute	The minute of a date property
hour	The hour of a date property
day	The day of the month of a date property
month	The month of a date property
year	The year of a date property
lower	Converts a string property to upper case
upper	Converts a string property to lower case
length	The length of a string property
trim	Trims 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：

width	height
2	7
2	8
2	9
4	9

查询结果：

[[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 { … }

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, this allows listing only distinct entities and is equivalent to DISTINCT_ROOT_ENTITY of the CriteriaSpecification class.
count	Returns 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'
}

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

Method	Description
list	List all matching entities
get	Return a single matching result
count	Count all matching records
exists	Return true if any matching records exist
deleteAll	Delete all matching records
updateAll(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])