Friday, May 15, 2009

How to filter collection in JPA/JPQL?

I have two entities:

public class Customer  implements {
    @OneToMany(fetch=FetchType.EAGER, mappedBy="customer")
    private Set<CustomerOrder> customerOrders;

public class CustomerOrder  implements {

    private double cost;

    public Customer customer;

Now in my JPQL, I want to return those customers with their CustomerOrder.cost>1000. For example, there are three customers A, B and C. A has two orders with cost=1000 and 2000 respectively. B has three orders with cost=2000,3000 and 500 respectively. C has one order with cost=500. Now i want to get the three customers: A returns the orders with cost=2000 only; B returns the orders with 2000 and 3000; C returns an empty orders collection.

But the following will always return the full collection:

select c from Customer c, in(c.customerOrders) o where o.cost>1000

How can I do that in JPQL or in Hibernate in particular?

No comments:

Post a Comment