Paged findAll(…) with Specification does not work with join fetch [DATAJPA-105]

[spring-projects-issues]

Andrew Geery opened DATAJPA-105 and commented

The method Page<T> findAll(Specification<T>, Pageable) in the JpaSpecificationExecutor interface throws an exception if the Specification uses a join fetch. The problem is that in order to do the pagination, the library has to first perform a count query, but when the count query is performed, the join fetches are not eliminated or changed to regular joins.

The error is:

org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

The attached zip file is a simple Maven project which demonstrates this problem (mvn test).

---

Affects: 1.0 GA, 1.0.1

Attachments:

• test.zip (6.81 kB)

14 votes, 11 watchers

[spring-projects-issues]

Andrew Geery commented

The way I've handled this is to use the CriteriaQuery.getResultType() method to check whether the query's projection is Long or the class the Specification is operating on. If the resultType is Long, the query is a count and no join fetches should be used. If the resultType is the class the Specification is operating on, it's a normal query and fetch joins can be used

[spring-projects-issues]

Oliver Drotbohm commented

So I assume you're calling getFetches() on the Root and remove all elements from the returned Set then, right? What is a bit unfortunate though is that we have to rely on the persistence provider returning a mutable Set. I thought about implementing an adapter for the Root interface that simply drops the calls to fetch*(…) methods but as clients might use the return value of the method call we cannot come up with a decent return value except creating a separate throw-away Root and thus increase complexity quite a lot. We might end up with that solution eventually but so far I'd stick to the simple approach of simply wiping out the fetches for the count query

[spring-projects-issues]

Andrew Geery commented

I think I tried that approach (wiping out the fetches Set) and it didn't work for Hibernate (I think the Set was not mutable). The approach I came up with is simpler :). From the CriterQuery object which is passed into the toPredicate() method, look at the resultType. If the resultType is a Long, the specification is being used as a count query and thus shouldn't have any fetches associated with it. If the resultType is not a Long, the specification is being used for a regular "data" query and fetches can be used. So it is up to each Specification definition to handle its own fetches and to not include the fetches (via an if statement) if it's being used for counting

[spring-projects-issues]

Thomas Darimont commented

Hello,

I just verified that the proposed solution works:

@SuppressWarnings("unused")
@Test
public void testPagedSpecificationProjection() {
     Specification<Person> spec = new Specification<Person>() {
          public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
               if (Long.class != query.getResultType()) {
                        root.fetch(Person_.addresses);
               }
               return cb.conjunction();
          }
     };
     Pageable pageable = new PageRequest(0, 1);
     Page<Person> page = personRepo.findAll(spec, pageable);
}

Best regards,
Thomas

[spring-projects-issues]

Jens Schauder commented

If I read the comments correct:

There exists a solution to the problem which can be implemented in the Predicate.

The solution Spring Data JPA could provide would result either in a leaky abstraction or recreating a significant part of the Criteria API which we don't want to maintain.

I therefore close this issue

[xerZV]

any updates on that?

[beherren2019]

Hello @schauder Is there any update on this?

[ilkou]

👀

[burakturker]

is there any update

[lehaiquantb]

2024 => any update ?

[sanderino666]

When using Kotlin you could use an extension function to get rid of the if statement in the specification.

For example:

fun <X : Any, Y : Any> Root<X>.joinOrFetch(
    cq: CriteriaQuery<*>,
    attributeName: String,
    jt: JoinType,
): FetchParent<X, Y> {
    // https://github.com/spring-projects/spring-data-jpa/issues/532
    return if (cq.resultType.name == "java.lang.Long") {
        this.join(attributeName, jt)
    } else {
        this.fetch(attributeName, jt)
    }
}

[anaconda875]

Hi @schauder, any update on it?

[rishiraj88]

@anaconda875 , this issue appears as "closed" already!