Spring Data JPA and Parameters That Can Be Null

Spring data JPA and parameters that can be null

You are right.

A request has been made to support better handling of null parameters.
https://jira.spring.io/browse/DATAJPA-121

In your case, i would advise you to write your repository implementation and to use a custom CriteriaQuery to handle your case.

Also you can use the @Query annotation with the is null syntax :

@Query("[...] where :parameter is null"
public List<Something> getSomethingWithNullParameter();

EDIT

Since Spring data jpa 2.0, spring now supports @Nullable annotation. This can be helpful to handle null parameters passed.

From the documentation :

@Nullable – to be used on a parameter or return value that can be null.

Spring data jpa native query with null parameter (PostgreSQL)

This is a common problem with Hibernate + PostgreSQL, and the solution is to implement that method yourself, instead of having Spring do it for you. Within the implementation you have to do something like this

List<CustomProjection> findByCustomCondition(
        @Param("identifierId") Long identifierId,
        @Param("eventTypes") List<String> eventTypes) {
     // entityManager is acquired with the @PersistenceContext annotation as an injectable
     Query q = entityManager.createNativeQuery(..., CustomProjection.class);
     // the important part:
     q.setParameter("identifierId", 0L);
     q.setParameter("identifierId", identifierId);
     ...

First call to setParameter ensures Hibenate uses the correct type setter, second call overrides the first value without having executed the query, and skips type detection.

Spring Data JPA - problem with null parameter for NUMBER column type

In Oracle DB it's not worked this way. A workaround is using JPQL like

SELECT s FROM Entity s WHERE :id is null OR s.id = COALESCE(:id, -1)

Or for native query use TO_NUMBER function of oracle

SELECT s FROM Entity s WHERE :id is null OR s.id = TO_NUMBER(:id)

JPA with @Param null parameters still throwing errors

Please try adding Nullable annotation -

@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null or c.email = :email)")
List<Customer> findCustomerByNameAndEmail(@Nullable @Param("name") String name, @Nullable @Param("email") String email);

---

Related Topics