Has anybody tried to use a Spring Data repository for a paginated query with a fetch join? I think I found a bug in Spring Data. Suppose you have a repository like this:
I need to query the db for all Users with specific filters like fullname, status etc, and to do pagination. So I use both the Specification API and the Pageable class.
My User entity has an optional one-to-one relationship with the Merchant entity, so by doing this query Hibernate will do n+1 queries after the main one, to fetch all Merchants associated to the Users.
In order to avoid the n+1 problem, the Specification object I created does a fetch join between User and Merchant. The problem is that Spring Data will call the toPredicate(...) function twice:
1- once for the count query
2- once for the main query with limit and offset
When doing the count query, I think the Spring Data designers didn't think about a serious bug: since there is just SELECT count(*) in the select statement, there is no User or Merchant entity in there. So what happens is that I get an exception telling me that there is no parent for the "merchant" attribute I am fetching.
Did that ever occur to anybody here? I did find a blogger who dealt with a similiar problem:
http://codingexplained.com/coding/java/spring-framework/fetch-query-not-working-spring-data-jpa-pageable
He was able to resolve it because he doesn't use any filtering criteria, so he manually wrote the count query and the main query.
That's not my case: I need to build the query based on filters from my HTTP parameters (a search form in my web app).
I thought that maybe in my code I could try to find out wether the query being built is a count query, but I wasn't able to do that with the Specification API. Any ideas? Unless I'm doing something terribly wrong, I was thinking to notify the bug to the Spring team. In the meantime I found a workaround but it's quite terrible. The alternative would be using JPA directly with the entity manager and doing pagination manually, rather than using all the cool pre-built stuff by Spring Data, but it would be a pitty.