EN
Spring JPA - create specification predicate with having sum query
14 points
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.
xxxxxxxxxx1
query.having( // <---------------------------------------- HAVING ...2
builder.equal(3
builder.sum( // <--------------------------------- SUM(...)4
builder.<Integer>selectCase()5
.when(/* CONDITION HERE */, 1) // <--- condition inside SUM() function6
.otherwise(0)7
),8
09
)10
)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:
xxxxxxxxxx1
SELECT o.*2
FROM offers o3
GROUP BY o.parent_id4
HAVING SUM(o.publication_time IS NOT NULL);CriteriaQuery source code equivalent:
xxxxxxxxxx1
// EntityManager manager = ...;2
3
CriteriaBuilder builder = manager.getCriteriaBuilder();4
CriteriaQuery<OfferEntity> query = builder.createQuery(OfferEntity.class);5
Root<OfferEntity> root = query.from(OfferEntity.class);6
7
query.select(root);8
query.groupBy(root.get("parentId"));9
query.having(10
builder.equal(11
builder.sum(12
builder.<Integer>selectCase()13
.when(root.get("publicationTime").isNotNull(), 1)14
.otherwise(0)15
),16
017
)18
);19
20
List<OfferEntity> results = query.getResultList();OfferEntity.java file:
xxxxxxxxxx1
(name = "offers")2
public class OfferEntity3
{4
5
6
private Long id;7
8
// other fields ... 9
10
private Long parentId;11
private Date publicationTime;12
13
// getters and setters ...14
}15