springjpaspring-datapredicatespecifications

Spring Data specifications with correlated subquery

How one using spring data specifications can do the equivalent of this:

select a.*, (select count(1) from b where b.a_id = a.id) as count from a;

If this is not possible is there an equivalent?

Solution

  • You can add an additional field count to your entity A, the value of which will be calculated by the correlated subquery:

    @Entity
class A {
    
  @Id
  private Long id;

  @Formula("(select count(1) from b where b.a_id = id)")
  private Long count;
}

    Then, having created a repository for entity A:

    public interface ARepository extends JpaRepository<A, Long> {
}

    You can get the required result using findAll() method.