Support search result classes different from Specification query root [DATAJPA-51]

[spring-projects-issues]

Stevo Slavić opened DATAJPA-51 and commented

Currently Specification API does not support search to return anything other than instance of query root (or some collection of root) JPA entity as result. But JPA itself supports result to be different class from query root and it typically isn't an entity bean class (see here and here).

Please add support for complex queries with search result classes different from Specification query root

---

Affects: 1.0 M2

42 votes, 25 watchers

[spring-projects-issues]

Kamil commented

For our project is important to use Specification together with DTO created in JPQL.

Is there any possibility it will be supported? I see that this issue have a lot of votes...

[yasslkb]

we need this

[ilkou]

in Hexagonal Architecture for example we have a domain class and a jpaEntity class;

• the domain class could be:

class User {
   private String id;
   private String fullname;
   private String phone;
   private String stuff;
   ...
}

and

class Wallet {
   private String id;
   private String balance;
   private User user; // we could have a constructor (lite modal) with only fullname / id
   ...
}

• the corresponding jpa entities could be:

class UserJpaEntity {
   private String id;
   private String fullname;
   private String phone;
   private String stuff;
   ...
}

and

class WalletJpaEntity {
   private String id;
   private String balance;
   private String userId;
   ...
}

now due to Hexagonal Architecture, we can't just use @ OneToOne or such annotations to link jpa entities and call it a day, I think..

Let's tackle the problem;

To fetch List/Page of Wallets for example, with their Users, one might fetch all wallets then loop through them and fetch the User, and that sounds like n+1 problem 🤔

or we can simply make a custom Wallet constructor and use it to map the result with @ Query, like the following:

@Query("""
        SELECT new io.spring.demo.Wallet(w.id, w.balance, u.id, u.fullname) FROM WalletJpaEntity w
        INNER JOIN UserJpaEntity u ON u.id = w.userId
        """)
Page<Wallet> findWallets(Pageable pageable);

and this works 🎉

But...

For filtering for example, we would need to use Specification API (that's why I'm commenting on this Issue), like this:

@Query("""
        SELECT new io.spring.demo.Wallet(w.id, w.balanace, u.id, u.fullname) FROM WalletJpaEntity w
        INNER JOIN UserJpaEntity u ON u.id = w.userId
        """)
Page<Wallet> findWallets(Specification<WalletJpaEntity> spec, Pageable pageable);

which doesn't work currently....

any work around this to assure we can have dynamic filters + efficient queries with joins (custom result mapping) at the same time ?

PS: A StackOverFlow question with same issue