In Hibernate all queries can be quite powerful and complex. We can say that the power of the query language is one of Hibernate's main strengths. With the help of the following example queries are similar to queries that have been used on recent projects. Remember only one thing that is most queries will write will be much simpler than the following examples.
In the following query returns the order id, number of items, the given minimum total value and the total value of the order for all unpaid orders for a particular customer. The results are ordered by total value. In determining the prices, it uses the current catalog. The resulting SQL query, against the ORDER, ORDER_LINE, PRODUCT, CATALOG and PRICE tables has four inner joins and an (uncorrelated) subselect.
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
In the above given code what a monster! Actually, in real life, I'm not very keen on subqueries, so my query was really more like this:
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
The next query counts the number of payments in each status, excluding all payments in the AWAITING_APPROVAL status where the most recent status change was made by the current user. It translates to an SQL query with two inner joins and a correlated subselect against the PAYMENT, PAYMENT_STATUS and PAYMENT_STATUS_CHANGE tables.
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
If the statusChanges collection was mapped as a list, instead of a set, the query would have been much simpler to write.
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
The next query uses the MS SQL Server isNull() function to return all the accounts and unpaid payments for the organization to which the current user belongs. It translates to an SQL query with three inner joins, an outer join and a subselect against the ACCOUNT, PAYMENT, PAYMENT_STATUS, ACCOUNT_TYPE, ORGANIZATION and ORG_USER tables.
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
For some databases, we would need to do away with the (correlated) subselect.
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