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 — araknoid
– araknoid, Commented Oct 17, 2017 at 12:06

Predrag Maric · Accepted Answer · 2017-10-17 12:07:52Z

16

Try changing

" and ds.statusCode in :paymentStatuses"

into

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

answered Oct 17, 2017 at 12:07

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sajad Over a year ago

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)

Suraj Shingade Over a year ago

same for me.. if i use parameter name twice . it does not works

Sutra Over a year ago

Try this: " and (:#{#paymentStatuses == null} or ds.statusCode in :paymentStatuses)"

msd.salehi Over a year ago

This solution will fail if list has more than one item

Thakkar Darshan Over a year ago

Can you please explain what we can do for mongo DB for this specific case ?

|

Shivanshu Goyal · Accepted Answer · 2020-02-20 15:16:00Z

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, 2020 at 15:16

Shivanshu Goyal

53011 silver badges26 bronze badges

2 Comments

anandchaugule Over a year ago

Thanks I solved this oracle.jdbc.OracleDatabaseException: ORA-00920: invalid relational operator error

fo0 Over a year ago

for list input is this the correct solution, thanks

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Linked

Related