Adding projection support for JPASpecificationExecutor

[ratulSharker]

Hi there,

As per current situation, JPASpecificationExecutor does not allow select any result other than the entity.

Can we allow projection by adding following methods in the JPASpecificationExecutor and their implementation inside SimpleJpaRepository ?

public <Q> List<Q> findAll(Specification<T> specification, Class<Q> clazz);

public <Q> List<Q> findAll(Specification<T> specification, Sort sort, Class<Q> clazz);

public <Q> Page<Q> findAll(Specification<T> specification, Pageable pageable,
		Class<Q> clazz);

These methods will allow getting different result type other than T where T is JpaSpecificationExecutor<T>.

[mp911de]

That's in place via #2274. We do not want to pollute the API surface with additional overloads that represent all possible combinations of parameter types and operations.

[ratulSharker]

@mp911de how can we use projection in 'JPASpecification' ?

[mp911de]

The code would look like along the lines of:

UserRepository repo;
Specification<User> spec;
List<UserProjection> result = repo.findBy(spec, q -> q.as(UserProjection.class).all());

[ratulSharker]

@mp911de I didn't get it. I can't find any findBy method inside the repository extending from JPARepository<T, ID> & JPASpecificationExecutor<T>. Do i have to introduce one method inside my repository interface ? Elaborate please.

[schauder]

That's part of the JpaSpecificationExecutor API and will be available with the next mile stone for the 3.0 release.

[ribertojunior]

The code would look like along the lines of:

UserRepository repo;
Specification<User> spec;
List<UserProjection> result = repo.findBy(spec, q -> q.as(UserProjection.class).all());

Hello, sorry to jump on this thread. I'm trying to implement this, but It seems that the generated SQL is the same of the regular findAll with specification, what makes the use of the projection meaningless. Is there something missing on this code? I should add that I 'm using pagination as well.

[mp911de]

We're attaching a jakarta.persistence.fetchgraph Query hint. We provide AttributeNodes to the EntityGraph and it is up to the JPA provider to consider the fetchgraph.

[ribertojunior]

The reason it was not working is that my projection was not correctly designed. So it was a error on my side. Thanks for the help.

[fernando-1996]

Hello @ribertojunior
What did you do to make it work? I have the same problem as you did. I've tried using project method also, but it always fetches all the records, triggering many unwanted relationship calls.

[SchlauFuchs]

Okay I have difficulties with the syntax in Kotlin. Here is my line, abstracted:

        val result: Page<ItemSearchProjection> = itemRepository.findBy(searchDefinition.specification) { queryFunction -> queryFunction.`as`(
            ItemSearchProjection::class.java).page(pageRequest) }

This fails for me with Not enough information to infer type variable S

There is not enough examples in the usual places like Baeldung or StackOverflow to code by example.

[edit]
I got it sorted myself, yay!

        val result: Page<ItemSearchProjection> =
            itemRepository.findBy(searchDefinition.specification) { queryFunction: FetchableFluentQuery<Item> ->
                queryFunction.`as`(
                    ItemSearchProjection::class.java
                ).page(pageRequest)
            }

But when looking at the Hibernate debug, the resulting DB query still contains all the fields instead of just the ones of the projection interface (and inner interface properties). So if any fetch hints have been given to hibernate, they are proudly ignored.

This is with SpringBoot+Data.JPA 3.1.3, Hibernate Core 6.2.7

[eduardosouzag]

Please ribertojunior show you solution.

[gexge]

We're attaching a jakarta.persistence.fetchgraph Query hint. We provide AttributeNodes to the EntityGraph and it is up to the JPA provider to consider the fetchgraph.

How the query hint can be attached to the method ?

Thanks.

[ribertojunior]

Please ribertojunior show you solution.

Sorry for taking so long to answer.
I used Specification.toPredicate(Root, CriteriaQuery, CriteriaBuilder) and did not construct the predicate myself. It worked well, but we shelved this spring boot migration for now, so this version is not well tested.

[ahmedmagdy512]

The code would look like along the lines of:

UserRepository repo;
Specification<User> spec;
List<UserProjection> result = repo.findBy(spec, q -> q.as(UserProjection.class).all());

it gives me error
java.lang.UnsupportedOperationException: Class-based DTOs are not yet supported.
when using the same as the example you've mentioned
can you tell me where the problem is ?
I'm using Spring 3, Postgresql driver