如何过滤JPA / JPQL中的集合？ [英] 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;
...

现在在我的JPQL中，我想用CustomerOrder返回这些客户。成本> 1000。例如，有三个客户A，B和C.A有两个订单，成本分别为1000和2000。 B有三个订单，成本分别为2000,3000和500。 C有一个订单，成本= 500。现在我想获得三个客户：A仅返回成本= 2000的订单; B以2000和3000返回订单; C返回一个空订单集合。

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

我怎样才能在JPQL或者Hibernate中做这个特别的事情？

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

推荐答案

发布的查询等同于

The query posted is equivalent to

select c from Customer c inner join c.customerOrders o where o.cost > 1000

只返回所有至少有一个订单的成本大于1000的客户。

which simply returns all customers that have at least one order with cost greater than 1000.

我会建议反转连接和选择顺序 - 它的语义相同，但结构不同于您所期望的结果：

I would suggest to inverse join and select orders - it's semantically the same but structurally different from your desired result though:

select o from CustomerOrder o where o.cost > 1000

现在，Hibernate具有称为Filter的非JPA功能，可以完成您正在寻找的内容 - 看到这里：
http://www.hibernate.org/ hib_docs / reference / en / html / filters.html

Now, Hibernate has non-JPA feature called Filter that should accomplish exactly what you are looking for - see here: http://www.hibernate.org/hib_docs/reference/en/html/filters.html

这篇关于如何过滤JPA / JPQL中的集合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！