Les requêtes Hibernate peuvent être relativement puissantes et complexes. En fait, la puissance du langage de requêtage est l'un des avantages principaux d'Hibernate. Voici quelques exemples très similaires aux requêtes que nous avons utilisées lors d'un récent projet. Notez que la plupart des requêtes que vous écrirez seront plus simples que les exemples suivantes !

La requête suivante retourne l'id de commande (order), le nombre d'articles (items) et la valeur totale de la commande (order) pour toutes les commandes non payées d'un client (customer) particulier pour un total minimum donné, le tout trié par la valeur totale. La requête SQL générée sur les tables ORDER, ORDER_LINE, PRODUCT, CATALOG et PRICE est composée de quatre jointures interne ainsi que d'une sous-requête (non corrélée).

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

Quel monstre ! En principe, nous ne sommes pas très fan des sous-requêtes, la requête ressemblait donc plutôt à cela :

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

La requête suivante compte le nombre de paiements (payments) pour chaque status, en excluant les paiements dans le status AWAITING_APPROVAL où le changement de status le plus récent à été fait par l'utilisateur courant. En SQL, cette requête effectue deux jointures internes et des sous requêtes corrélées sur les tables PAYMENT, PAYMENT_STATUS et 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

Si nous avions mappé la collection statusChanges comme une liste, au lieu d'un ensemble, la requête aurait été plus facile à écrire.

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

La requête qui suit utilise la fonction de MS SQL isNull() pour retourner tous les comptes (accounts) et paiements (payments) impayés pour l'organisation à laquelle l'uilisateur (user) courant appartient. Elle est traduite en SQL par trois jointures internes, une jointure externe ainsi qu'une sous requête sur les tables ACCOUNT, PAYMENT, PAYMENT_STATUS, ACCOUNT_TYPE, ORGANIZATION et 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

Pour d'autres base de données, nous aurions dû faire sans la sous-requête (corrélée).

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