[DATAJPA-105] Paged findAll(…) with Specification does not work with join fetch Created: 12/Sep/11  Updated: 17/Oct/19  Resolved: 17/Oct/19

Status: Closed
Project: Spring Data JPA
Component/s: Core
Affects Version/s: 1.0 GA, 1.0.1
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Andrew Geery Assignee: Jens Schauder
Resolution: Won't Fix Votes: 14
Labels: None
Remaining Estimate: 0d
Time Spent: Not Specified
Original Estimate: 0d

Attachments: Zip Archive test.zip    
Last updater: Jens Schauder

 Description   

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



 Comments   
Comment by Andrew Geery [ 15/Oct/11 ]

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.

Comment by Oliver Drotbohm [ 16/Nov/11 ]

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.

Comment by Andrew Geery [ 16/Nov/11 ]

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.

Comment by Thomas Darimont [ 30/Jul/13 ]

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

Comment by Jens Schauder [ 17/Oct/19 ]

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.

Generated at Wed Oct 23 00:56:14 UTC 2019 using Jira 7.13.8#713008-sha1:1606a5c1e7006e1ab135aac81f7a9566b2dbc3a6.