Go Sub-query in HQL

Original address: http://blog.csdn.net/xb12369/article/details/8638683

Sub-query: subqueries are a very important feature in SQL statements that can take advantage of the query results of another SQL statement in a SQL statement , HQL in Hibernate queries also provide support for subquery functionality. As shown in the following code: list List=session.createquery ("from Customer C where 1> (select COUNT (o) from c.orders o)"). List (); The above program queries all customers for more than 1 orders , so the SQL statement corresponding to the HQL statement for the upper face query is: SELECT * from the Customer C where 1> (select count (o.id) from Order o where c.id=o.customer_id); If the subquery returns more than one record, you can use the following keyword: All: indicates that all records returned by a subquery statement any: represents any one result returned by a subquery statement some: equivalent to "any" In : equivalent to "=any" exists: indicates that at least one record is returned by a subquery statement For example, the query exists a customer with an order price greater than From the Customer C where 100>any (select O.price from C.orders o); If you manipulate a collection in a subquery,HQL provides a set of functions and properties that manipulate the collection: size () function and size property: Gets the number of elements in the collection minindex () function and minindex property: Gets the minimum index value for the set of indexes (about the collection index reference the first part of the map value type collection) minelement () function and minelement property: Gets the element with the lowest value in the collection for the collection of elements containing the base type maxelement () function and maxelement property: Gets the element with the largest value in the collection for a collection that contains primitive type elements Element () function: Gets all the elements in the collection For example, a customer who queries for more than 0 orders From Customer C where size (c.orders) >0; or from Customer C where c.orders.size>0; The HQL statement above generates an SQL statement similar to the following: SELECT * from the customer C where 0> (select count (o.id) from order where O. customer_id =c.id); Note: inHQLThe neutron query must appear in thewhereclause, and must be enclosed in a pair of parentheses. Why do I have to appear inwhere after the sentence? In fact, we all think carefully about it, in hibernate hibernate main configuration file, That is to say, all appear in hql fromsqlfrom The dynamic view subquery after the sentence.

Sub-query in]HQL