Spring Data JPA: How not to repeat myself in countQueries?

Question

I am using Spring Data JPA repositories (1.7.2) and I am typically facing the following scenario:

• entities have lazy-loaded collections
• those collections are sometimes eagerly fetched (via JPAQL fetch join)
• repositories often return Page<Foo> instead of List<Foo>

I need to provide countQuery to every @Query that uses fetch joins on a repository that returns a Page. This issue has been discussed in this StackOverflow question

My typical repository method looks like this:

@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);

Obviously, it's not very DRY. You can tell that I am repeating all of the conditions in both value and countQuery parameters. How do I stay DRY here?

Answer 1

You could do something like this

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);