Rabbit Eight Brother Note 14 (3): Hibernate Query Language (HQL)
10.11 subqueries (SUBQUERIES)
Hibernate supports subquery, and subquery must be surrounded by parentheses. Subsequet usually called by SQL aggregation function. The alias that references an external query in the subquery is also allowed.
From eg.cat as fatcat
WHERE fatcat.weight>
SELECT AVG (Cat.Weight) from eg.domsticcat cat
)
From eg.domesticcat as cat
Where cat.name = Some
Select name.nickname from eg.name as name
)
From eg.cat as cat
WHERE NOT EXISTS
From eg.cat as worth where code.mate = cat
)
From eg.domesticcat as cat
Where cat.name not in (
Select name.nickname from eg.name as name
)
10.12 examples (Examples)
Hibernate's query features are quite powerful and complicated. In fact, the powerful query language is the main selling point of Hibernate (SELLING POINTS. It turns out that this is a foreign word, today know. ^ _ ^).
Here are some simple query examples, which is picked out from my nearest project. In most cases, you should write more simpler than these.
The table below is used below: Order, Order_Line, Product, Catalog and Price. There are 4 internal connections and a subquery that does not have a uncorrelated.
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
And catalog.effectiveDate And catalog.effectiveDate> = all Select Cat.effectiveDate From catalog as cat Where cat.effectiveDate ) GROUP BY Order Having Sum (Price.Amount)>: Minamount Order by Sum (Price.Amount) DESC It's a monster! (What a monster!). In Real Life, we are not very concerned about child inquiries. Most of our queries are 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 = falseand Order.customer =: Customer and price = product And catalog =: CurrentCatalog GROUP BY Order Having Sum (Price.Amount)>: Minamount Order by Sum (Price.Amount) DESC The following query is translated into SQL to need 2 internal connections and a associated subquery. It uses the table: payment, payment_status and payment_status_change. Select count (payment), status.name From payment as payment Join payment.currentstatus as status Join payment.statuschange asse 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 I mapping TatusChanges for a list, then our query will be 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.statuschange)] .user <>: currentuser Group by status.name, Status.Sortorder Order by status.sortorder The following query uses the MS SQL's ISNULL () function to use 3 internal connections and 1 outer join, with large tables: account, payment, payment_status, account_type, organization, and 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 For other databases, we need to use the associated subquery: Select Account, Payment From Account As Account Join Account.Holder.Users as User Left Outer Join Account.Payments as paymentwhere: currentuser = User And paymentStatus.unpaid = isnull (payment.currentstatus.name, paymentstatus.unpaid) Order by account.type.sortorder, account.accountnumber, payment.duedate 10.13 Tips and Tips You can don't have to return to the query results, and you know the quantity of the query results: (Integer) session.Ike ("Select count (*) from ...."). Next ()) .intValue () Sort by the size of the collections returned, you can 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 subqueries, then you can specify a query to return a result size in the WHERE clause: From user usr where size (usr.Messages)> = 1 If your database does not support sub-inquiry, then you can use the front query: SELECT usr.id, usr.name From user usr.name Join usr.Messages MSG Group by usr.id, usr.name haVing count (msg)> = 1 The above approach is useful because of the information that has a user who has 0 messages cannot be returned. The following query is useful: 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's properties can be bound as the specified parameters of the query: Query Q = S.CREATEQUERY ("from foo in class foo where foo.name =: name and foo.size =: size"); Q.SetProperties (FOOBEAN); // foobean Has getName () and getsize () List foos = q.list (); The collection can be implemented by using the Query interface with a filter: Query Q = S.CreateFilter (Collection, "); // The Trivial Filter Q.SetMaxResults (Page_Size); Q.SetFirstResult (Page_SIZE * PAGENUMBER); List page = q.list (); Using the query filter, you can achieve the ordering and packet of the collection element: 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 get its size without initialization, you can get its size: ("" Select count (*) from .... "). Next ()) .intValue (); Bugs Bunny Sunday, January 4, 2004 17:57
