performance - 为什么 JPA/Eclipselink 即使使用 BatchFetchType.IN 提示也会触发冗余数据库查询？

Question

摘要:

我正在尝试尽量减少基于 JPA 的 Java 应用程序对数据库的查询次数。我指定了 @BatchFetch(BatchFetchType.IN)优化提示，但我仍然看到一些我认为多余和不必要的额外查询。

详情:

考虑一个简单的域模型:我们有发票管理系统。发票与订单具有一对一关系。我们也有客户，它与订单有 OneToMany 关系。 (客户 1->M 订单 1<-1 发票) .查找更多 details here .找到完整的 source code here .
这是实体定义，正如它目前所代表的那样:

Client.java(不包括 getter 和 setter ):

  @Entity(name = "CUSTOMER") 
  public class Customer {
    @Id //signifies the primary key
    @Column(name = "CUST_ID", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long custId;

    @Column(name = "FIRST_NAME", length = 50)
    private String firstName;

    @OneToMany(mappedBy="customer",targetEntity=Order.class,
            fetch=FetchType.LAZY)
    private Collection<Order> orders;
    }

Order.java(不包括 getter 和 setter ):

 @Entity(name = "ORDERS")
 public class Order {
    @Id
    @Column(name = "ORDER_ID", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long orderId;

    @Column(name = "TOTAL_PRICE", precision = 2)
    private double totPrice;

    @OneToOne(fetch = FetchType.LAZY, optional = false, cascade = CascadeType.ALL,  mappedBy = "order")
    private Invoice invoice;

    @ManyToOne(optional = false)
    @JoinColumn(name = "CUST_ID", referencedColumnName = "CUST_ID")
    private Customer customer;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "ORDER_DETAIL", joinColumns = @JoinColumn(name = "ORDER_ID",    referencedColumnName = "ORDER_ID"), inverseJoinColumns = @JoinColumn(name = "PROD_ID",    referencedColumnName = "PROD_ID"))
    private List<Product> productList;

 }

Invoice.java(不包括 getter 和 setter ):

@Entity(name = "ORDER_INVOICE")
public class Invoice {

    @Id
    // signifies the primary key
    @Column(name = "INVOICE_ID", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long invoiceId;

    @Column(name = "AMOUNT_DUE", precision = 2)
    private double amountDue;

    @OneToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "ORDER_ID")
    private Order order;

 }

有了这个模型，我运行了一个简单的测试来获取客户的所有订单。

EntityManagerFactory entityManagerFactory =  Persistence.createEntityManagerFactory("testjpa");     
    EntityManager em = entityManagerFactory.createEntityManager();

    Customer customer = em.find(Customer.class, 100L);

    Collection<Order> orders = customer.getOrders();

    for(Order order: orders){
        System.out.println(order.getInvoice().getInvoiceId());
    }

    em.close();

由于所有内容都是延迟获取的，我们得到了四个查询，如下所示:

1398882535950|1|1|statement|SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = ?)|SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = 100)

1398882535981|0|1|statement|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (CUST_ID = ?)|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (CUST_ID = 100)

1398882535995|1|1|statement|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = ?)|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = 111)

1398882536004|0|1|statement|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = ?)|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = 222)

由于我不希望 N+1 次调用来获取发票，因此我考虑使用批量提取并将总查询减少到 4 个(一个查询来获取所有客户订单的发票)。为此，我更新了我的 Order 实体，如下所示:

更新 - Order.java，为发票添加 BatchFetch。 (不包括 getter 和 setter ):

 @Entity(name = "ORDERS")
 public class Order {
    @Id
    @Column(name = "ORDER_ID", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long orderId;

    @Column(name = "TOTAL_PRICE", precision = 2)
    private double totPrice;

    @BatchFetch(BatchFetchType.IN)
    @OneToOne(fetch = FetchType.LAZY, optional = false, cascade = CascadeType.ALL,  mappedBy = "order")
    private Invoice invoice;

    @ManyToOne(optional = false)
    @JoinColumn(name = "CUST_ID", referencedColumnName = "CUST_ID")
    private Customer customer;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "ORDER_DETAIL", joinColumns = @JoinColumn(name = "ORDER_ID",    referencedColumnName = "ORDER_ID"), inverseJoinColumns = @JoinColumn(name = "PROD_ID",    referencedColumnName = "PROD_ID"))
    private List<Product> productList;

 }

我再次运行相同的测试，并假设将有 3 个查询来获取数据。(一个用于客户，一个用于订单，一个用于批量获取发票)。但是，eclipselink
生成 5 个相同的查询。以下是查询:

1398883197009|1|1|statement|SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = ?)|SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = 100)

1398883197030|0|1|statement|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (CUST_ID = ?)|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (CUST_ID = 100)

1398883197037|1|1|statement|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID IN (?,?))|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID IN (111,222))

1398883197042|1|1|statement|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (ORDER_ID = ?)|SELECT ORDER_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME, CUST_ID FROM ORDERS WHERE (ORDER_ID = 222)

1398883197045|0|1|statement|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = ?)|SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME, ORDER_ID FROM ORDER_INVOICE WHERE (ORDER_ID = 222)

我不明白为什么要生成最后两个查询。任何帮助解释正在发生的事情都会有所帮助。

谢谢!

Answer 1

看起来像 EclipseLink 中的一个错误/问题，因为对象模型中的急切关系的遍历允许在加载引用它的 Order 之前的“in”中加载第二个 Invoice。这会强制 Invoice 在数据库中查询 Order，而不是在缓存中查找它。

您可以通过对 Invoice to Order 关系使用延迟获取来解决此问题。此延迟将允许 EclipseLink 完全构建对象模型，以便在访问时将其保存在缓存中。问题中的代码显示这种关系被标记为 Lazy，但这只是对 JPA 提供者的一个提示，如果不使用代理或字节码编织，则无法在 EclipseLink 中工作，如下所述:
https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Performance/Weaving
https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Performance/Weaving/Dynamic_Weaving

惰性集合不需要编织，仅用于 1:1 和其他优化。