Details below:
Currently I have 3 separate queries to bollock together returning an Item to a customer. The first gets the Item and is a simple get by Id query. The second gets all the Items on stock and adds that to the Item (in java code inside service method), the third gets all Items on Hand with similar sort of query. Performance for this is obvious sloooow. From what I understand that fetching a collection that belongs to any object is done in a separate query and that if I was to put them both as bidirectional manytoone relationships that they would still be separate SQL queries and that you can only fetch one collection on an object (at least in older versions of hibernate.) Problem is I want to do aggregate SUM() on both of the collections that I'm adding to my composite object. What is the most performant way to do this. I'm doing 3 separate queries from my service object and bollocking it all together here. But it seems a waste. FYI I am using springs HibernateDaoSupport class.

3.2.5.ga


QUERY 1:
simple get by id
QUERY 2:
Query query = session.createQuery("select new Map ( sum(ile.quantity) as quantity, ile.itemNo as itemNo, ile.locationCode as locationCode ) from ItemLedgerEntry as ile where ile.itemNo = (?) and ile.open = 1 group by ile.itemNo, ile.locationCode").setParameter(0, itemNo);
QUERY 3:
Query query = session.createQuery("select new Map ( sum(sl.quantity) as quantity, sl.itemNo as itemNo, sl.locationCode as locationCode ) from SalesLine as sl where sl.itemNo = (?) group by sl.itemNo, sl.locationCode").setParameter(0, itemNo);


SQL Server

Generated SQL: <br>
Hibernate: select item0_.[No.] as No1_0_0_, item0_.[Base Unit of Measure] as Base2_0_0_, item0_.[Description] as Descript3_0_0_, item0_.[Item Disc. Group] as Item4_0_0_, item0_.[Gen. Prod. Posting Group] as Gen5_0_0_, item0_.[Inventory Posting Group] as Inventory6_0_0_, item0_.[No. 2] as No7_0_0_, item0_.[Unit Cost] as Unit8_0_0_, item0_.[Unit Price] as Unit9_0_0_ from TWL$Item item0_ where item0_.[No.]=?
Hibernate: select sum(itemledger0_.Quantity) as col_0_0_, itemledger0_.[Item No.] as col_1_0_, itemledger0_.[Location Code] as col_2_0_ from [TWL$Item Ledger Entry] itemledger0_ where itemledger0_.[Item No.]=? and itemledger0_.[Open]=1 group by itemledger0_.[Item No.] , itemledger0_.[Location Code]
Hibernate: select sum(salesline0_.[Outstanding Qty. (Base)]) as col_0_0_, salesline0_.[No.] as col_1_0_, salesline0_.[Location Code] as col_2_0_ from [TWL$Sales Line] salesline0_ where salesline0_.[No.]=? group by salesline0_.[No.] , salesline0_.[Location Code]