How to exclude the where condition from the native query when the parameter is null

Question

I am using spring boot v2.6.1 and Postgres DB for my application. I have the below method in one of my repositories.

@Query(value = "SELECT * from subscriptions s " +
            "WHERE (:merchantID is NULL OR s.created_by = cast(:merchantID AS text)) " +
            "AND (:status is NULL or s.status IN (:status))", nativeQuery = true)
    List<Subscription> findWithStatus (@Param("merchantID")String merchantID, @Param("status") List<String> status);

When the parameter 'status' is not null, it works fine. But when it is null it throws an exception

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea

From this, I understand that the condition is not excluded from the query even though the specified parameter is null. I thought the problem is checking whether the parameter is null that is in :status is null, so I just changed the list parameter to String and passed a null value, still the same exception is thrown. So the problem is not with :status is null but with the IN operator in the query.

How to exclude this condition with IN operator when the parameter is null?

Answer 1

If you want to use this Native Query approximation, one solution is adding an additional Boolean parameter that indicates if the Status List is null.

@Query(value = "SELECT * from subscriptions s " +
            "WHERE (:merchantID is NULL OR s.created_by = cast(:merchantID AS text)) " +
            "AND (:statusIsNull or s.status IN (:status))", nativeQuery = true)
    List<Subscription> findWithStatus (@Param("merchantID")String merchantID, @Param("statusIsNull") Boolean statusIsNull, @Param("status") List<String> status);

So function call would be something like this:

Boolean statusIsNull = (statusList == null || statusList.isEmpty()) ? true : false;

List<Subscription> subscriptionList = findWithStatus (merchantID, statusIsNull, statusList);

But I really recommend to use JPA Criteria API to build dynamic queries.

Regards.