Inner Joins Query in HQL

Question

I'm unable to execute HQL for Inner Joins, Query is executing correct at sql but not in HQL. I don't know where i'm missing. Your help is appreciable.

 ***Error***: org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ON near line 1, column 148 [SELECT op.username, op.email, orders.p_id, orders.o_id, product.listed_price FROM com.model.Orders  orders INNER JOIN orders.OrderProcessing as op ON op.u_id = orders.u_id INNER JOIN orders.Product as product ON product.p_id = orders.p_id WHERE product.p_id = '208' ORDER BY op.username]

productList = (List<Orders>) session.createQuery(
 "SELECT op.username, op.email, orders.p_id, orders.o_id, product.listed_price " +                                                                               
 "FROM Orders orders " +                                                                   
 "INNER JOIN orders.OrderProcessing as op " +                                                                                       
 "ON op.u_id = orders.u_id " +                                                         
 "INNER JOIN orders.Product as product " +                                                              
 "ON product.p_id = orders.p_id " +                                                        
 "WHERE product.p_id = '"+p_id +"' " +                                                         
 "ORDER BY op.username"
).list();

Answer 1

Joins in HQL have a slightly different syntax.

If you've already got those associations mapped with Hibernate, the join conditions (which id fields to do the join on) are usually handled by Hibernate itself (which already has that information defined in the mapping), so you only need to specify which attribute the association is mapped with, and do the join on it:

SELECT op.username, op.email, orders.p_id, orders.o_id, product.listed_price 
FROM Orders order
INNER JOIN order.orderProcessing as op
INNER JOIN order.product as product 
ORDER BY op.username

In the case you haven't got those associations mapped, you should probably use a cross join like syntax, and specify the join conditions in the WHERE clause. Please note this could have a negative impact on efficiency depending on your schema structure and DBMS.

SELECT op.username, op.email, orders.p_id, orders.o_id, product.listed_price
FROM Orders order, OrderProcessing op, Product product
WHERE op.u_id = order.u_id AND product.p_id = orders.p_id
ORDER BY op.username

You can find more information on HQL joins in the 14.3. Associations and joins section of the Hibernate reference.