-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for JBoss Enterprise Application Platform Common Criteria Certification
7.12. EJB-QL examples
Hibernate queries can be quite powerful and complex. In fact, the power of the query language is one of Hibernate's main selling points (and now EJB-QL). This section includes examples of queries in Hibernate.
The following query returns the order id, number of items and total value of the order for all unpaid orders for a particular customer and given minimum total value, ordering the results 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 < current_date() and catalog.effectiveDate >= all ( select cat.effectiveDate from Catalog as cat where cat.effectiveDate < current_date() ) group by order having sum(price.amount) > :minAmount order by sum(price.amount) desc
To avoid using subqueries, you could write something 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 were mapped as a list instead of a set, the query would be far simpler.
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
However the query would have been HQL specific.
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 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