I'm a newbie with Hibernate so I have difficulties to convert my SQL requests. I have the following SQL request :
SELECT product_name, sum(quantity)
FROM final_product
JOIN customer_order_entry ON final_product.final_product_id = customer_order_entry.final_product_id
JOIN customer_order ON customer_order_entry.customer_order_id = customer_order.customer_order_id
JOIN customer ON customer_order.customer_id = customer.customer_id
JOIN salesman ON customer.salesman_id = salesman.salesman_id
WHERE salesman.salesman_id = '1'
GROUP BY final_product.final_product_id
LIMIT 3;
I want to have the same result with hibernate criteria.
But I don't know how to specify that I only want two rows as result.
Plus, I can't figure out how should I recover the result (due to my "join", the result doesn't belong to only one class so I can't put my result in List<Classname>
as I used to do).
For this moment, I coded this :
session.createCriteria(FinalProduct.class)
.add(Projections.sum("quantity"))
.setFetchMode("CustomerOrderEntry", FetchMode.JOIN)
.setFetchMode("CustomerOrder", FetchMode.JOIN)
.setFetchMode("Customer", FetchMode.JOIN)
.setFetchMode("Salesman", FetchMode.JOIN)
.add(Restrictions.eq("Salesman.salesmanId", new Integer(1))
.add(Projections.groupProperty("FinalProduct.finalProductId"))
c.setMaxResults(3);
Please, could you give me any clue ?