Hi,

I'm a newcomer to Hibernate, so bear with me.

I've got a problem where I'd like to do a select on 3 tables, a master, a detail and a subdetail. Let's say an order, orderline and product. I'd like to use a single query with a where clause on the product to populate my model. Let's say I'd like to select all orders that have orderlines referring to products that have less than 5 in stock (stock is a field of product). I need fields from all tables, so I'd like to get an object hierarchy that has orders, orderlines and products. After selecting I'd like to send an email for each order.

What is the best strategy to do this?

I've tried using the query API, but this has given me no end of problems. I created a getOrdersWithLowStock in OrdersDAO object. This gives me a couple of order objects, but when I want to drill down (order->orderlines) with getOrderLines, it gives me all orderlines, not just the ones I'd queried. I can make a getOrderLinesWithLowStock in OrderLinesDAO, but that would result in another query, even though I already have this information.

I've also tried to select all products with stock < 5 and work my way up. But this gets very complex, very fast. I'd need the products ordered by ordeline and then order. Then I'd need to traverse down again to be able to send the email.

Does anybody have any advice? Sorry if I didn't explain myself enough, I hope my problem is clear.

Regards,

Arjan