from eg.Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten
from eg.Cat as cat left join cat.mate.kittens as kittens
from Formula form full join form.parameter param
支持的连接类型是从ANSI SQL借用的:
内连接,inner join
左外连接,left outer join
右外连接,right outer join
全连接,full join (不常使用)
inner join, left outer join 和 right outer join 都可以简写。
from eg.Cat as cat
join cat.mate as mate
left join cat.kittens as kitten
并且,加上 "fetch"后缀的抓取连接可以让联合的对象随着它们的父对象的初始化而初始化,只需要一个select语句。这在初始化一个集合的时候特别有用。它有效地覆盖了映射文件中对关联和集合的外连接定义。
from eg.Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens
抓取连接一般不需要赋予别名,因为被联合的对象应该不会在where子句(或者任何其它子句)中出现。并且,被联合的对象也不会在查询结果中直接出现。它们是通过父对象进行访问的。
请注意,目前的实现中,在一次查询中只会抓取一个集合(其他的一切都做不到。)(?原文为:only one collection role may be fetched in a query)。也请注意,在使用scroll()或者 iterate()方式调用的查询中,是禁止使用fetch构造的。最后,请注意full join fetch和right join fetch是没有意义的。
select mate
from eg.Cat as cat
inner join cat.mate as mate
这个查询会选择出作为其它猫(Cat)朋友(mate)的那些猫。当然,你可以更加直接的写成下面的形式:
select cat.mate from eg.Cat cat
你甚至可以选择集合元素,使用特殊的elements功能。下面的查询返回所有猫的小猫。
select elements(cat.kittens) from eg.Cat cat
查询可以返回任何值类型的属性,包括组件类型的属性:
select cat.name from eg.DomesticCat cat
where cat.name like 'fri%'
select cust.name.firstName from Customer as cust
查询可以用元素类型是Object[]的一个数组返回多个对象和/或多个属性。
select mother, offspr, mate.name
from eg.DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr
或者实际上是类型安全的Java对象
select new Family(mother, mate, offspr)
from eg.DomesticCat as mother
join mother.mate as mate
left join mother.kittens as offspr
上面的代码假定Family有一个合适的构造函数。
select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat)
from eg.Cat cat
在select子句中,统计函数的变量也可以是集合。
select cat, count( elements(cat.kittens) )
from eg.Cat cat group by cat
下面是支持的统计函数列表:
avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)
distinct 和 all关键字的用法和语义与SQL相同。
select distinct cat.name from eg.Cat cat
select count(distinct cat.name), count(cat) from eg.Cat cat
11.6. 多态(polymorphism)查询
类似下面的查询:
from eg.Cat as cat
返回的实例不仅仅是Cat,也有可能是子类的实例,比如DomesticCat。Hibernate查询可以在from子句中使用任何Java类或者接口的名字。查询可能返回所有继承自这个类或者实现这个接口的持久化类的实例。下列查询会返回所有的持久化对象:
from java.lang.Object o
可能有多个持久化类都实现了Named接口:
from eg.Named n, eg.Named m where n.name = m.name
请注意,上面两个查询都使用了超过一个SQL的SELECT。这意味着order by子句将不会正确排序。(这也意味着你不能对这些查询使用Query.scroll()。)
11.7. where子句
where子句让你缩小你要返回的实例的列表范围。
from eg.Cat as cat where cat.name='Fritz'
返回所有名字为'Fritz'的Cat的实例。
select foo
from eg.Foo foo, eg.Bar bar
where foo.startDate = bar.date
会返回所有的满足下列条件的Foo实例,它们存在一个对应的bar实例,其date属性与Foo的startDate属性相等。复合路径表达式令where子句变得极为有力。思考下面的例子:
from eg.Cat cat where cat.mate.name is not null
这个查询会被翻译为带有一个表间(inner)join的SQL查询。如果你写下类似这样的语句:
from eg.Foo foo
where foo.bar.baz.customer.address.city is not null
你最终会得到的查询,其对应的SQL需要4个表间连接。
=操作符不仅仅用于判断属性是否相等,也可以用于实例:
from eg.Cat cat, eg.Cat rival where cat.mate = rival.mate
select cat, mate
from eg.Cat cat, eg.Cat mate
where cat.mate = mate
特别的,小写的id可以用来表示一个对象的惟一标识。(你可以使用它的属性名。)
from eg.Cat as cat where cat.id = 123
from eg.Cat as cat where cat.mate.id = 69
第二个查询是很高效的。不需要进行表间连接!
from eg.Cat cat where cat.class = eg.DomesticCat
你也可以指定组件(或者是组件的组件,依次类推)或者组合类型中的属性。但是在一个存在路径的表达式中,最后不能以一个组件类型的属性结尾。(这里不是指组件的属性)。比如,假若store.owner这个实体的的address是一个组件
from eg.AuditLog log, eg.Payment payment
where log.item.class = 'eg.Payment' and log.item.id = payment.id
注意上面查询中,log.item.class和payment.class会指向两个值,代表完全不同的数据库字段。
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