Hibernate HQL syntax (II)

11. subquery

For databases that support subqueries, Hibernate supports using subqueries in queries. A subquery must be enclosed by parentheses (usually the parentheses of SQL clustering functions ). Even correlated subqueries (subqueries that reference aliases in external queries) are allowed.

from Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from DomesticCat cat )

from DomesticCat as cat where cat.name = some ( select name.nickName from Name as name )

from Cat as cat where not exists ( from Cat as mate where mate.mate = cat )

from DomesticCat as cat where cat.name not in ( select name.nickName from Name as name )

The select list contains a subquery with more than one expression. You can use a tuple constructors ):

from Cat as cat where not ( cat.name, cat.color ) in ( select cat.name, cat.color from DomesticCat cat )

Note that in some databases (excluding Oracle and HSQL), you can also use tuples in other contexts, such as querying user-type components and combinations:

from Person where name = ('Gavin', 'A', 'King')

This query is equivalent to a more complex one:

from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')

There are two good reasons why you should not think of it as this: first, it is not entirely applicable to various database platforms; second, queries are now dependent on the order of attributes in the ing file.

12. HQL example

Hibernate queries can be very powerful and complex. In fact, a major selling point of Hibernate is the power of query statements. Here are some examples, which are very similar to the queries I used in a recent project. Note that most of the queries you can use are much simpler than these!

The following query returns the order id, number of items, and total value for all unpaid bills of a specific customer when the minimum total value is given, return values are sorted by the total value. To determine the price, the current directory is used for query. The SQL query used as the Conversion ResultORDER,ORDER_LINE,PRODUCT,CATALOGAndPRICEDatabase table.

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

This is a monster! In fact, in real life, I am not keen on subqueries, so my query statements look 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 following query calculates the number of payments in each State.AWAITING_APPROVALStatus payment, because the current user has made the latest change in the status. This query is converted to an SQL query that contains two inner connections and an associated subselection. This query uses the tablePAYMENT,PAYMENT_STATUSAndPAYMENT_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

IfStatusChangesThe instance set is mapped to a list instead of a set. It is easier to write query statements.

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 following query usesIsNull ()The function is used to return the Organization account of the current user's organization and the unpaid account of the organization. It is converted into a tableACCOUNT,PAYMENT,PAYMENT_STATUS,ACCOUNT_TYPE,ORGANIZATIONAndOrg_userThree internal connections, one external connection, and a sub-selected SQL query.

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 need to discard (related) Sub-selection.

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

13. Batch UPDATE & DELETE statements

Hql now supports update and delete statements.

14. Tips & tips

You can count the number of query results without actually returning them:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()

To sort data by the size of a set, use the following statement:

select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name order by count(msg)

If your database supports sub-selection, you can specify a condition for the selected size (selection size) in the where clause of your query:

from User usr where size(usr.messages) >= 1

If your database does not support sub-selection statements, use the following query:

select usr.id, usr.name from User usr.name join usr.messages msg group by usr.id, usr.name having count(msg) >= 1

Because of inner join, this solution cannot return zero informationUserSo it is helpful to use the following format in this case:

select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name having count(msg) = 0

JavaBean attributes can be bound to a named query parameter:

Query q = s. createQuery ("from foo Foo as foo where foo. name =: name and foo. size =: size "); q. setProperties (fooBean); // fooBean contains methods getName () and getSize () List foos = q. list ();

By callingQueryUsed with a filter, Collections can be paged:

Query q = s. createFilter (collection, ""); // a simple filter q. setMaxResults (PAGE_SIZE); q. setFirstResult (PAGE_SIZE * pageNumber); List page = q. list ();

You can use the query filter to group or sort the elements of a Collection:

Collection orderedCollection = s.filter( collection, "order by this.amount" ); Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );

You can know the size of a Collection without initialization:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();