How to handle all enum value in JPA Query method for empty parameter

Question

I have a JPA method that finds list of Students by their graduation status.

List<Student> findAllByStatus(Status status);

But if the request is made with null Status, I want to retrieve entities with null status.

How can I handle this with JPARepository using only one method query, to achieve no filtering by status if the status is null?

Thanks in advance.

Answer 1

You should use something like:

@Query("SELECT s from Student s WHERE (?1 is null or s.status = ?1)")
List<Student> findAllByStatus(Status status);

Just a little fix to Ankit Kanani answer.

Answer 2

Try

@Query("SELECT s from Student s WHERE (s.status is null or s.status =?1)")
List<Student> findAllByStatus(Status status);

Answer 3

This method involves a bit more code but i think it's your best bet :

@Override
public List<Interface> findAllWithFilters(String name, InterfaceType type, String ip) 
{
    Interface intfc = new Interface();
    intfc.setName(name);
    intfc.setType(type);
    intfc.setIp(ip);

    ExampleMatcher matcher = ExampleMatcher.matching()
        .withMatcher("name", match -> match.contains())
        .withMatcher("type", match -> match.exact())
        .withMatcher("ip", match -> match.contains())
        .withIgnorePaths("id", "uuid")
        .withIgnoreNullValues();
    Example<Interface> example = Example.of(intfc, matcher);

    return ((InterfaceRepository) baseRepository).findAll(example);
}

The .withIgnoreNullValues() is the key. It will just return everything if you send a null value instead of an enum constant.

Answer 4

JPA is generating SQL statement with equal sign. Comparing null with equal sign does not work in most DBMS. Instead is keyword is needed:

WHERE (s.status =?1 and ?1 is not null) or (s.status is null and ?1 is null)