How to handle null values in an HQL query when checking a list for a contained integer value

Question

What I have is a Spring Boot repository in which I try to create a query function using HQL.

The function takes an Integer parameter which should be ignored by the query if it is null, else a list should be checked if it contains the value.

I have the where clause in two parts, the null check and the list check, looking like this:

@Query("SELECT u FROM MyEntity " +
" WHERE (:myParam is null or :myParam in (2, 3))"
)

Now the problem is that for the :myParam in (2, 3) part it is complaining "Inconsistent Datatypes: expected BINARY got NUMBER

(when :myParam is null, for :myParam != null it works)

I tried:

• casting either the param or the null value to in

• using coalesce(:myParam, CAST(NULL AS int)) which worked on a similar problem with :myParam being an integer list

• using a switch case statement

(case when :spracheKy is null then true when :spracheKy in (2, 3) then true else false end) = true

Thanks in advance for any help

Answer 1

why don't you just make use of two different repository methods, e.g. one that takes no parameter and another method that takes the parameter. and then decide and encapsulate the decision taking logic in a separate method of your service layer - I mean the logic which repository method to call based on parameter being null or not... could look like this:

@Service
@Transactional
public class YourService {

    // autowired by constructor injection
    private final YourEntityRepository repo;

    public List<YourEntity> getAllYourEntitiesByParam(Long param) {
        if (param == null) {
            return repo.findAll();
        }
        return repo.findAllByParam(param);
    }
}