Languages
[Edit]
EN

Spring JPA - create specification predicate with having sum query

14 points
Created by:
Evie-Grace-Noble
411

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 ...
}
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

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