How to use dynamic constructed SQL, with complex aggregation, with Hibernate 6 and JPA

Question

In Spring Boot 3.4.2 + Java 21, it's easy to use repositories to create methods which make database calls for you.

public interface PlayerRepository extends CrudRepository<Player, Long> {
    PlayerfindById(long id);
}

You can even specify your own static SQL:

public interface PlayerRepository extends CrudRepository<Player, Long> {

    @Query(value = "SELECT * FROM PLAYER WHERE ID = ?1", nativeQuery = true)
    PlayerfindById(long id);
}

However, if you need to do complex sorting and filtering, with joins on several tables which returns results table from parts of several tables (including summing up values, and group by), you need to construct your own SQL with or without the various where clauses and order by clauses.

The kind of SQL we want to call is

select 
   p.id, p.name, a.balance, count('x') as num_transactions, 
   sum(t.credit) as total_credit
   round(sum(t.credit::number(32,2) / a.balance) * 100, 2) as percentage, 
   .. lots more complex maths and type casting....
from 
   player p, account a, transactions t
where 
   a.playerId = p.id and t.accountId = a.id
   and p.age > xx -- may or may not be required
   and t.transactionType in (1,2,3) -- may or may not be required
   and p.created_at > xxx -- may or may not be required.
   -- lots more possible filter criteria
   group by p.id, p.name, a.balance
   order by  xxx -- dynamic

Is something like this still possible in Spring Boot with JPA?

We looked at specifications and criteria but it's unclear if this will work as the examples only selected rows from a single table without the aggregation and summing we need, i.e. they allowed adding of a where clause, but not with a custom select clause.

If JPA/Spring Boot doesn't support dynamically constructed custom SQL, one strategy might be to create a repository method for every variation, but the combinations of where and sort are very large. E.g.

@Query(value = "select" 
   + "p.id, p.name, a.balance, count('x') as num_transactions,"
   + "sum(t.credit) as total_credit,"
   +  "round(sum(t.credit::number(32,2) / a.balance) * 100, 2) as percentage" +  ... lots more stuff....
   + "from" 
   + "player p, account a, transactions t"
   + "where" 
   + "a.playerId = p.id and t.accountId = a.id"
   + "and p.age > ?"
   + "and t.transactionType in (?)"
   + "group by p.id, p.name, a.balance"
   + "order by  num_transactions"
List<ResultsDTO>getStuffByAgeAndTransactionTypesAndSortByNumTransactions(int age, List<int> transactionTypes)

But this would result in hundreds of functions.

On top of this we have to figure out how to handle pagination.

We also looked at querydls: http://querydsl.com/ but this suffers from the same issue - we can't specify a custom select in order to do things like round(sum(t.credit::number(32,2) / a.balance) * 100, 2) as percentage.