One Simple Technique to Handle Null Parameters for Your JPA Query

Writing SQL queries can be sometimes quite complex, and writing them with JPA can be even more complex, so to be able to handle a null query parameter, we can use the OR operator from SQL.

The OR operator in SQL is a powerful tool for combining multiple conditions in a WHERE clause to filter results based on different criteria.

In this blog post, you will learn how the OR operator works and how to use it to check your JPA SQL query params.

How the OR Operator Works

The OR operator is used in a WHERE clause to combine multiple conditions, where at least one of the conditions must be true for the row to be included in the result set. The syntax for the OR operator in SQL is:

SELECT *
FROM table_name
WHERE condition1 OR condition2;

If either condition1 or condition2 (or both) evaluates to true, then the row will be included in the result set.

Short-Circuit Evaluation

SQL evaluates conditions from left to right when using the OR operator. If the left-hand side condition is true, the right-hand side condition is not evaluated because the OR condition as a whole is already true. This is known as short-circuit evaluation.

Here's an example to illustrate the usage of the OR operator in a query:

SELECT *
FROM employees
WHERE department = 'HR' OR salary > 50000;

In this example, if an employee belongs to the HR department, the OR condition is true, and the query does not need to check the salary condition for that employee. This short-circuit evaluation can improve query performance by avoiding unnecessary evaluations.

Practical case with JPA Request

When using @Query for writing native SQL queries with JPA, you can face the case when you have to construct a dynamic query with a parameter which can be present (null) or not.

To achieve that, you can use the OR operator like the example below

@Query(
	"SELECT e FROM Entity e " +
	"WHERE (:paramName IS NULL OR e.field = :paramName)"
)
List<Entity> findEntitiesByField(@Param("paramName") String paramName);

If you want to add more filters in your query using this technique, you can use the AND operator

@Query(
	"SELECT e FROM Entity e " +
	"WHERE (:param1 IS NULL OR e.field = :param1) " +
	"AND (:param2 IS NULL OR e.field = :param2)"
)
List<Entity> findEntitiesByField(@Param("param1") String param1, @Param("param2") String param2);

Closing thoughts

Understanding the short-circuit behaviour of the OR operator can help optimize query performance.

By placing the simplest or most likely condition on the left-hand side of an OR statement, you can potentially avoid expensive evaluations of the right-hand side condition.

Knowing how the OR operator works and its order of checking can help improve query efficiency and performance.