ITEEDU

首页 上一页 下一页

10.9. order by 子句

查询返回的列表可以按照任何返回的类或者组件的属性排序： 

from eg.DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate

asc和desc是可选的，分别代表升序或者降序。

10.10. group by 子句

返回统计值的查询可以按照返回的类或者组件的任何属性排序： 

select cat.color, sum(cat.weight), count(cat) 
from eg.Cat cat
group by cat.color

select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) 
from eg.Foo foo
group by foo.id

请注意：你可以在select子句中使用elements和indices指令，即使你的数据库不支持子查询也可以。

having子句也是允许的。 

select cat.color, sum(cat.weight), count(cat) 
from eg.Cat cat
group by cat.color 
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)

在having子句中允许出现SQL函数和统计函数，当然这需要底层数据库支持才行。（比如说,MySQL就不支持） 

select cat
from eg.Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc

注意，group by子句和order by子句都不支持数学表达式。

10.11. 子查询

对于支持子查询的数据库来说，Hibernate支持在查询中嵌套子查询。子查询必须由圆括号包围（常常是在一个SQL统计函数中）。也允许关联子查询（在外部查询中作为一个别名出现的子查询）。 

from eg.Cat as fatcat 
where fatcat.weight > ( 
    select avg(cat.weight) from eg.DomesticCat cat 
)

from eg.DomesticCat as cat 
where cat.name = some ( 
    select name.nickName from eg.Name as name 
)
    
from eg.Cat as cat 
where not exists ( 
    from eg.Cat as mate where mate.mate = cat 
)

from eg.DomesticCat as cat 
where cat.name not in ( 
    select name.nickName from eg.Name as name 
)

10.12. 示例

Hibernate查询可以非常强大复杂。实际上，强有力的查询语言是Hibernate的主要卖点之一。下面给出的示例与我在近期实际项目中使用的一些查询很类似。请注意你编写的查询大部分等都不会这么复杂！

下面的查询对特定的客户，根据给定的最小总计值（minAmount)，查询出所有未付订单，返回其订单号、货品总数、订单总金额，结果按照总金额排序。在决定价格的时候，参考当前目录。产生的SQL查询，在ORDER,ORDER_LINE,PRODUCT,CATALOG和PRICE表之间有四个内部连接和一个没有产生关联的字查询。 

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate < sysdate
    and catalog.effectiveDate >= all (
        select cat.effectiveDate 
        from Catalog as cat
        where cat.effectiveDate < sysdate
    )
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

好家伙，真长！实际上，在现实生活中我并不是非常热衷于子查询，所以我的查询往往是这样的： 

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog = :currentCatalog
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

下面的查询统计付款记录处于每种状态中的数量，要排除所有处于AWAITING_APPROVAL状态的，或者最近一次状态更改是由当前用户做出的。它翻译成SQL查询后，在PAYMENT,PAYMENT_STATUS和PAYMENT_STATUS_CHANGE表之间包含两个内部连接和一个用于关联的子查询。 

select count(payment), status.name 
from Payment as payment 
    join payment.currentStatus as status
    join payment.statusChanges as statusChange
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or (
        statusChange.timeStamp = ( 
            select max(change.timeStamp) 
            from PaymentStatusChange change 
            where change.payment = payment
        )
        and statusChange.user <> :currentUser
    )
group by status.name, status.sortOrder
order by status.sortOrder

假若我已经把statusChange集合映射为一个列表而不是一个集合的话，查询写起来会简单很多。 

select count(payment), status.name 
from Payment as payment
    join payment.currentStatus as status
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser
group by status.name, status.sortOrder
order by status.sortOrder

下面的查询使用了MS SQL Server的isNull()函数，返回当前用户所属的组织所有账户和未付支出。翻译为SQL查询后，在ACCOUNT, PAYMENT, PAYMENT_STATUS,ACCOUNT_TYPE, ORGANIZATION 和 ORG_USER表之间有三个内部连接，一个外部连接和一个子查询。 

select account, payment
from Account as account
    left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate

对某些数据库而言，我们可能不能依赖（关联的）子查询。

select account, payment
from Account as account
    join account.holder.users as user
    left outer join account.payments as payment
where :currentUser = user
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate

10.13. 提示和技巧（Tips & Tricks）

你不返回结果集也可以查询结果集的大小： 

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
要依据一个集合的大小对结果集排序，可以用下面的查询来对付一对多或多对多的关联： 
select usr
from User as usr 
    left join usr.messages as msg
group by usr
order by count(msg)
如果你的数据库支持子查询，你可以在查询的where子句中对选择的大小进行条件限制： 
from User usr where size(usr.messages) >= 1

如果你的数据库不支持子查询，可以使用下列查询： 

select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1

因为使用了inner join,这个解决方法不能返回没有message的User.下面的方式就可以： 

select usr
from User as usr
    left join usr.messages as msg
group by usr
having count(msg) = 0
JavaBean的属性可以直接作为命名的查询参数： 
Query q = s.createQuery("from foo in class Foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();
在Query接口中使用过滤器(filter),可以对集合分页： 
Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();

集合元素可以使用查询过滤器(query filter)进行排序或者分组： 

List orderedCollection = s.filter( collection, "order by this.amount" );
List counts = s.filter( collection, "select this.type, count(this) group by this.type" );

不用初始化集合就可以得到其大小：

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();
下一页 上一页首页