JPA Specification and null parameter in .where clause

Question

I wrote two Specifications which return null if their parameter is null.

public static Specification<Prodotto> getProdottoByLineaSpec (String linea) {

        if (linea != null) {
            return (root, query, criteriaBuilder) -> {
                return criteriaBuilder.like((root.join("linea")).get("nome"), "%"+linea+"%");
            };
        }
        else return null;
    }

public static Specification<Prodotto> getProdottoByIngSpec (String ing) {

        if (ing != null) {
            return (root, query, criteriaBuilder) -> {
                return criteriaBuilder.like(((root.join("listaQuoteIng")).join("ing")).get("nome"), "%"+ing+"%");
            };
        }
        else return null;
    }

Then I created a third one that combines the previous ones with an and operator inside a where clause:

public static Specification<Prodotto> getProdottoByMainTraits (String linea, String ing) {

        return Specification.where(getProdottoByLineaSpec(linea).and(getProdottoByIngSpec(ing)));
    }

Now, that's the funny part:

• If ByLinea returns null, i get a nullPointerException from checkPackageAccess when resolving the where clause.
• If ByIng returns null, it just gets ignored (like it should be) and the query matches just the other predicate.
• If I switch the two predicates, putting ByIng as the first one and then ByLinea inside the where clause, everything works in every combination.

Yalu

Answer 1

It is a good practice to avoid returning null from methods.

You can use criteriaBuilder.conjunction() to ignore null parameter Specification. It generates always true Predicate. There is an opposite method criteriaBuilder.disjunction()

public static Specification<Prodotto> getProdottoByLineaSpec (String linea) {
                        
    return (root, query, criteriaBuilder) -> {
        if (linea == null) {                 
            return criteriaBuilder.conjunction();
        }

        return criteriaBuilder.like(
                 (root.join("linea")).get("nome"), 
                 "%" + linea + "%"
        );            
    }
}

P.S. You get NullPointerException if first Specification is null trying to access a method and. To be clear it looks like this

Specification.where(null.and(getProdottoByIngSpec(ing)));

But if only second Specification is null this one works

Specification.where(getProdottoByLineaSpec(linea).and(null));

because and method parameter can be null

Parmentier

Thanks for your suggestion. I didn't know those methods. It actually works, you just got to call the lambda function inside else block else return (root, query, criteriaBuilder) -> criteriaBuilder.conjunction(); I upvote your answer, but wait to mark it as solution, because it doesn't explain why my original structure got no problem if I switch predicates order inside where clause. – Yalu

It is my fault in else block. I wrote the code using phone. I have edited the answer. – Parmentier

The answer also contains NullPointerException cause explanation – Parmentier

Yesterday I was experiencing this strange behaviour: if I set ByIng as first predicate, and make it return null, i didn't get the problem! That was the thing that I couldn't understand. Now I just retried, and actually it throws the expected exception. Probably yesterday was tired, and made some mistake. Thean as a general rule, is it always better to make a Specification return conjunction/disjunction instead of null, when you want it to be nullable? – Yalu

Yes. Or you should handle every possible nulls. But this approach is more error prone – Parmentier

Keep it in mind that every conjunction and disjunction add extra where clause in sql statement. For conjunction it looks this way 1=1. – Parmentier

Didn't think about that... perhaps this different approach adds overhead on the service, but eases workload on DB. https://mcmap.net/q/1921013/-spring-specification-conjunction-of-predicates What do you think? – Yalu

I can opt for this approach only for private methods. But for external api I try to avoid returning null. Conjunction and disjunction clauses do not cause any workload. They are used as predicates markers. – Parmentier

Didn't you wrote that conjunction adds an extra where clause to query? – Yalu

Yes, but clauses like 1=1 cost nothing – Parmentier

Perfect. I marked your answer as solution. Thanks a lot. – Yalu