Writing Complex SQL Queries Using Spring Specificaitons

Question

I'm having a complex SQL query which I want to execute in my Spring boot application. Following is a part of it's JPQL equivalent:

SELECT j FROM Job j WHERE
(   (?6 = 0L) OR 
    0L IN   (SELECT i12.filterFieldId FROM IjpPublishFilterFields i12 WHERE  i12.jobId = j.id AND    i12.fieldType LIKE 'GRADE') OR
    (?6 IN  (SELECT node.id FROM Grade node 
            INNER JOIN Grade parent ON  ((node.lft BETWEEN parent.lft AND parent.rgt) AND 
                                          parent.id IN (SELECT i1.filterFieldId FROM   IjpPublishFilterFields i1 WHERE  **j.id** = i1.jobId AND i1.fieldType LIKE 'GRADE'))
            WHERE   node.organizationId = ?5 AND         
                    node.isActive = true AND 
                    parent.isActive = true )))

Since there are multiple other conditions which require to form 'Where' clause dynamically, I believe @Query annotation is out of the window.

After a lot of research, still I'm unable to convert this where clause to Specification. e.g., I can return whole IjpPublishFilterFields object but can't return just i12.filterFieldId.

How could I write the where clause using JPA Specification?

score 0 · Answer 1 · answered Sep 15 '21 at 12:48

I was able to solve this by using EnitityManager.createQuery() and formed this equivalent JPQL using simple String concatenation. Still I believe, we should be able to form this where caluse using Spring data JPA Specifications. So, further hints are welcomed.

score 0 · Answer 2 · answered Sep 20 '21 at 09:47

There is nothing special about your query, so you should be able to translate this to something like criteriaBuilder.or( criteriaBuilder.eq(param, 0L), criteriaBuilder.literal(0L).in(subquery1), param.in(subquery2) ) where subquery1 and subquery2 are constructed roughly like this: Subquery<Long> subquery1 = criteriaQuery.subquery(Long.class); Root<IjpPublishFilterFields> r = subquery1.from(IjpPublishFilterFields.class); subquery1.select(r.get("filterFieldId")); ...

Writing Complex SQL Queries Using Spring Specificaitons

2 Answers2