Spring Data JPA-谓词-使用分页连接获取-响应很慢

Question

我将spring datat jpa谓词与Join Fetch一起使用，我的响应非常慢。我收到一条警告消息：

[警告]组织。冬眠hql。内部的ast。QueryTranslatorImpl:hh000104：使用集合获取指定的firstResult/maxResults；正在内存中应用！

Answer 1

您可以使用基于双查询方法的通用/可重用方法。

一个SQL查询用于检索实体的ID，另一个查询使用包含第二个查询中ID的IN谓词。

实现一个自定义的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;
  }
}

您的存储库类类似于：

@Repository
public interface FilmDao extends JpaRepository<Film, Integer>, AsimioJpaSpecificationExecutor<Film, Integer> {

  @EntityGraph(
    type = EntityGraphType.FETCH,
    attributePaths = {
      "language", 
      "filmActors", "filmActors.actor"
    }
  )
  List<Film> findAll(@Nullable Specification<Film> spec);

  @EntityGraph(
    type = EntityGraphType.FETCH,
      attributePaths = {
      "language", 
      "filmActors", "filmActors.actor"
    }
  )
  Page<Integer> findEntityIds(Pageable pageable);
}

您的@Service注释类可以执行以下操作：

public Page<Film> retrieveFilms(Pageable page) {
    // Getting film ids and page data to prevent:
    // HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! 
    // which affects application's performance
    Page<Integer> filmIdsPage = this.filmDao.findEntityIds(page);

    List<Film> result;
    List<Integer> filmIds = filmIdsPage.getContent();
    if (CollectionUtils.isEmpty(filmIds)) {
      result = Lists.newArrayList();
    } else {
      // Retrieve films using IN predicate
      Specification<Film> fimlIdInSpecification = FilmSpecifications.idIn(Sets.newHashSet(filmIds));
      result = this.filmDao.findAll(fimlIdInSpecification);
    }
    return PageableExecutionUtils.getPage(result, page, () -> filmIdsPage.getTotalElements());
  }

你可以在https://tech.asimio.net/2021/05/19/Fixing-Hibernate-HHH000104-firstResult-maxResults-warning-using-Spring-Data-JPA.html