Spring JPA - create specification predicate with having sum query

In this article we would like to show how in Spring JPA create Specification that provides Predicate with configured CriteriaQuery to work with HAVING SUM(...) condition.

Quick solution: use builder.equal() and builder.<Integer>selectCase() functions.

query.having(  // <---------------------------------------- HAVING ...
    builder.equal(
        builder.sum(  // <--------------------------------- SUM(...)
            builder.<Integer>selectCase()
                .when(/*   CONDITION HERE   */, 1)  // <--- condition inside SUM() function
                .otherwise(0)
        ),
        0
    )
)

SQL vs CriteriaQuery

This section presents comparision between SQL and JPA Hibernate query.

Note: below example is only theoretical - we try to find one offer entity for each group in which exists minimum one offer that was published.

SQL query: 

SELECT o.*
FROM offers o
GROUP BY o.parent_id
HAVING SUM(o.publication_time IS NOT NULL);

CriteriaQuery source code equivalent:

// EntityManager manager = ...;

CriteriaBuilder builder = manager.getCriteriaBuilder();
CriteriaQuery<OfferEntity> query = builder.createQuery(OfferEntity.class);
Root<OfferEntity> root = query.from(OfferEntity.class);

query.select(root);
query.groupBy(root.get("parentId"));
query.having(
    builder.equal(
        builder.sum(
            builder.<Integer>selectCase()
                .when(root.get("publicationTime").isNotNull(), 1)
                .otherwise(0)
        ),
        0
    )
);

List<OfferEntity> results = query.getResultList();

OfferEntity.java file: 

@Table(name = "offers")
public class OfferEntity
{
    @Id
    @GeneratedValue
    private Long id;

    // other fields ...    

    private Long parentId;
    private Date publicationTime;

    // getters and setters ...
}