JPQL查询:如何过滤关系中的行? [英] JPQL query: how to filter rows on a relationship?

问题描述

我是JPA 2.0的新手，有几件事我不理解.

I'm new to JPA 2.0 and there are few things I don't understand.

我有几张桌子:

    CUST table (for customers)
    --------------------------
    CUST_ID   (pk, integer)
    CUST_NAME (varchar)

和

    ORD table (for orders)
    ----------------------
    ORD_ID     (pk, integer)
    ORD_STATUS (char) can be: 'N' for new, 'S' for shipped, 'D' for delivered
    CUST_ID    (fk, integer)

这种关系是简单的一对多"(每个客户可以下很多订单).

The relationship is a simple "one to many" (every customer can place many orders).

表的内容:

    CUST_ID | CUST_NAME
    -------------------
     1      | elcaro
     2      | tfosorcim
     3      | elppa

和

    ORD_ID | ORD_STATUS | CUST_ID
    -----------------------------
     2     | N          | 1
     3     | N          | 1
     4     | N          | 1
     5     | S          | 1
     6     | S          | 1
     7     | D          | 1
     8     | D          | 1
     9     | D          | 1
     10    | D          | 2
     11    | N          | 2
     12    | S          | 3
     13    | S          | 3

这是我注释班级的方式:

Here's how I annotated my classes:

    @Entity(name = "Customer")
    @Table(name = "CUST")
    public class Customer implements Serializable
    {
        private static final long serialVersionUID = 1L;

        @Id
        @Column(name = "CUST_ID")
        private Integer id;

        @Column(name = "CUST_NAME")
        private String name;

        @OneToMany(mappedBy = "customer")
        private List<Order> orders;

        // Default constructor, getters and setters (no annotations on these)
    }

和

    @Entity(name = "Order")
    @Table(name = "ORD")
    public class Order implements Serializable
    {
        private static final long serialVersionUID = 1L;

        @Id
        @Column(name = "ORD_ID")
        private Integer id;

        @Column(name = "ORD_STATUS")
        private Character status;

        @ManyToOne
        @JoinColumns
        (
          {
            @JoinColumn(name = "CUST_ID", referencedColumnName = "CUST_ID")
          }
        )
        private Customer customer;

        // Default constructor, getters and setters (no annotations on these)
    }

一切正常，下面的JPQL查询产生了我期望的结果:

Everything works just fine, the following JPQL query yields the results I expected:

`select c from Customer c`

它返回三个类型为Customer的对象，每个对象都包含属于该客户的订单.

it returns three objects of type Customer, each of which contains the orders that belong to that customer.

但是现在，我想提取状态为"N"的订单以及相关的订单(当然，仅状态为"N"的订单)的客户列表. 回顾过去，我会写这样的SQL查询:

But now, I want to extract the list of customers that have orders in status 'N', along with the associated orders (only the status 'N' orders, of course). Back in the good ol' days I would have written an SQL query like this:

    select      c.cust_id,
                c.cust_name,
                o.ord_id,
                o.ord_status
    from        cust c
    inner join  ord o on (o.cust_id = c.cust_id)
    where       o.ord_status = 'N'

，它将返回以下结果集:

and it would have returned the following result set:

    CUST_ID | CUST_NAME | ORD_ID | ORD_STATUS
    -----------------------------------------
     1      | elcaro    | 2      | N
     1      | elcaro    | 3      | N
     1      | elcaro    | 4      | N
     2      | tfosorcim | 11     | N

但是，以下JPQL查询不会产生预期的结果:

The following JPQL query, however, doesn't yield the expected results:

`select distinct c from Customer c join c.orders o where o.status = 'N'`

它返回正确的一组客户(客户'elppa'没有任何状态'N'订单，并且被正确排除)，但是每个客户都包含完整的订单集，无论状态如何. 似乎只评估"where"子句以确定必须提取哪组客户，然后持久性提供程序开始导航关系以提取全部订单. 想一想，我必须承认这是有道理的.

it returns the correct set of customers (customer 'elppa' doesn't have any status 'N' order and is correctly excluded), but each customer contains the full set of orders, regardless of the status. It seems that the 'where' clause is only evaluated to determine which set of customers has to be extracted and then the persistence provider starts to navigate the relationship to extract the full set of orders. Thinking a little about it, I must admit that it makes sense.

然后我尝试了另一个JPQL查询:

I then tried out another JPQL query:

`select c, o from Customer c join c.orders o where o.status = 'N'`

此JPQL查询所产生的结果与上一个SQL查询所产生的结果相似:每个结果(预期为4个结果)是一个2对象数组，第一个对象的类型为Customer，第二个对象的类型为命令.但是，同样，类型Customer的对象包含完整的相关订单集(这是我这次所期望的).更不用说现在订单不包含在Customer对象中，而是像SQL结果集中那样单独返回的事实.

this JPQL query yields results that are similar to the ones produced by the previous SQL query: each result (4 results as expected) is a 2-object array, the first object is of type Customer and the second object is of type Order. But, again, the objects of type Customer contain the full set of related orders (as I expected, this time). Not to mention the fact that now the orders are not contained in the Customer objects, but are returned separately, just as in an SQL result set.

现在的问题是: 是否可以编写一个JPQL查询来过滤掉，不仅没有状态为"N"的订单的客户，而且还过滤掉状态也不为"N"的相关订单(在关系导航期间获取的)? 我想要得到的是2个客户的结果，其中每个客户仅包含其状态为"N"个订单.

Now the question is: Is it possible to write a JPQL query that filters out, not only the customers that don't have an order in status 'N', but the related orders (fetched during relationship navigation) that are not in status 'N' as well? What I'd like to be able to get is a 2-customer result where each customer contains only its status 'N' orders.

我阅读了Java EE 6教程，其中一个示例(订购应用程序)具有类似于我的架构，但是找不到这样的查询(在下载的源代码中).

I read the Java EE 6 Tutorial and one of the examples (the Order Application) has a schema that is similar to mine, but I couldn't find a query like this (in the downloaded source code).

尽管我认为以上是标准行为，但我使用Oracle Weblogic 12c服务器(通过其Eclipse适配器)，而持久性提供程序似乎是EclipseLink.

Although I think the above is standard behavior, I use an Oracle Weblogic 12c server (through its Eclipse adapter) and the persistence provider appears to be EclipseLink.

谢谢.

最诚挚的问候，

Stefano

推荐答案

JPA处理对象，并且对象具有身份并且无论如何查询都是相同的.无论您的where子句是什么，返回的Customer对象仍然是相同的Customer对象，并且应具有相同的关系.这对于缓存，对象标识和一致性很重要.

JPA deals with objects, and an object has an identity and is the same no matter how it is queried. No matter what your where clause is, the Customer objects returned are still the same Customer objects and should have the same relationships. This is important for caching, object identity and consistency.

您的第二个查询可能是您想要做的正确方法.可以使用JOIN FETCH上的别名(从EclipseLink 2.4开始)来执行您想做的事情，但不建议这样做.

Your second query is probably the correct way to do what you want. It is possible to do what your are trying to do using an alias on a JOIN FETCH (as of EclipseLink 2.4), but not recommended.

看， http://wiki.eclipse.org/EclipseLink/UserGuide/JPA /Basic_JPA_Development/Querying/JPQL#JOIN_FETCH

这篇关于JPQL查询:如何过滤关系中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！