I want to create a search filter in Spring Boot controller, that takes 6 paramas and param value may be null then how to write a Data JPA Query?

Question

Like I have a page where data are showing like first name, middle name, last name, address, city, state, country, age, salary.

There are a filter implemented that have 4 fields like city, age, salary, state. now I have to made a controller in Spring boot that takes all 4 fields as input params and find data from database using Spring Data JPA.

But my problem is that I want to filter Data sometime by salary only, sometime by city, state, sometime with all 4 params. So what will be controller code and JPA Repository query to do this filter process.

Please Help me Thanks in Advance

You can have one SQL as a `native query` in Spring data JPA `@Query(value=SQL_Statement, native=true)` , SQL statement must fetch records based on OR conditions ! Hope that is helpful, you can crawl internet for JPA implementations. Although , not a good approach I'd suggest as it will involve validations, filterations etc, better to fetch what is ensured to be returned ! — Harsh, Sep 20 '22 at 12:33
Use the `JpaSpecificationExecutor` and use a `Specification` to build a `Criteria` query. That way you can factor in the dynamic bits. — M. Deinum, Sep 20 '22 at 12:50

score 1 · Answer 1 · answered Oct 12 '22 at 10:40

I would suggest to use JPA Specification instead, to know more on specification code here is my thread on stackoverflow

If you want to stick with JPA then here's the query which can help you

where (:field1 is null or table.field1 = :field1) 
and (:field2 is null or table.field2 = :field2)
...
...
...

Explanation: The above code will check if the parameter is null, then return true otherwise compare with the column.

score 0 · Answer 2 · answered Sep 21 '22 at 07:21

0

test if your parameters are null :

"where (:name is null or name = :name) and (:city is null or city = :city)"

answered Sep 21 '22 at 07:21

grigouille

511
3
14

score 0 · Answer 3 · answered Oct 12 '22 at 12:54

You can modify Query with below clause Where you can write query in Repository and in where clause you can add conditions. So this will work as expected.

@Query(value = "SELECT user from User user WHERE ((:name IS NULL) OR (user.name = :name)) AND ((:city IS NULL) OR (user.city = :city)))

score -1 · Answer 4 · edited Aug 01 '23 at 06:24

   @Query("select new com.rainbow.customer.rainbowcustomerservice.user.model.profile.AgentProfileDto("
            + " a.id as agentId,a.firstName ,a.middleName, a.lastName,a.mobileNumber,a.emailId,a.gender,a.userName,"
            + " a.status,p.id as profileId, p.profileType, p.isAadhaarVerified, p.isPanVerified,"
            + " p.isSelfieVerified, p.status as profileStatus, p.isDefault, p.pincode, p.remarks, p.district.name as districtName, "
            + "p.createdAt, p.sales.userName as salesUser, p.rmUser) "
            + "from Agent a inner join AgentProfileV2 p on a.id=p.agent.id inner join Zone z on p.district.stateId.zoneId=z.id where "
            + "( :#{#search.name} is null or lower(a.fullName) like %:#{#search.name}%)" +
            " and (:#{#search.userName} is null or lower(a.userName) like %:#{#search.userName}%)" +
            " and (:#{#search.mobileNumber} is null or lower(a.mobileNumber) like %:#{#search.mobileNumber}%)" +
            " and (:#{#search.gender} is null or lower(a.gender) like %:#{#search.gender}%)" +
            " and (:#{#search.emailId} is null or lower(a.emailId) like %:#{#search.emailId}%)" +
            " and (:#{#search.status} is null or lower(a.status) like %:#{#search.status}%)" +
            " and (:#{#search.profileType} is null or lower(p.profileType) like %:#{#search.profileType}%)" +
            " and (:#{#search.profileStatus} is null or lower(p.status) like %:#{#search.profileStatus}%)"+
            " and (:#{#search.zoneId} is null or p.district.stateId.zoneId = :#{#search.zoneId})"
    +" and (:#{#search.stateId} is null or p.district.stateId.id = :#{#search.stateId})"
    +" and (:#{#search.districtId} is null or p.district.id = :#{#search.districtId})"
    +" and (:#{#search.salesUser} is null or lower(p.sales.userName) like %:#{#search.salesUser}%)"
           + " and (:#{#search.rmUser} is null or lower(p.rmUser) like %:#{#search.rmUser}%)")
    Page<AgentProfileDto> findAgent(ProfileSearchDto search, Pageable sort);

I want to create a search filter in Spring Boot controller, that takes 6 paramas and param value may be null then how to write a Data JPA Query?

4 Answers4