CODEWITHSUNDEEP
springspring-bootspring-dataspring-data-jpa
Imran
Imran

Reputation: 1902

Spring Data JPA and NULL filter, if Double field value is null nothing is returned

I have a data JPA entity where it contains a "price" type Double. Now, the users need to able to filter the records based on that field (Between min and max). Now the problem is, the value in the DB can be null for some records. My data JPA repository uses a native query like "price BETWEEN :priceFrom AND :priceTo". Now, if the user does not specify anything in the filter conditions, all record including the ones where prices is null should be returned. However, this query does not return those record. I know, I can create a new method with query "price IS NULL" and check the filter values in my service layer and call the null version if nothing is specified. But, I have multiple fields with the same requirement then it results in a lot of duplicate methods to maintain. Is there a better approach to handle that situation?

Upvotes: 0

Views: 3099

Answers (2)

Betlista
Betlista

Reputation: 10549

It seems to me, that you can specify

(:priceFrom is null and :priceTo is null and price is null)
OR price between :priceFrom and :priceTo

if priceFrom and priceTo are entered, second part of OR will be used, otherwise it selects records where price is null

Upvotes: 2

Rahul Kargwal
Rahul Kargwal

Reputation: 527

Since you are using Spring Data JPA - Specifications should solve this for you. This is a JPA Criteria based solution. For any complex-enough API – searching/filtering your resources by very simple fields is simply not enough. A query language is more flexible and allows you to filter down to exactly the resources you need. Hence you should easily be able to program for NULL (in the scenario that you currently need) and anything else that you might need.

This is scalable for multiple fields and easy to code/configure. There are a few links which will give you more insight into it

Hopefully, this is helpful.

Upvotes: 1

Related Questions