兔八哥笔记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 
|
本类阅读TOP10