How to get multiple entities from a Hibernate SQL join

Question

I have 4 table:

Orders(orderID, orderDate, orderItem (OneToMany))

OrderItem(id, order(manyToOne), book (manyToOne), quantity)

Book (id, title, cost)

User(id, username, password)

Here is my query in SQL:

String sql = "SELECT orders.id, book.title, orderitem.quantity
    FROM orderitem INNER JOIN book ON book.id = orderitem.book_id INNER JOIN orders ON orders.id = orderitem.orders_id WHERE user_id = 1;

(user_id is the foreign key of User in Orders table)

(orders_id is the foreign key of Orders in OrderItem table)

List<OrderItem> orderBookInfo = (List<OrderItem>) session.createSQLQuery(sql); // returns List<Object[]> why?!

This query result comes from joining of 3 tables (Book, Order, OderItem)

And this is the result in table:

Question is how can i assign each result's column to it's corresponding properties?

For example:

orderBookInfo.order.id = (first location of orderBookInfo)

orderBookInfo.book.title = (second location of orderBookInfo)

Answer 1

You need to execute an Entity query instead. Assuming you already mapped the entities properly, this is how the HQL query would look like:

SELECT o
FROM orderitem oi
JOIN FETCH oi.book
JOIN FETCH oi.orders 
JOIN FETCH oi.user u 
WHERE u.id = 1;