英文:
Hibernate JPA fetch joins should not be used in paged queries
问题
> 在分页查询（例如setFirstResult()或setMaxResults()）中不应使用获取连接，也不应与scroll()或iterate()功能一起使用。
没有进一步解释为什么像下面这样的JPQL查询应该避免使用：
List<Person> persons = entityManager.createQuery(
"select distinct pr " +
"from Person pr " +
"left join fetch pr.phones ", Person.class )
.setFirstResult(15)
.setMaxResults(10)
.getResultList();
有人可以解释一下这个禁令背后的原因吗？
更新
我找到了这篇帖子，里面解释了一切。
更新2
为了更明确：
1）为什么对于1-1的延迟关系是被禁止的，因为不会丢失任何行吗？
2）为什么它不能在幕后使用2个select来实现？（
像这样的内容：
select pr.id from Person pr where ... 分页条件
select distinct pr from Person pr left join fetch pr.phones where pr.id in (:ids_from_previous_select)
）
英文:
In the Hibernate User Guide chapter 15.14 it says:
> Fetch joins should not be used in paged queries (e.g. setFirstResult()
> or setMaxResults()), nor should they be used with the scroll() or
> iterate() features.
There is no further explanation why JPQL queries like this one:
List<Person> persons = entityManager.createQuery(
"select distinct pr " +
"from Person pr " +
"left join fetch pr.phones ", Person.class )
.setFirstResult(15)
.setMaxResults(10)
.getResultList();
should be avoided.
Can someone explain what are the rationales behind this interdiction?
UPDATE
I found out this post that explains everything.
UPDATE 2
To be more precise:
-
Why it is forbidden for 1-1 lazy relationships since no row can be lost?
-
Why it is not implemented with 2 selects behind the scene? (
Something like:select pr.id from Person pr where ... pagination select distinct pr from Person pr left join fetch pr.phones where pr.id in (:ids_from_previous_select)
)
答案1
得分: 5
这是因为如果直接在 SQL 中使用 JOIN 并直接应用 limit offset，可能会导致相关实体列表被截断。
例如，如果我们有一个具有 Friends 的 Person，并且将获取前5条记录（第一页，每页5个项目），您最终将得到1个带有5个朋友的人，而实际上，您希望有5个带有所有朋友的人。
为了克服这个问题，Hibernate 将在不带限制的情况下执行整个查询，并在聚合后应用限制/偏移。这将使可迭代的游标变得完全不可迭代。
HHH000104: 针对集合获取指定了 firstResult/maxResults；正在应用内存！
英文:
This is because if use JOIN and apply limit offset directly to the SQL, you will end up with possibly truncated list of related entities.
Eg if we will have Person with Friends and will take first 5 records (first page, 5 items of size) you wil end up with 1 person with 5 frends where in fact, you wated to have 5 persons with all friends.
To overcome that, hiberante will execute whole query without limit and will apply limit/offset after aggregation. This will make eg iterable cursor not iterable at all.
HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
答案2
得分: 0
这不会在一对一关联中受到限制，完全没有问题。但是在集合，即一对多关联中会有问题。
我认为当进行分页时，可以使 Hibernate 更有效地适应获取连接，但它只没有以这种方式实现。
我确实为 Blaze-Persistence 实施了你提到的那种分页，它是在 JPA/Hibernate 之上工作的库：https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination
甚至还有一些进一步的优化：
- 仅在我称之为“id 查询”的情况下执行必要的连接
- id 查询不一定要是单独的查询。实际上，默认情况下，Blaze-Persistence 会将此查询内联到“对象查询”中
- Blaze-Persistence 还通过相同的 API 支持更高效的键集分页。
英文:
It's not forbidden for one-to-one associations, that's totally fine. It's just problematic for collections i.e. *-to-many associations.
I think Hibernate could be adapted to do fetch joins more efficiently when doing pagination, but it just isn't implemented this way.
I did implement the kind of pagination you mention for Blaze-Persistence which is a library that works on top of JPA/Hibernate: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination
There are even a few further optimizations in there:
- Only the necessary joins are done in what I call the "id-query"
- The id query does not necessarily have to be a separate query. In fact, by default, Blaze-Persistence inlines this query into the "object-query"
- Blaze-Persistence also supports the much more efficient keyset pagination through the same API
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论