7

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?

Community
  • 1
  • 1
Xorty
  • 18,367
  • 27
  • 104
  • 155

1 Answers1

4

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);
Predrag Maric
  • 23,938
  • 5
  • 52
  • 68
  • Yea that's certainly more DRY. Now I have to do a tough choice whether I sacrifice some DRY principles or readability (string concatenation + inability of IDE to control syntax of JPAQL statement properly) – Xorty Feb 18 '15 at 14:37
  • Actually, the [documentation](http://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html#countQuery--) says this: `Defines a special count query that shall be used for pagination queries to lookup the total number of elements for a page. If non is configured we will derive the count query from the method name.`. So, if the conditions are the same as in the basic query, probably your best option is to let spring-data derive the count query itself. You should check the generated SQL, though, just to be sure. – Predrag Maric Feb 18 '15 at 15:08
  • 1
    Unfortunately that doesn't seem to work with fetch joins, check out the linked SO question link in my original post. – Xorty Feb 18 '15 at 15:16