CODEWITHSUNDEEP
javasqlspringspring-bootspring-data-jpa
user3248186
user3248186

Reputation: 1558

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

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)
}

Upvotes: 0

Views: 3059

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81998

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.

Upvotes: 3

Related Questions