我得到了这个存储库代码:
@Query(value = "select distinct r from Reference r " +
"inner join fetch r.persons " +
"left outer join fetch r.categories " +
"left outer join fetch r.keywords " +
"left outer join fetch r.parentReferences",
countQuery = "select count(distinct r.id) from Reference r " +
"inner join r.persons " +
"left outer join r.categories " +
"left outer join r.keywords " +
"left outer join r.parentReferences")
Page<Reference> findsAllRelevantEntries(Pageable pageable);
当我对该查询运行测试时,我收到了此休眠警告:HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
@Test
void testFindAllRelevantAsync() throws ExecutionException, InterruptedException {
CompletableFuture<Page<Reference>> all = referenceService.findAllRelevantAsync(PageRequest.of(1, 20));
CompletableFuture.allOf(all).join();
assertThat(all.get()).isNotNull();
assertThat(all.get()).isNotEmpty();
}
存储库代码封装在此处未显示的服务方法中。它(服务方法)只是将调用从服务封送到存储库并返回。
此外,生成的 sql 查询不会生成limit
子句。虽然它确实触发了两个查询。
一个用于count
,另一个用于获取所有记录.
因此,它获取所有记录并在内存中应用分页.
这会导致查询执行非常慢。
如何使分页使用此查询?
编辑
我知道这里经常建议将其作为解决方案: 如何避免警告"收集提取指定的第一个结果/最大结果;在内存中应用!"使用休眠时?
有没有办法使用 Spring Data JPA 实现分页? 我不想硬连线EntityManager
,我也不想 从BasicTransformerAdapter
扩展代码
您可以使用基于双查询方法的通用/可重用方法。
一个 SQL 查询用于检索实体的IDs
,另一个查询具有IN
谓词,包括第二个查询中的IDs
。
实现自定义 Spring Data JPA 执行器:
@NoRepositoryBean
public interface AsimioJpaSpecificationExecutor<E, ID extends Serializable> extends JpaSpecificationExecutor<E> {
Page<ID> findEntityIds(Pageable pageable);
}
public class AsimioSimpleJpaRepository<E, ID extends Serializable> extends SimpleJpaRepository<E, ID>
implements AsimioJpaSpecificationExecutor<E, ID> {
private final EntityManager entityManager;
private final JpaEntityInformation<E, ID> entityInformation;
public AsimioSimpleJpaRepository(JpaEntityInformation<E, ID> entityInformation, EntityManager entityManager) {
super(entityInformation, entityManager);
this.entityManager = entityManager;
this.entityInformation = entityInformation;
}
@Override
public Page<ID> findEntityIds(Pageable pageable) {
CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
CriteriaQuery<ID> criteriaQuery = criteriaBuilder.createQuery(this.entityInformation.getIdType());
Root<E> root = criteriaQuery.from(this.getDomainClass());
// Get the entities ID only
criteriaQuery.select((Path<ID>) root.get(this.entityInformation.getIdAttribute()));
// Update Sorting
Sort sort = pageable.isPaged() ? pageable.getSort() : Sort.unsorted();
if (sort.isSorted()) {
criteriaQuery.orderBy(toOrders(sort, root, criteriaBuilder));
}
TypedQuery<ID> typedQuery = this.entityManager.createQuery(criteriaQuery);
// Update Pagination attributes
if (pageable.isPaged()) {
typedQuery.setFirstResult((int) pageable.getOffset());
typedQuery.setMaxResults(pageable.getPageSize());
}
return PageableExecutionUtils.getPage(typedQuery.getResultList(), pageable,
() -> executeCountQuery(this.getCountQuery(null, this.getDomainClass())));
}
protected static long executeCountQuery(TypedQuery<Long> query) {
Assert.notNull(query, "TypedQuery must not be null!");
List<Long> totals = query.getResultList();
long total = 0L;
for (Long element : totals) {
total += element == null ? 0 : element;
}
return total;
}
}
您可以在 https://tech.asimio.net/2021/05/19/Fixing-Hibernate-HHH000104-firstResult-maxResults-warning-using-Spring-Data-JPA.html 阅读更多信息
我自己找到了解决方法。基于此:
我怎样才能避免警告"收集提取指定的第一个结果/最大结果;在内存中应用!"使用休眠时?
首先:通过分页获取 ID:
@Query(value = "select distinct r.id from Reference r " +
"inner join r.persons " +
"left outer join r.categories " +
"left outer join r.keywords " +
"left outer join r.parentReferences " +
"order by r.id",
countQuery = "select count(distinct r.id) from Reference r " +
"inner join r.persons " +
"left outer join r.categories " +
"left outer join r.keywords " +
"left outer join r.parentReferences " +
"order by r.id")
Page<UUID> findsAllRelevantEntriesIds(Pageable pageable);
第二:使用 Id 执行in
查询
@Query(value = "select distinct r from Reference r " +
"inner join fetch r.persons " +
"left outer join fetch r.categories " +
"left outer join fetch r.keywords " +
"left outer join fetch r.parentReferences " +
"where r.id in ?1 " +
"order by r.id",
countQuery = "select count(distinct r.id) from Reference r " +
"inner join r.persons " +
"left outer join r.categories " +
"left outer join r.keywords " +
"left outer join r.parentReferences ")
@QueryHints(value = {@QueryHint(name = "hibernate.query.passDistinctThrough", value = "false")},
forCounting = false)
List<Reference> findsAllRelevantEntriesByIds(UUID[] ids);
注意:我得到一个List<Reference
而不是Pageable
所以你必须自己构建你的Pageable
,如下所示:
private Page<Reference> processResults(Pageable pageable, Page<UUID> result) {
List<Reference> references = referenceRepository.findsAllRelevantEntriesByIds(result.toList().toArray(new UUID[0]));
return new PageImpl<>(references, pageable, references.size());
}
这看起来不太好,并且执行两个语句,但它使用limit
进行查询,因此只获取所需的记录。
首先获取 id,然后执行主查询的方法有效,但效率不高。我认为这是Blaze-Persistence的完美用例。
Blaze-Persistence是JPA之上的查询构建器,它支持JPA模型之上的许多高级DBMS功能。它附带的分页支持可以处理您可能遇到的所有问题。
它还具有Spring Data集成,因此您可以像现在一样使用相同的代码,只需添加依赖项并进行设置:https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-setup
Blaze-Persistence有许多不同的分页策略,您可以配置这些策略。默认策略是将 id 查询内联到主查询中。像这样:
select r
from Reference r
inner join r.persons
left join fetch r.categories
left join fetch r.keywords
left join fetch r.parentReferences
where r.id IN (
select r2.id
from Reference r2
inner join r2.persons
order by ...
limit ...
)
order by ...