JPA Specifications with Group By Aggregation

In this article I demonstrate how to enable JPA to produce SQL queries dynamically and execute them, for example:

select book_type, count(book.id) 
from book
where book.publish_date<=PARSEDATETIME('01-01-1900','dd-MM-yyy')
group by book.book_type

It is easy to use named queries and predefined method from JPA to query a database. However, this may not be enough. Some situations require generation of SQL queries during runtime, e.g. filtering or aggregation based on user-specified fields.

We start with a basic entity:

@Entity
@Table(name = "Book")
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String author;

    @Column
    private String title;

    @Column
    private Date publishDate;
    
    @Enumerated(EnumType.STRING)
    private BookType bookType;
}

The following repository definition allows us to access and to query the db:

@Repository
public interface BookRepo extends JpaRepository<Book, Long>, JpaSpecificationExecutor<Book>, GroupByRepository{
}

Extension of JpaSpecificationExecutor interface adds a support of JPA Specifications. This allows us to dynamically generate where-statements in the SQL query.

The interface GroupByRepository adds the aggregation functionality. Here is the definition:

public interface GroupByRepository {
    Map<Object, Long> whereGroupBy(SingularAttribute singularAttribute, Specification where);
}

The only method of the interface takes an attribute of the entity as a group-by argument, and the mentioned JPA specification. Here is an implementation of the interface, inspired by a post from stackoverflow:

public class GroupByRepositoryImpl implements GroupByRepository {
    @Autowired
    private EntityManager entityManager;
    @Override
    public Map<Object, Long> whereGroupBy(SingularAttribute singularAttribute, Specification where) {
        final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        final CriteriaQuery<Tuple> query = criteriaBuilder.createQuery(Tuple.class);
        final Root<Book> root = query.from(Book.class);
        final Path<String> expression = root.get(singularAttribute);
        query.multiselect(expression, criteriaBuilder.count(root));
        query.select(criteriaBuilder.tuple(expression, criteriaBuilder.count(root)));
        query.where(where.toPredicate(root, query, criteriaBuilder));
        query.groupBy(expression);
        final List<Tuple> resultList = entityManager.createQuery(query).getResultList();
        return resultList.stream()
                .collect(toMap(
                        t -> t.get(0, singularAttribute.getJavaType()),
                        t -> t.get(1, Long.class))
                );
    }
}

The following JUnit test contains an example of how to use the method:

@RunWith(SpringRunner.class)
@SpringBootTest
public class GroupByRepositoryTest {
    @Autowired
    BookRepo bookRepo;

    @Test
    public void groupByTest() {
        final Date Century20th = new Date(-2199999999999l);
        System.out.println(Century20th.toString());
        Specification<Object> where = Specification.where(
                (root, query, cb) -> cb.lessThanOrEqualTo(root.<Date>get("publishDate"), Century20th)
        );
        Map<Object, Long> result = bookRepo.whereGroupBy(Book_.bookType, where);
        System.out.println(result);
        Assert.assertEquals(2, result.entrySet().size());
    }
}

I showed you how you can use JPA Specification API to generate type-safe SQL queries dynamically instead of using query strings.

The sources are available here. Hopefully, you find this post useful.