1

I have an GET endpoint which fetches the data based on the below parameters. firstName is mandatory and rest all are optional. 

{

"firstName:"",
"lastName:"",
"Gender:"",
"city"""

}

How should i approach for the database query? i am using Sring Data JPA, I have tried

findByFirstNameOrLastNameOrGenderOrCity(firstName,lastName,Gender,City)

Not sure if a native sql query using @Query annotation will work as any of the values apart from firstName can be null if the consumer of the endpoint is not sending the values. Kindly help

James Z
  • 12,209
  • 10
  • 24
  • 44
Vinay Vullakula
  • 63
  • 1
  • 4
  • 13

1 Answers1

0

If you need something like this:

GET /myEntities?firstName=bla-bla&lastName=bla-bla&gender=1&city=bla-bla

where firstName is mandatory and rest all are optional,

then you can use like this query:

@Query("select e from MyEntity e where e.firstName = ?1 " +
    "and (?2 is null or e.lastName = ?2) " + 
    "and (?3 is null or e.gender = ?3) " +
    "and (?4 is null or e.city = ?4)")
List<MyEntities> getByFilter(String firstName, String lastName, Boolean gender, String city);
Cepr0
  • 28,144
  • 8
  • 75
  • 101
  • @Cerp0 i have tried this query , but its not returning the results as expected , in my case i will be having only one record when all the parameters are passed including optional parameters , but somehow it is taking null values ---this is what its printing on the console bind => [male, male, null, null, null, null, firstNmae, firstNmae, lastName, lastName] – Vinay Vullakula Mar 09 '18 at 22:15
  • @VinayVullakula Sorry, I didn't understand what you mean ) Could you provide a detailed example (in your question) what do you need to achieve? – Cepr0 Mar 14 '18 at 08:39