CODEWITHSUNDEEP
spring-bootspring-data-jpa
sanjeev chauhan
sanjeev chauhan

Reputation: 11

Spring Data JPA @Query with Specification

I have a requirement to create a REST api. Api allows user to provide dynamic search criteria in URL. For example, let say I have NOTES table with column as Note_ID, NOTE_TEXT, STATUS, PERSON_ID. This table is used to keep notes of every person.

Now I want my REST api to be as https://server:host/MyApi/Notes?search=NoteText=='My Java adventure'. API should provide all notes having NOTE_TEXT as 'My Java adventure'. Similarly user can provide status also in url and also he can use operators as LIKE. I was able to do it via rsql parser as mentioned in https://www.baeldung.com/rest-api-search-language-rsql-fiql

Now I have additional requirement that based on user security person_id filter should be applied on query automatically.

I found that we can't have findBy method which can take Specification, Pageable and extra personId. For example I can't have a repository function as findByPersonId(Specification spec, Pageable page, Long personId);

I thought of using SpEL to use it, but then I found that if we use @Query annotation on findBy method, Specifications are ignored.

Seems like there is no way I can have Specification and @Query both. I need to add more clauses using specification only. In reality my where clause is very complex which I have to append and getting it with Specification seems to be difficult. Its something like Select * from NOTES where exists (select 'x' from ABC a where n.person_id = a.person_id)

Is there a way I can write @Query and also have Specification working on top of it?

Ideally I have achieve a query like

select * from test.SCH_FORUM_THREAD t
where exists (select 'x' from test.FORUM_THREAD_ACCESS fta, school.SCH_GROUP_PERSON gp
                where gp.GROUP_ID = fta.GROUP_ID
                and t.THREAD_ID = fta.THREAD_ID
                and gp.PERSON_ID = :personId)
    or exists (select 'x' from test.FORUM_THREAD_ACCESS fta
                where fta.THREAD_ID = t.THREAD_ID
                and fta.PERSON_ID = :personId);

So there are two exists clauses with or condition. I was able to make second exists by following How to write query(include subquery and exists) using JPA Criteria Builder

Now struggling with first exists as it has join also. Any idea how to do that with Specification. Also as there are two exists, does that mean I need two specifications. Can I achieve it in one specification.

Upvotes: 0

Views: 3068

Answers (1)

sanjeev chauhan
sanjeev chauhan

Reputation: 11

I was able to resolve it by creating a complex specification code. Something like 

@Override
public Predicate toPredicate(Root<ForumThread> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            Subquery<ForumThread> subQuery = query.subquery(ForumThread.class);
            Root<ForumThread> subRoot = subQuery.from(ForumThread.class);
            Join<ForumThreadAccess, GroupPerson> fragpjoin = subRoot.join("groupPersons");
            Predicate threadPredicate = builder.equal(root.get("threadId"), subRoot.get("threadId"));
            Predicate personPredicate = builder.equal(fragpjoin.get("personId"), personId); 
            subQuery.select(subRoot).where(threadPredicate, personPredicate);


            Predicate existsGroupPredicate = builder.exists(subQuery);


            Subquery<ForumThreadAccess> subQuery1 = query.subquery(ForumThreadAccess.class);
            Root<ForumThreadAccess> subRoot1 = subQuery1.from(ForumThreadAccess.class);
            Predicate threadPredicate1 = builder.equal(root.get("threadId"), subRoot1.get("threadId"));     
            Predicate personPredicate1 = builder.equal(subRoot1.get("personId"), personId); 
            subQuery1.select(subRoot1).where(threadPredicate1, personPredicate1);


            Predicate existsPersonPredicate = builder.exists(subQuery1);

            return builder.or(existsGroupPredicate,existsPersonPredicate);


        }

To make it work your entities should also have proper @OneToMany and @ManyToMany in place.

Thanks

Upvotes: 1

Related Questions