兔八哥笔记14(3)：Hibernate 查询语言(HQL)
10.11 子查询（Subqueries）
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 例子（Examples）
Hibernate的查询功能相当强大，而且复杂。事实上，功能强大的查询语言是Hibernate的主要的卖点（selling points。原来这是一个外来词呀，今天才知道。^_^）。
下面是一些简单的查询例子，这是从我最近的项目中挑出来的，在大多数情况下，你写的查询要比这些更简单。
下面这个查询使用到的表：order, order_line, product, catalog 和price。有4个内连接和一个没有关系（uncorrelated）的子查询。
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
简直是怪物！（What a monster!）。现实生活中（in real life），我们对子查询并不是很关心，我们大多数的查询更像下面这样：
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
下面这个 查询翻译成SQL将需要2个内连接和一个相关联的子查询。它用到的表：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
如果我映射tatusChanges为一个List代替set，那个我们的查询将更简单：
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的isnull（）函数，用到3个内连接和1个外联接，用大的表：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 提示和窍门
你可以不用真正的返回查询结果，而知道查询结果的数量：
( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
按照返回的集合的大小排序，可以使用下面的语句：
select usr.id, usr.name from User as usr
left join usr.messages as msg
group by usr.id, usr.name
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
上面这个办法因为使用了内连接不能返回拥有0个消息的用户的信息，下面这个查询是有用的：
select usr.id, usr.name
from User as usr
left join usr.messages as msg
group by usr.id, usr.name
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接口实现分页（pageable）功能：
Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();
使用查询过滤器，可以实现集合元素的排序和分组：
Collection orderedCollection = s.filter( collection, "order by this.amount" );
Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );
你可以不用对集合进行初始化，就可以得到它的大小：
( (Integer) session.iterate("select count(*) from ....").next() ).intValue();
兔八哥
2004年1月4日星期日 17:57
