How to query list of objects with paramteres that might not be entered?

I’m trying to query list of objects filtered by parameters that might not get entered by the user at all.

@Query(value = "SELECT  * " +
        "FROM project  " +
        "WHERE CASE WHEN (:location is not null) THEN location_Id LIKE :location" +
        " and CASE WHEN (:category is not null) THEN category_Id LIKE :category", nativeQuery = true)
List<Project> getProjects(@Param("category") List<Category> category,
                          @Param("location") List<Location> location);

But i keep on getting similar errors that my syntax is wrong even tho i just copied it from a tutorial. Any idea where i might be mistaking?

Answer

If you want a single query:

WHERE (:location is null OR location_Id LIKE :location) AND
      (:category is null OR category_Id LIKE :category)

From a performance perspective, OR can kill performance (by preventing the use of available indexes). Often applications build the WHERE clause for the non-NULL parameter values.

Advertisement

Answer