JPA:@OneToMany(fetch = FetchType.EAGER)，分页和重复项 [英] JPA: @OneToMany(fetch = FetchType.EAGER), pagination and duplicates

问题描述

我将eclipselink用作JPA提供程序.我有一个实体文章，其中有许多作者(此字段标记为@OneToMany(fetch = FetchType.EAGER)).文章和作者仅使用一个sql查询(JOIN)加载在一起.

I use eclipselink as a JPA provider. I have an entity Article which has many Authors (this field is marked @OneToMany(fetch = FetchType.EAGER)). Article and authors are loaded together using only one sql query (JOIN).

要使用分页，我使用以下代码:

To work with pagination I use the following code:

String queryString="SELECT DISTINCT e FROM Article e LEFT JOIN FETCH e.authors";
Query query = em.createQuery(queryString);
query.setHint("eclipselink.join-fetch", "e.authors"); 
query.setFirstResult(position);
query.setMaxResults(amount);

据我所知，当使用setFirstResult和setMaxResults时，它们在sql查询中定义limit part.结果，我有两个问题:

When setFirstResult and setMaxResults are used they, as I understand, define limit part in sql query. As a result I have two problems:

1. 当我想获得10篇文章时，我只会得到两篇文章，其中第一篇文章有​​4位作者，第二篇文章有6位作者
2. 文章重复-我在不同页面上有一篇不同作者的文章.

如何解决此类问题?

推荐答案

FirstResult和MaxResult不能按预期使用，因为它们是对数据库SQL性能的操作，如

FirstResult and and MaxResult do not work as you would expect when using fetch JOINs over collections as they are database SQL performance operations as described here.

如果您需要绝对的分页结果，请不要在集合映射上使用访存联接-使用 batch.type 用于限制辅助查询.

Don't use a fetch join over a collection mapping if you need absolute pagination results - use batch fetching instead. This will use 2 queries, allowing the first to correctly return the required rows with pagination, and the second to return the rows required for the collection relationship. Specify the IN or batch.type be used to limit the secondary query.

这篇关于JPA:@OneToMany(fetch = FetchType.EAGER)，分页和重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！