CODEWITHSUNDEEP
hibernatehql
ZAJ
ZAJ

Reputation: 835

Join multiple tables in HQL

Can anyone help me to convert this query in to HQL

SELECT      
  supplierOrderDetails.productID,
  supplierOrderDetails.orderQty,
  supplierOrderReceiveDetail.qtyArrived
FROM
 supplierOrder
INNER JOIN
  supplierOrderDetails
ON
  (supplierOrderDetails.supplierOrderID = supplierOrder.ID)
INNER JOIN 
    supplierOrderReceive
ON  
 (supplierOrderReceive.supplierOrderID = supplierOrder.ID)
INNER JOIN 
    supplierOrderReceiveDetail
ON  
 (supplierOrderReceiveDetail.supplierOrderReceiveID = supplierOrderReceive.ID)  
   AND 
 (supplierOrderReceiveDetail.ProductID =supplierOrderDetails.ProductID)     
WHERE supplierOrder.ID = 1

Here is the table relationship

enter image description here

Upvotes: 6

Views: 37350

Answers (3)

Yagnesh Agola
Yagnesh Agola

Reputation: 4659

This link is good to start learning how to join entity by HQL.

HQL Join Reference

Edit :
For hibernate to join entities, you must have defined associations/mapping between entities.
Hibernate uses the relationship name defined in your Hibernate mapping file.

Upvotes: -1

ZAJ
ZAJ

Reputation: 835

I have added the query for those who want to know how to JOIN multiple tables in HQL

SELECT supplierOrderDetails.productID as product, supplierOrderDetails.orderQty as orderedQty,sum(supplierOrderReceiveDetail.qtyArrived) as qtyArrived 

FROM SupplierOrder as so, SupplierOrderDetails as supplierOrderDetails,  SupplierOrderReceiveDetail as supplierOrderReceiveDetail, SupplierOrderReceive as supplierOrderReceive

INNER JOIN supplierOrderDetails.supplierOrderID

INNER JOIN supplierOrderReceive.supplierOrder

INNER JOIN supplierOrderReceiveDetail.supplierOrderReceive

GROUP BY supplierOrderDetails.productID, supplierOrderDetails.orderQty

Upvotes: 5

K.C.
K.C.

Reputation: 2112

HQL makes use of your mapped Entities and returns Entities. So depending on your hibernate (JPA) mapping, the HQL you want could be as simple as :

from SupplierOrder where id = 1

  • SupplierOrder is the class name of your mapped Entity
  • id is the property name (instance variable) in your SupplierOrder class
  • and I presupposed that all the other Entities where mapped with SupplierOrder class the highest in the tree hierarchy.

Upvotes: -1

Related Questions