Spring JPA Specification Not Exists and Self Join

Question

How to build the following SQL query in Spring JPA Specification?

SELECT col1, col2, MAX(col3) 
FROM table_name t1 
WHERE col4 IN (1,2,3) AND status IN ('STATUS_1','STATUS_2') AND 
NOT EXISTS 
(
 SELECT 1 FROM table_name t2 WHERE t1.id = t2.parent_id 
 AND t2.status IN ('STATUS_3','STATUS_4')
)
GROUP BY col1, col2;

Java code:

return new Specification<TableEntity>() {

    @Override
    public Predicate toPredicate(Root<TableEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        // how to build Predicates for the above query which has self-join and not exists.

    }
};

Answer 1

try this

return new Specification<TableEntity>() {

    @Override
    public Predicate toPredicate(Root<TableEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
         Subquery<TableEntity> subquery = query.subquery(TableEntity.class);
         Root<TableEntity> subqueryRoot = subquery.from(TableEntity.class);
         subquery.select(subqueryRoot);

         subquery.where(builder.and(builder.equal(root, subqueryRoot.get("parent")),
                                    subqueryRoot.get("status").in("STATUS_3","STATUS_4"))
                       );

         return builder.not(builder.exists(subquery));
    }
};