# 14.13\. HQL示例 Hibernate查詢可以非常的強大與復雜。實際上，Hibernate的一個主要賣點就是查詢語句的威力。這里有一些例子，它們與我在最近的 一個項目中使用的查詢非常相似。注意你能用到的大多數查詢比這些要簡單的多！ 下面的查詢對于某個特定的客戶的所有未支付的賬單，在給定給最小總價值的情況下，返回訂單的id，條目的數量和總價值， 返回值按照總價值的結果進行排序。為了決定價格，查詢使用了當前目錄。作為轉換結果的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 ``` 如果我把`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 ```