Use Optional filters parameters in Jpa SQL query

In Java Persistence API (JPA), crafting queries that efficiently handle null values is a common challenge faced by us developers.
This is particularly usual when constructing queries for databases where certain parameters might be optional.
I personally had to deal with it a few times.

In this article, i’ll explore how to effectively filter null values in JPA queries.

Understanding the Challenge

Consider a scenario where you want to retrieve entities based
on multiple optional parameters, and some of these parameters
can have null values. Let’s take an example query from a Spring Data JPA repository:

@Query("SELECT pm FROM PaymentMethodVersionEntity pm WHERE (pm.store.id = :storeId OR :storeId IS NULL) AND (pm.store.code = :code OR :code IS NULL)")
List<PoliResourceEntity> findPoliResources(@Param("storeId") Long storeId, @Param("code") String code);

Explanation

In this query, we aim to retrieve PoliResourceEntity
instances based on the provided siteId and code, but we want to accommodate
cases where these parameters can be null.
These are optional parameters.

Alternative Approach

Some developers might be tempted to use the IN clause to check for null values.
However, this approach might not yield the expected results.
For instance, the condition pm.store.id IN (:storeId, NULL)
doesn’t behave as intended because SQL treats comparisons involving NULL differently.
So the Optional storeId here should be treated with the OR logical condition.
Here is the incorrect query, that serves retrieving the values including the null :

// Incorrect usage of IN clause for null values
@Query("SELECT pm FROM PaymentMethodVersionEntity pm WHERE pm.store.id = :storeId IN (:storeId, NULL)")
List<PoliResourceEntity> findPoliResources(@Param("storeId") Long storeId);

Conclusion

Filtering null values in JPA queries is a common requirement when building dynamic and flexible data retrieval systems.
By using conditional logic and understanding the behavior of SQL with respect to null values,
developers can construct queries that handle null parameters effectively.
Following best practices ensures that the code remains clear, maintainable, in the face of varying input conditions.