I have one-to-many association between ORDER and ITEM.

ORDER.ID
ORDER.ITEMS

ITEM.ID
ITEM.PRICE

I want to find all the orders when ITEM.PRICE=100

This is my code

List tempSets = session.createCriteria(Order.class)
.createCriteria("items")
.add(Restrictions.eq("price",new Long(100)))
.list();

It finds correct number of Orders but when I ask for order.getItems() it gives me all the items for that order and what I need is the order only with items that match this restriction (price==100)

Hibernate generates this query:
select o.*, i.* from order o inner join item i on o.id=i.order_id where i.price=100

which is correct, but somehow later on it does select all items for this orders. Is there a way to restrict also association query?

How can I achieve it?

Thanks

Would this help to get only those items with price=100?

Yes sort of.. I mean I did it.. and it works, but I would expect hibernate to have a bit better way of doing that.. is there? :)

for (Iterator i=orders.iterator();i.hasNext();) {
Order tempOrder = (Order)i.next();
List items = (List)session.createFilter(tempOrder.getItems(), " where this.price=100" )
.setParameter( 0, new Long(100) )
.list();
tempOrder.setItems(items);
}

Thanks anyway!

Instead of iterating through Order you could use HQL join condition and specify parmeters for Order and Item in the same WHERE clause.

Instead of iterating through Order you could use HQL join condition and specify parmeters for Order and Item in the same WHERE clause. That would result in 1 SQL v/s 1 to n SQLs in the code snippet you pasted.

Can you please post sample of that..

And of course code I posted is very inneficient and I'm quite surprised there is not better way of doing it with Criteria object.

Thanks

You could so something like this:

.

I beleive you can do a similar thing using Criteria queries as well. It might go something like this:



Replace cat and kitten with Order and Items appropriately. Refer to Section 15.4 of Reference guide.