Inner Joins Query in HQL

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 orderINNER JOIN order.orderProcessing as opINNER 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_priceFROM Orders order, OrderProcessing op, Product productWHERE op.u_id = order.u_id AND product.p_id = orders.p_idORDER BY op.username

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

Joins in HQL do not work like in SQL. You can either join on an association:

select order_1.code, item.code, item.quantity  from Order order_1 inner join order_1.item item;

or use a multi-table select:

select order_1.code, item.code, item.quantity  from Order order_1, Item item where item.order=order_1;

either syntax has advantages and disadvantages, so you should choose depending on situation.

It is worth noting that there is also an implicit notation, which might make unnecessary to use the join at all:

select item.order.code, item.code, item.quantity from Item item;

I suspect it is because order is a reserved word (as in order by). Try doing FROM Orders o instead.

I think you'll have other problems too though.

Look at what you are selecting (a bunch of random column values), and what you are expecting Hibernate to return you (List<Orders>).

I expect you won't get back List<Orders> because the structure of your query will force hibernate into SQL mode instead of HQL. In this mode, you'll just get back a List<Object[]> where each Object[] is a set of those four columns you are selecting.

This link explains what you can expect to get back with different queries:

http://www.laliluna.de/jpa-hibernate-guide/ch11s02.html