Null condition in Jpa @Query

Question

I have a query in JPA, but all parameters should be optional. I tried many different ways, but none were successful because they threw some error.

If for example the given list of job Types is empty it should evaluate this condition as true. Currently no companies would be queried.

@Query("""
        select c from Company c
        where ((EXISTS (select 1 from CompanyJobType cjt JOIN JobType jt on cjt.jobType = jt where jt.name in :jobTypes and cjt.company = c)))
        AND ((EXISTS (select 1 from CompanyIndustry ci JOIN Industry i on ci.industry = i where i.name in :industries and ci.company = c)))
        AND (c.name like %:search% or c.shortDescription like %:search%)
        AND (c.isStartup = :isStartup OR :isStartup = false)
        AND c.talentpoolOpen = true
        """)
Page<Company> find(List<String> jobTypes, List<String> industries, String search, boolean isStartup, Pageable pageable);

If you have any other approach how to get this to work, I'm happy to adapt every part.

Answer 1

Usually when we know that the parameter or list of parameters can be optional aka null the @Query should know it:

Can you try next one query and tell me if it is going to fix the problem:

  @Query("""
        select c from Company c
        where ((:jobTypes) is null or (EXISTS (select 1 from CompanyJobType cjt JOIN JobType jt on cjt.jobType = jt where jt.name in :jobTypes and cjt.company = c)))
        AND ((:industries) is null or (EXISTS (select 1 from CompanyIndustry ci JOIN Industry i on ci.industry = i where i.name in :industries and ci.company = c)))
        AND ((:search) is null or (c.name like %:search% or c.shortDescription like %:search%))
        AND ((:isStartup) is null or (c.isStartup = :isStartup OR :isStartup = false))
        AND c.talentpoolOpen = true
        """)
  Page<Company> find(List<String> jobTypes, List<String> industries, String search, boolean isStartup, Pageable pageable);