Spring Data JPA - count number or results of query

Question

I want to get the number of results of a query in Spring Data Jpa, using a non-native @Query method. It consists of a basic group by plus a having clause.

My plain query looks like this (analogous example):

select count(*) from (
    select 1 from table t 
    where t.field_a = 1
    group by t.id
    having count(*) = 2) a;

Since Hibernate 5 does not allow subqueries in the form clause, I have to find a workaround for that. The only one I found is very inefficient as per the query plan:

select count(*) from table t
where t.field_a = 1 and
2 = (select count(*) from table temp where temp.id = t.id);

Is there a way to write a Spring Data JPA query that's as efficient as the first one? I can think of no solution rather than selecting the inner query and taking its size() in java, but that can produce issues due to a ton of redundant data passing through the network.

Answer 1

There is no easy solution to count the results of a subquery in JPA but the a workaround is proposed here https://arjan-tijms.omnifaces.org/2012/06/counting-rows-returned-from-jpa-query.html. The principle is to build a native query based on the initial Jpa subselect query.