java - Spring 数据 JPA : How not to repeat myself in countQueries?

Question

我正在使用 Spring Data JPA 存储库 (1.7.2)，我通常会遇到以下情况:

• 实体有延迟加载的集合
• 这些集合有时会被急切获取(通过 JPAQL fetch join)
• 仓库经常返回Page<Foo>而不是 List<Foo>

我需要提供countQuery每@Query在返回 Page 的存储库上使用 fetch join .此问题已在 this StackOverflow question 中讨论过

我的典型存储库方法如下所示:

@Query(value = "SELECT e FROM Employee e LEFT JOIN FETCH e.addresses a " +
    "WHERE e.company.id = :companyId " +
    "AND e.deleted = false " +
    "AND e.primaryAddress.deleted = false " +
    "ORDER BY e.id, a.id",
    countQuery="SELECT count(e) FROM Employee e WHERE e.companyId = :companyId AND e.deleted = false AND e.primaryAddress.deleted = false"
)
Page<Employee> findAllEmployeesWithAddressesForCompany(@Param("companyId") long companyId, Pageable pageable);

显然，它不是很干。你可以看出我在 value 中重复了所有条件和 countQuery参数。我如何在这里保持 DRY？

Answer 1

你可以这样做

public interface MyRepository extends JpaRepository {

    public static final String WHERE_PART = "e.companyId = :companyId AND e.deleted = false AND e.primaryAddress.deleted = false ";

    @Query(value = "SELECT e FROM Employee e LEFT JOIN FETCH e.addresses a " +
        "WHERE " + MyRepository.WHERE_PART
        "ORDER BY e.id, a.id",
        countQuery="SELECT count(e) FROM Employee e WHERE " + MyRepository.WHERE_PART
    )
    Page<Employee> findAllEmployeesWithAddressesForCompany(@Param("companyId") long companyId, Pageable pageable);