Before I go down a long learning path, I’m looking for input on choosing between Spring JPA Specification and JPA CriteriaAPI instead of JPQL for a specific type of query.

I need to be able to filter based on many different fields not just name as in the below example. This seems like the purpose of both Specification and CriteriaAPI, i.e. dynamically building Predicate(s) to filter data. I’ve been unable to find references / examples that show the type of calculations below. I did read that Specification can only return an Entity, not a DTO/Projection so I think it is out.

Questions

1. Can the query below be written in Spring’s Specification?
2. Can the query below be written in JPA CriteriaAPI (Hibernate)?

Also any pointers to reference / examples would be much appreciated!

A generic version of the JPQL query is

    @Query(value = """
            select new ...dto.FooListDto(id, name, sum(songCnt), sum(codeCnt))
                from (select f.id as id, f.name as name,
                            count(case when s.position = 'S' then 1 end) as songCnt,
                            count(case when s.position = 'C' then 1 end) as codeCnt
                        from Foo f join f.bars b join b.snafus s
                        where f.name like :providedFooName
                        group by f.id, f.name)
                group by id, name
                order by id, name""")
Page<FooListDto> findByFooName(@Param("providedFooName") String providedFooName, Pageable pageable);