Optional parameter in query method with explicit null support in Spring JPA

Question

As shown in this answer, I want to ignore the fields which are not specified in filter but I also want a way to explicitly specify if a field is null.

Basically something like this:

class User {
    String f1;
    String f2;
    String f2;
}

class UserFilter {
    Optional<String> f1;
    Optional<String> f2;
    Optional<String> f3;
}

class UserService {
    List<User> findBy(UserFilter u){
        userRepository.findAll(Example.of(user));
    }
}

If Optional<String> is null then it should ignore that field but if its not null and is empty then I should explicitly check for nullability condition in database.

e.g If f1 and f3 are not null and not empty and f2 is null then it should findby f1 and f3 and ignore f2's value but if f2's value is not null and is empty then it should return entities who's f2 is null in database.

İsmail Y. · Answer 1 · 2022-03-16T10:01:49.157

First you need to include null fields in ExampleMatcher because default matcher does not include it.

Second if optional is null you need to exclude this field.

Short, you can write a method like this:

public static Example<User> getExampleByUserFilter(UserFilter userFilter) {

    User user = new User();
    ExampleMatcher exampleMatcher = ExampleMatcher.matchingAll()
            .withIgnorePaths("id") // (1)
            .withIncludeNullValues();

    Optional<String> optionalF1 = userFilter.getF1();
    if (optionalF1.isPresent()) {
        if (!optionalF1.get().isBlank()) {
            user.setF1(optionalF1.get());
        }
    } else {
        exampleMatcher = exampleMatcher.withIgnorePaths("f1");
    }

    Optional<String> optionalF2 = userFilter.getF2();
    if (optionalF2.isPresent()) {
        if (!optionalF2.get().isBlank()) {
            user.setF2(optionalF2.get());
        }
    } else {
        exampleMatcher = exampleMatcher.withIgnorePaths("f2");
    }

    // other fields ..

    return Example.of(user, exampleMatcher);
}

Let's make an example and look at the sql query output:

...

UserFilter userFilter1 = new UserFilter(
        Optional.of("f1"),
        Optional.empty(),
        Optional.of("f3"));

UserFilter userFilter2 = new UserFilter(
        Optional.of("f1"),
        Optional.of(""),
        Optional.of("f3"));

userRepository
        .findAll(getExampleByUserFilter(userFilter1));
userRepository
        .findAll(getExampleByUserFilter(userFilter2));
...

The console output for this example:

Hibernate: select ... from user user0_ 
    where user0_.f1=? and user0_.f3=?
Hibernate: select ... from user user0_ 
    where user0_.f1=? and user0_.f3=? and (user0_.f2 is null)

(1) The important thing here is that you need to eliminate all the fields that you will not use in the filter with withIgnorePaths(...) in the first step.

OR

I recommend using Specifications and Querydsl for such conditional operations.

You can extends from JpaSpecificationExecutor too and get the Predicate with QueryByExamplePredicateBuilder from Example.

Optional parameter in query method with explicit null support in Spring JPA

1 Answers1