Languages
[Edit]
EN

Spring JPA - create specification predicate with having sum query

14 points
Created by:
Evie-Grace-Noble
351

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 ...
}

Java Persistence API (JPA)

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join