Friday, May 15, 2009

How to filter collection in JPA/JPQL?

I have two entities:

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


@Entity
public class CustomerOrder  implements java.io.Serializable {
....       

    private double cost;

    @ManyToOne
    @JoinColumn(name="CUST_ID")
    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