org.hibernate.hql.ast.QueryTranslatorImpl list注意：使用集合提取指定的firstResult / maxResults;在内存中应用 [英] org.hibernate.hql.ast.QueryTranslatorImpl list ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory

问题描述

我遇到了一个问题，我在JPA中有一个查询。因为我有一些集合，我需要使用左连接读取或内部连接读取

我的问题是使用 setFirstResult 和 setMaxResult ，以便返回精确数目的结果。每次我看到整个结果都回来了，只有在使用maxResult之后。

有没有办法让maxResult成为之前的？

非常感谢！

这里是更多信息：

我的问题是当我使用它时：

  startIndex = 0; 
 maxResults = 10; 
 query.setFirstResult（startIndex）; 
 query.setMaxResults（maxResults）; 
  

我在日志中看到这条消息：

< blockquote>
org.hibernate.hql.ast.QueryTranslatorImpl list
注意：使用collection fetch指定firstResult / maxResults;
在内存中应用！

我看到200结果返回（以log记录），并且在HashSet之后我终于我要求的10个结果。

它在内存中似乎带回了200个结果，并在maxResults应用到内存中。

我正在搜索是否有任何方法可以获取并限制结果数量。

我使用了一种解决方法，我首先查询了我的订单的id，但没有进行任何获取，使用了maxResult。
一切正常，它使用限制指令。
在我使用我的大查询与获取和限制在列表中的结果内带回第一个。

这是我的完整的查询没有我的解决方法（注意，没有作为@Bozho谈话生成的限制）：
$ b

  select o from Order o 
 left join fetch o.notes note 
 left join fetch o.orderedBy orderedBy 
 left join fetch orderedBy.address addressOrdered 
 left join fetch orderedBy.language orderedByLg 
左连接抓取orderedByLg.translations orderedByLgTtrad 
左连接抓取o.deliveredTo deliveredTo 
左连接抓取deliverTo.address addressDelivered 
左连接抓取deliverTo.language deliveredToLg 
左连接fetch deliveredToLg.translations 
 left join fetch o.finalReceiptPlace finalReceiptPlace 
 left join fetch finalReceiptPlace.address addressFinalReceiptPlace 
 left join fetch finalReceiptPlace。语言finalReceiptPlaceLg 
左连接读取finalReceiptPlaceLg.translations 
内部连接读取o.deliveryRoute传递
左连接读取delivery.translations 
内部连接读取o.type orderType 
左连接fetch orderType.translations 
内部连接读取o.currency货币
左连接读取currency.translations 
左连接读取o.attachments 
左连接读取note.origin orig 
 left join fetch orig.translations 
 left join fetch o.supplier sup 
 left join fetch sup.department dep 
 left join fetch o.stateDetail stateD 
 inner join fetch stateD.state stat 
 where 1 = 1 and o.entryDate> =：startDat 
  

解决方案

TL; DR Hibernate不知道为了获取指定数量的Order对象而需要展开多少行的扁平连接查询，所以它必须在内存中加载整个查询。为了理解Hibernate为什么这么做，你需要了解Hibernate如何执行ORM（对象 - 关系映射）涉及JPA实体。

考虑您订单的一组简化实体。 Class Order 包含2个字段： number 和 customerId 和a订单行列表。类 OrderLine 包含 productCode 和数量字段，以及一个 uid 键以及对父Order的引用。

这些类可以这样定义：

pre $ @Entity
@Table（name =ORDER）
public class Order {
@ID
@Column（name =NUMBER）
私人整数;
@Column（name =CUSTOMER_ID）
private Integer customerId;
@OneToMany（mappedBy =order，fetch = FetchType.LAZY）
@OrderBy
private List< OrderLine> orderLineList;

.... //其余的类
}

@Entity
@Table（name =ORDER_LINE）
公共类OrderLine
{
@ID
@Column（name =UID）
private Integer uid;
@Column（name =PRODUCT_CODE）
private Integer productCode;
@Column（name =QUANTITY）
private整数数量;
@Column（name =ORDER_NUMBER）
私人整数orderNumber;
@ManyToOne（fetch = FetchType.LAZY，可选= false）
@JoinColumn（name =ORDER_NUMBER，referencedColumnName =NUMBER，insertable = false，updatable = false）
private Order订购;

.... //其余班级
}


现在，如果您对这些实体执行了以下JPQL查询：

pre $ SELECT $ FROM $ LEFT JOIN FETCH o.orderLineList

然后Hibernate将这个查询作为一个'拼合'SQL查询执行，类似于以下内容：

  SELECT o.number，o.customer_id，ol.uid，ol.product_code，ol.quantity，ol.order_number 
 FROM order o LEFT JOIN order_line ol on order_line.order_number = order.number 
  

这会给出如下结果：

  | o.number | o.customer_id | ol.uid | ol.product_code | ol.quantity | 
 | ========== | =============== | ======== | ========= ======== | ============= | 
 | 1 | 123 | 1 | 1111 | 5 | 
 | 1 | 123 | 2 | 1112 | 6 | 
 | 1 | 123 | 3 | 1113 | 1 | 
 | 2 | 123 | 4 | 1111 | 2 | 
 | 2 | 123 | 5 | 1112 | 7 | 
 | 3 | 123 | 6 | 1111 | 6 | 
 | 3 | 123 | 7 | 1112 | 5 | 
 | 3 | 123 | 8 | 1113 | 3 | 
 | 3 | 123 | 9 | 1114 | 2 | 
 | 3 | 123 | 10 | 1115 | 9 | 
 ... etc 
  

Hibernate会用来'reconstruct' Order 具有附加列表 OrderLine 子对象的对象

然而，既然每个订单的订单行数是随机的，Hibernate无法知道该查询需要多少行才能获取指定的最大数量的 Order 对象。因此，在丢弃结果集的其余部分之前，它必须接受整个查询并在内存中构建对象，直至其具有适当的量。它产生的日志警告暗指：



 注意：使用集合提取指定的firstResult / maxResults;在内存中应用！ 
  

我们现在才发现这些查询可能会对服务器内存使用产生重大影响，我们的服务器在尝试这些查询时出现内存不足错误时遇到了问题。

顺便说一下，现在我会说，这主要是理论对我而言，我不知道实际的Hibernate代码是如何工作的。当你用Hibernate记录它产生的SQL语句时，你可以从日志中收集大部分内容。

---

UPDATE ：
最近我发现了上面的一个小问题。

考虑一个名为 Shipment 这是一个或多个订单行。

货件实体将会拥有 @ManyToOne 与订单实体的关联

假设您有相同订单的2件货件，其中有4件行。

如果您对以下内容执行JPQL查询：

  SELECT s FROM运输的左连接s.order o左连接FETCH o.orderLineList 
  

您会期望（或者至少我做过）获得2个发货对象，每个发货对象都引用相同的Order对象，该对象本身将包含4条线。

不，再错了！事实上，您会得到2个Shipment对象，每个对象都指向同一个Order对象，其中包含 8 行！是的，订单中的行重复！是的，即使你指定了DISTINCT子句。

如果你在SO或其他地方（最着名的是Hibernate论坛）研究这个问题，你会发现根据Hibernate的功能，这实际上是一个功能而不是bug。有些人实际上 想 这种行为！

去图。

I'm facing a problem, I have a query in JPA. as I have some collections I need to use left join fetch or inner join fetch

My problem is in using the setFirstResult and setMaxResult in order to bring back a precise number of result. every time i see the whole result is bring back AND only AFTER the maxResult is used.

Is there any way to make the maxResult before ?

Thanks a lot !

here it is more information :

my problem is when i use that :

startIndex = 0;
maxResults = 10;
query.setFirstResult(startIndex);
query.setMaxResults(maxResults);

I see this message in my log :

7 juin 2011 09:52:37 org.hibernate.hql.ast.QueryTranslatorImpl list ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory!

I see the 200 result coming back (in log) and after in the HashSet i have finally the 10 result i ask.

its seems in memory is bring back the 200 result and after the maxResults is applied in memory.

I'm searching if there is any way to be able to fetch and limit the number of result.

I used a workaround, I make a first query to ask the id of my order , without any fetch, used the maxResult. everything work perfectly it's used the limit instruction. After I use my "big" query with the fetch and limit the result inside the list of id bring back in the first one.

here it is my full query without my workaround (notice that there is no limit generated as talk by @Bozho ):

select o from Order  o
   left join fetch o.notes note
   left join fetch o.orderedBy orderedBy
   left join fetch orderedBy.address addressOrdered 
   left join fetch orderedBy.language orderedByLg 
   left join fetch orderedByLg.translations orderedByLgTtrad
   left join fetch o.deliveredTo deliveredTo 
   left join fetch deliveredTo.address addressDelivered 
   left join fetch deliveredTo.language deliveredToLg
   left join fetch deliveredToLg.translations 
   left join fetch o.finalReceiptPlace finalReceiptPlace
   left join fetch finalReceiptPlace.address addressFinalReceiptPlace 
   left join fetch finalReceiptPlace.language finalReceiptPlaceLg 
   left join fetch finalReceiptPlaceLg.translations
   inner join fetch o.deliveryRoute delivery
   left join fetch delivery.translations
   inner join fetch o.type orderType
   left join fetch orderType.translations 
   inner join fetch o.currency currency
   left join fetch currency.translations
   left join fetch o.attachments 
   left join fetch note.origin orig
   left join fetch orig.translations
   left join fetch o.supplier sup  
   left join fetch sup.department dep 
   left join fetch o.stateDetail stateD
   inner join fetch stateD.state stat  
where 1=1 and o.entryDate >= :startDat

解决方案

TL;DR Hibernate doesn't know how many rows of the flattened, joined query it needs to get the specified number of the Order objects, so it has to load the whole query in memory. See below for an explanation.

To understand why Hibernate does this, you need to understand how Hibernate does the ORM (Object-Relational Mapping) involved for JPA Entities.

Consider a simplified set of Entities for your order. Class Order contains 2 fields: number and customerId and a list of order lines. Class OrderLine contains productCode and quantity fields, as well as a uid key and a reference to the parent Order.

These classes may be defined thus:

@Entity
@Table(name = "ORDER")
public class Order {
    @ID
    @Column(name = "NUMBER")
    private Integer number;
    @Column(name = "CUSTOMER_ID")
    private Integer customerId;
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    @OrderBy
    private List<OrderLine> orderLineList;

    .... // Rest of the class
}

@Entity
@Table(name = "ORDER_LINE")
public class OrderLine
{
    @ID
    @Column(name = "UID")
    private Integer uid;
    @Column(name = "PRODUCT_CODE")
    private Integer productCode;
    @Column(name = "QUANTITY")
    private Integer quantity;
    @Column(name = "ORDER_NUMBER")
    private Integer orderNumber;
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ORDER_NUMBER", referencedColumnName = "NUMBER", insertable = false, updatable = false)
    private Order order;

    .... // Rest of the class
}

Now, if you performed the following JPQL query on these Entities:

SELECT o FROM Order o LEFT JOIN FETCH o.orderLineList

then Hibernate performs this query as a 'flattened' SQL query similar to the following:

SELECT o.number, o.customer_id, ol.uid, ol.product_code, ol.quantity, ol.order_number
FROM order o LEFT JOIN order_line ol ON order_line.order_number = order.number

which would give a result like this:

| o.number | o.customer_id | ol.uid | ol.product_code | ol.quantity |
|==========|===============|========|=================|=============|
| 1        | 123           | 1      | 1111            | 5           |
| 1        | 123           | 2      | 1112            | 6           |
| 1        | 123           | 3      | 1113            | 1           |
| 2        | 123           | 4      | 1111            | 2           |
| 2        | 123           | 5      | 1112            | 7           |
| 3        | 123           | 6      | 1111            | 6           |
| 3        | 123           | 7      | 1112            | 5           |
| 3        | 123           | 8      | 1113            | 3           |
| 3        | 123           | 9      | 1114            | 2           |
| 3        | 123           | 10     | 1115            | 9           |
...etc

which Hibernate would use to 'reconstruct' Order objects with attached lists of OrderLine sub-objects.

However, since the number of order lines per order is random, there is no way for Hibernate to know how many rows of this query to take to get the specified maximum number of Order objects required. So it has to take the whole query and build up the objects in memory until it has the right amount, before discarding the rest of the result set. The log warning it produces alludes to this:

ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory!

We're only just discovering now that these queries can have a significant impact on server memory use, and we've had issues with our server falling over with out of memory errors when these queries are attempted.

By the way, I will say now that this is mostly just theory on my part and I have no idea how the actual Hibernate code works. Most of this you can glean from the logs when you have Hibernate logging the SQL statements it generates.

---

UPDATE: Recently I have discovered a little 'gotcha' with the above.

Consider a third Entity called Shipment which is for one or more Lines of an Order.

The Shipment entity would have a @ManyToOne association to the Order entity.

Let's say you have 2 Shipments for the same Order which has 4 Lines.

If you perform a JPQL query for the following:

SELECT s FROM Shipment s LEFT JOIN s.order o LEFT JOIN FETCH o.orderLineList

You would expect (or at least I did) to get 2 shipment objects back, each with a reference to the same Order object, which itself would contain the 4 Lines.

Nope, wrong again! In fact, you get 2 Shipment objects, each referring to the same Order object, which contains 8 Lines! Yes, the Lines get duplicated in the Order! And yes, that is even if you specify the DISTINCT clause.

If you research this issue here on SO or elsewhere (most notably the Hibernate forums), you'll find that this is actually a feature not a bug, according to the Hibernate powers that be. Some people actually want this behaviour!

Go figure.

这篇关于org.hibernate.hql.ast.QueryTranslatorImpl list注意：使用集合提取指定的firstResult / maxResults;在内存中应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！