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();

Xavi López · Answer 1 · 2023-02-24T23:08:13.837

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 orders
INNER JOIN orders.orderProcessing as op
INNER JOIN orders.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 orders, OrderProcessing op, Product product
WHERE op.u_id = orders.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.

Good answer, but I think `orders` in your `SELECT` should be `order`, the alias. — beechovsky, Feb 24 '23 at 19:30
@beechovsky Ah, ty, finally spotted where that nasty bug was coming from, it has been bothering me exactly for 10 years :) — Xavi López, Feb 24 '23 at 23:06

score 11 · Answer 2 · edited Dec 19 '13 at 13:13

11

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;

edited Dec 19 '13 at 13:13

rzymek

9,064
2
45
59

answered Feb 25 '13 at 14:00

Grim

1,608
9
12

It's still not working. SELECT op.username, op.email, orders.p_id, orders.o_id" + "FROM Orders order" + "INNER JOIN order.orderProcessing as op " + "INNER JOIN order.product as product" + "Where product.p_id="+p_id+ "ORDER BY op.username – Feb 25 '13 at 14:25
If that string concatenation is taken straight from the code you're using you're missing a number of spaces - before and after _from Orders order_, before the _where_ and the _order by_ clause. If that's not the problem, you might need to post the error you're getting before someone can figure out what's happening (and if possible the mapping/structure of your business objects) – Grim Feb 25 '13 at 14:29
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: Orders near line 1, column 60 [SELECT op.username, op.email, orders.p_id, orders.o_idFROM Orders orderINNER JOIN order.orderProcessing as op INNER JOIN order.product as productWhere product.p_id=208ORDER BY op.username] – Feb 25 '13 at 14:34
The error is clearly stating that you have `orders.p_id` in the `SELECT` clause. You've got to use the same alias you gave in the `FROM` clause, `order`. `SELECT ..., order.p_id, order.o_id`. – Xavi López Feb 25 '13 at 15:07
@DevSingh like i pointed out previously, there are many spaces missing - before 'FROM' and before the first 'INNER JOIN', before 'Where' and before 'ORDER BY'. Also, see _Mr Spoon_ answer below since both of its notes are true as well (_order_ can not be used as alias - my mistake here for letting it slip in my answer - and you're not going to get back an Orders list, but some scalar values). – Grim Feb 25 '13 at 15:22
@Grim : SELECT op.username, op.email, orders.p_id, orders.o_id, product.listed_price " + "FROM Orders orders " + "INNER JOIN OrderProcessing op ON op.u_id = orders.u_id " + "INNER JOIN Product product ON product.p_id = orders.p_id WHERE product.p_id = '"+p_id + "' " + "ORDER BY op.username – Feb 25 '13 at 16:07
The spaces seem right - change back to joins to the form you used above ('INNER JOIN orders.orderProcessing as op' and likewise for Product) – Grim Feb 25 '13 at 16:54
@Grim 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 – Feb 26 '13 at 05:16

score 1 · Answer 3 · answered Feb 25 '13 at 15:14

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

I'm getting an 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] — , Feb 26 '13 at 05:42

score 0 · Answer 4 · answered Oct 18 '22 at 11:10

I suggest using Referencing Associated Entities.

In SQL, you will often join tables together to search for results by related Entities. In JPQL, you can reference the value of associated Entities by accessing them directly as Entity attributes in the query.

private static final String FIND_ORDERS_WITH_PRODUCTS =
       "from Order o " +
       "where :product member of o.products";

Note that Order is the entity class and products is the associated list.

then you can use it in your method like that:


List<Order> findOrders(Outfit o){
   TypedQuery<Order> query = entityManager.createQuery(FIND_ORDERS_WITH_PRODUCTS, Order.class);
   query.setParameter("outfit", o);
   return query.getResultList();
}

Inner Joins Query in HQL

4 Answers4