How to write query in Spring Data JPA with multiple nullable parameters?

Question

I've got an Entity class like this :

class EntityClass {
    UUID id;
    String param1, param2, param3;
    //Getters and Setters
}

My Service class will've some method like this to make use of all the columns like this, and I want to be able to handle all requests with the same repository method instead of writing multiple repository methods for each case where a combination of parameters is null.

class ServiceClass {
    List<EntityClass> getAllBy(List<String> param1, List<String> param2, String param3) {
        return repositoryInterface.customFindAll(param1, param2, param3);
    }
}

Now my repository class should've this method. But I want to be able to handle the case where any combination of {param1, param2, param3} can be null as in {param1 = null or empty, param2 != null, param3 != null} or {param1 = null or empty, param2 = null or empty, param3 != null} etc.

The repository method customFindAll should basically be able to search entity table to check if param1 column value is in any of the lists of values sent etc. if not null.

So how should I handle the native query in spring JPA to do such a thing instead of creating different methods for different combinations?

interface RepositoryInterface extends JpaRepository<EntityClass, UUID> {
    @Query(value = "FILL THE QUERY", nativeQuery = true)
    List<EntityClass> customFindAll(@Param("param1") List<String> param1, @Param("param2") List<String> param2, @Param("param3") String param3)
}

if they are null why do you need to execute this method? even though in database there will be nothing to compare with null — Ryuzaki L
– Ryuzaki L, Commented Jan 28, 2019 at 18:59
@Deadpool Yea that's what I want to do - if my service method receives a list of values then search like param1 in :param1, but if it receives a null value for param1, don't serach for it. — user3248186
– user3248186, Commented Jan 28, 2019 at 19:03
@Deadpool As in check in service class and based on that call the appropriate repository method? If so, then I'll 've to create many repository methods - one for each combination, right? — user3248186
– user3248186, Commented Jan 28, 2019 at 19:28
When everything is not null then call that method, if something is null or empty don't call, i believe there is no use if any of them are null or empty — Ryuzaki L
– Ryuzaki L, Commented Jan 28, 2019 at 19:30

Jens Schauder · Accepted Answer · 2019-01-29 07:19:02Z

3

The general approach to this is to use a where clause like this:

WHERE (:param1 IS NULL OR param1 IN :param1) ...

But I don't think you can do a is null check on a list-valued bind parameter. But you can use SpEL expressions for this.

This should do the trick:

SELECT id, param1, param2, param3
FROM EntityClass
WHERE (:#{#param1 == null ? 0 : 1} = 0 OR param1 IN :param1) 
AND   (:#{#param2 == null ? 0 : 1} = 0 OR param2 IN :param2) 
AND   (:#{#param3 == null ? 0 : 1} = 0 OR param3 IN :param3)

The comment is probably right: IN on an empty or null parameter might just not parse as a legal SQL statement. In that case you can use even more SpEL force (repeat three times as above):

( param1 IN :#{(#param1 == null || #param1.isEmpty()) ? java.util.Collections.singletonList(#param1) : #param1})

Of course, all this gets rather ugly, so you might be better of creating your query dynamically using Specifications.

edited Jan 29, 2019 at 7:19

answered Jan 29, 2019 at 6:56

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3248186 Over a year ago

but if :param1 is NULL or an empty list, won't it try to check in NULL or () which raises an error?

Collectives™ on Stack Overflow

How to write query in Spring Data JPA with multiple nullable parameters?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related