14. HQL: Hibernate查询语言 / 14.13. HQL示例

Hibernate查询可以非常的强大与复杂。实际上,Hibernate的一个主要卖点就是查询语句的威力。这里有一些例子,它们与我在最近的 一个项目中使用的查询非常相似。注意你能用到的大多数查询比这些要简单的多!

下面的查询对于某个特定的客户的所有未支付的账单,在给定给最小总价值的情况下,返回订单的id,条目的数量和总价值, 返回值按照总价值的结果进行排序。为了决定价格,查询使用了当前目录。作为转换结果的SQL查询,使用了ORDER, ORDER_LINE, PRODUCT, CATALOGPRICE 库表。

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

如果我把statusChanges实例集映射为一个列表(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 Server的 isNull()函数用以返回当前用户所属组织的组织帐号及组织未支付的账。 它被转换成一个对表ACCOUNT, PAYMENT, PAYMENT_STATUS, ACCOUNT_TYPE, ORGANIZATION 以及 ORG_USER进行的三个内连接, 一个外连接和一个子选择的SQL查询。

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