Spring JPA Repository Null Parameters

user_16688218 0
Viewed 2034

I have read some posts on having null values in repository requests, but they are all years old. So I ask this question to get the current state of the problem.

What I am exactly talking about:

I want to have a JPA request with optional null values. A function in my repository would look like this:

public List<MyClass> filter(Integer param);

So that I can omit the parameter in the filter like this: (part of @Query)

(:param IS NULL OR param=:param)

This only works as long as my param is not NULL.

Pls note that I am using a native query here. (JPA implementation is Hibernate, DB is postgres)

Thanks in advance for every update!

postgresql hibernate spring java
2 Answers
user_93004210

I looked at this issue fairly extensively. Observations:

  1. With custom queries using JPQL, you should be able to do something like this:
@Query("select m from MyClass m where :param IS null OR m.param = :param")
public List<MyClass> filter(@Param("param") Integer param);

assuming here of course that param is a well-defined property of a Spring entity named MyClass.

  1. If you use native queries, then you need to supply a non-null value for param. Eg:
@Query(value = "select * from MyClass m where :param < 0 OR m.param = :param", nativeQuery = true)
public List<MyClass> filter(@Param("param") Integer param);

In the event param is null-valued, you then would need to call filter() with param set to -1, for example.

I realize this is not so satisfying, and I suspect it speaks to some current limitations in Spring JPA.

user_13782680
Related