How to skip @Param in @Query if is null or empty in Spring Data JPA

Question

@Query(value = "Select f from Documents f " +
        "RIGHT JOIN f.documentStatus ds " +
        "where f.billingAccount.accountId  in :billingAccountIdList " +
        " and ds.statusCode in :paymentStatuses" +
        " and f.paymentDate < :paymentDate")
List<FinancialDocumentEntity> getFinancialDocumentsOverdue(@Param("billingAccountIdList")List<String> billingAccountIdList,
                                                           @Param("paymentStatuses") List<String> paymentStatuses,
                                                           @Param("paymentDate") Date paymentDate);

I have query like above. It is possible to skip searching param for example @Param("paymentStatuses") in query method if is null or is empty ?

Possible duplicate of [Spring Data - ignore parameter if it has a null value](https://stackoverflow.com/questions/43780226/spring-data-ignore-parameter-if-it-has-a-null-value) — araknoid, Oct 17 '17 at 12:06

score 16 · Accepted Answer · answered Oct 17 '17 at 12:07

16

Try changing

" and ds.statusCode in :paymentStatuses"

into

" and (:paymentStatuses is null or ds.statusCode in :paymentStatuses)"

answered Oct 17 '17 at 12:07

Predrag Maric

23,938
5
52
68

2

How it should be if the parameter should be checked as a member? I tried this but not works: `Select a from MyEntity a where (:groups is null or :groups member of a.groups)` – Sajad Apr 04 '18 at 12:06
same for me.. if i use parameter name twice . it does not works – Suraj Shingade Jun 28 '18 at 16:21
Try this: ```" and (:#{#paymentStatuses == null} or ds.statusCode in :paymentStatuses)"``` – Sutra Sep 23 '19 at 07:52
This solution will fail if list has more than one item – msd.salehi Aug 18 '20 at 13:41
Can you please explain what we can do for mongo DB for this specific case ? – Thakkar Darshan Apr 26 '21 at 09:26
Getting `argument of OR must be type boolean, not type record` in postgres – dark_prince Sep 30 '22 at 15:34

score 6 · Answer 2 · answered Feb 20 '20 at 15:16

6

Try changing

" and ds.statusCode in :paymentStatuses"

into

" and (COALESCE(:paymentStatuses, null) is null or ds.statusCode in :paymentStatuses)"

This solution will work for the empty list, null list, and a list with items 1 or more.

answered Feb 20 '20 at 15:16

Shivanshu Goyal

500
9
25

Thanks I solved this oracle.jdbc.OracleDatabaseException: ORA-00920: invalid relational operator error – anandchaugule Jan 27 '21 at 09:18
for list input is this the correct solution, thanks – fo0 Apr 08 '21 at 16:28

How to skip @Param in @Query if is null or empty in Spring Data JPA

2 Answers2

Linked