Ignoring condition if the parameter is null in Spring Data JPA

Question

I am currently developing the search and filtering feature of my project. I am trying to retrieve data with this Request body:

{
    "researchers": [
        "Jhon Doe"
    ],
    "unit": null,
    "agency":null,
    "date": null,
    "status": null,
    "budget": null
}

and below is the method from the ReasearchRepository that retrieves this data:


    @Query("select r from Research r " +
            "left join r.fundingAgencies fundingAgencies " +
            "left join r.researchers researchers " +
            "where (:budgetStart is null or :budgetEnd is null or  r.budget between :budgetStart and :budgetEnd )" +
            "and (:startDate is null or r.startDate >= :startDate) " +
            "and (:endDate is null or r.endDate <= :endDate) " +
            "and (:agencyNames is null or fundingAgencies.agencyName in :agencyNames) " +
            "and (:unitNames is null or r.deliveryUnit.unitName in :unitNames )" +
            "and (:names is null or researchers.name in :names ) " +
            "and (:researchStatuses is null or r.researchStatus in :researchStatuses)")
    List<Research> findAdvanced( @Param("budgetStart") Double budgetStart,
                                 @Param("budgetEnd")  Double budgetEnd,
                                 @Param("startDate")  LocalDate startDate,
                                 @Param("endDate") LocalDate endDate,
                                 @Param("agencyNames") Collection<String> agencyNames,
                                 @Param("unitNames") Collection<String> unitNames,
                                 @Param("names") Collection<String> names,
                                 @Param("researchStatuses") Collection<ResearchStatus> researchStatuses);

My problem is when I'm trying to find data from given values in the request, it returns an empty list [] even though it exists in the database. What I want to achieve is that if the parameter is null, it will ignore a specific condition in the query and will still return data.

I just followed the article from Baeldung.

I also find another article about using Criteria API, but it will take a lot of time for me to understand that. My project is a bit rush.

TIA

you can go with JDBCTemplate, build the query based on non-null values — sidgate
– sidgate, Commented Oct 17, 2021 at 2:12
Is it okay if I integrate it with my project that has Spring Data Jpa? — ruuuyin
– ruuuyin, Commented Oct 17, 2021 at 2:15
This ":paramName is null" code looks correct, you did well following www.baeldung.com article. I think your problem is not in these null conditions. — Igor Kanshyn
– Igor Kanshyn, Commented Oct 17, 2021 at 2:35
It is working now. I just found out that there is something wrong with my Service class. There is a procedure in my codes that modifies the data. Anyways, thank you very much for your time — ruuuyin
– ruuuyin, Commented Oct 17, 2021 at 2:55

4EACH · Accepted Answer · 2021-10-17 02:15:46Z

2

I seems like a good case to use Specifications.

https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

answered Oct 17, 2021 at 2:15

4EACH

2,1974 gold badges20 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Ignoring condition if the parameter is null in Spring Data JPA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related