14.14. HQL の例
Hibernate クエリは非常に強力で複雑になりえます。実際、クエリ言語の威力は Hibernate の主要なセールスポイントの一つです。以下のクエリ例は、最近のプロジェクトで使用したクエリと非常によく似ています。ほとんどのクエリはこれらの例より簡単に記述できることに注意してください。
以下のクエリは、特定の顧客に関する未払いの注文すべてに対し、注文 ID 、商品の数、最小の合計値、注文の合計値を返します。結果は合計値別に整列されます。価格を決定する際、現在のカタログを使います。結果として返される SQL クエリは
ORDER
、ORDER_LINE
、PRODUCT
、CATALOG
および PRICE
テーブルに対し4つの内部結合と (関連しない) 副問い合わせを持ちます。
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
である場合を除きます。このクエリは2つの内部結合と PAYMENT
, PAYMENT_STATUS
および PAYMENT_STATUS_CHANGE
テーブルに対する関連副問い合わせを備えた SQL クエリに変換されます。
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
set の代わりに list として
statusChanges
コレクションをマッピングすると、はるかに簡単にクエリを記述できるでしょう。
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()
関数を使用しています。このクエリは3つの内部結合と1つの外部結合、そして 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