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