Spring Data JPA: How to form a conditional query?

Question

Consider a table with 4 fields: id, name, age, date. Now I want to look up the Database using these fields. So that's simple, I can do:

@Query("select d from data d where d.name=:name and d.age=:age and d.date=:date")

But the problem is, name, age and date are optional fields, i.e age, name, the date can be null. If age is null, then I should look up the Database using name and date and if age and name are NULL, then I should look up the Database using the date alone and so on...

Is it possible to achieve this in a simple way rather than forming different queries for each scenario? Because the real scenario that I am working on has 6-7 optional fields and forming different queries for each one looks damn weird.

Answer 1

For optional field, use this:

@Query("select d from data d 
         where (:name is null or d.name=:name) 
           and (:age is null or d.age=:age)
           and (:date is null or d.date=:date)")