Spring data JPA and parameters that can be null

Question

My understanding is, that with Spring data JPA I cannot have a query method to fetch all rows where a column equals a given non-null method parameter and use the same method to fetch all rows where this column is NULL when the method parameter is null.

Is that correct?

So I have to distinguish this in my JAVA code and I must use a separate query method explicitly asking for null values, like in the example below?

// Query methods
List<Something> findByParameter(Parameter parameter);
List<Something> findByParameterIsNull();

...

List<Something> result = new ArrayList<>();

if (parameter == null)
  result = findByParameterIsNull();
else
  result = findByParameter(parameter);

That's bad, if I have 4 parameters which could be null and would have to code 16 different query methods.

Solution 1

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.

Solution 2

i found something...if u put the parameter in the jpa method like this

@Param("value") String value,

then it can be null and in the query you will have this condition:

(table.value = :value OR :value IS NULL)

if the value is null it will automatically return true and if is not null, it will search that value in the table.

Solution 3

It seems Query by Example might be what you need.

Query by Example is a new feature in Spring Data (since version Hopper, out April 2016), which allows one to create simple dynamic queries with a code like this

Person person = new Person();                          
person.setFirstname("Dave");                           

ExampleMatcher matcher = ExampleMatcher.matching()     
  .withIncludeNullValues();                        

Example<Person> example = Example.of(person, matcher);

personRepository.count(example);
personRepository.findOne(example);
personRepository.findAll(example);

Methods count/findOne/findAll that take an instance of org.springframework.data.domain.Example as a parameter (and some of them also take sorting/pagination parameters) are coming from org.springframework.data.repository.query.QueryByExampleExecutor<T> interface, which is extended by org.springframework.data.jpa.repository.JpaRepository<T, ID extends Serializable> interface.

In short, all JpaRepository instances now have these methods.

Solution 4

Today as of Jun 2018, by looking at https://jira.spring.io/browse/DATAJPA-121, the query will automatically form is null if your parameter is null.

I did that in my project, it is true:

compile group: 'org.springframework.data', name: 'spring-data-jpa', version: '2.0.7.RELEASE'

--

public interface AccountDao extends CrudRepository<T, ID> {

    //this can accept null and it will become isNull
    public List<MyAccount> findByEmail(String email);

}

if parameter is null:

select
        myaccount0_.id as id1_0_,
        myaccount0_.email as email2_0_,
        myaccount0_.password as password3_0_,
        myaccount0_.user_id as user_id4_0_ 
    from
        my_account myaccount0_ 
    where
        myaccount0_.email is null

if parameter is not null:

select
        myaccount0_.id as id1_0_,
        myaccount0_.email as email2_0_,
        myaccount0_.password as password3_0_,
        myaccount0_.user_id as user_id4_0_ 
    from
        my_account myaccount0_ 
    where
        myaccount0_.email=?
11:02:41.623 [qtp1507181879-72] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [[email protected]] 

Then it comes to an interesting question, some developers want better control to ignore the parameter in query if it is null, this is still being under investigating in https://jira.spring.io/browse/DATAJPA-209.

Solution 5

In my case membershipNumber is nullable, and I have handled it this way. This will handle all the cases where table.membershipNumber is null too.

      @Query(value = "SELECT pr FROM ABCTable pr " +
            "WHERE LOWER(pr.xyz) = LOWER(:xyz) " +
            "and LOWER(pr.subscriptionReference) = LOWER(:subscriptionReference) " +
            "and pr.billId = :billId " +
            "and ((pr.membershipNumber = :membershipId) or (pr.membershipNumber = null and :membershipId = null))")
    List<PaymentRequest> getSomething (@Param("xyz") String xyz,
                                                 @Param("subscriptionReference") String subscriptionReference,
                                                 @Param("billId") Integer billId,
                                                 @Param("membershipId") String membershipNumber);

View more solutions

Comments

[Sebastian S.]

I have extend the answer: When using the Spring data JPA @Query annotation, you have to use two separate query methods. One for handling null values and one for non-null values. But when using a query method which is parsed by the Spring data parser (without @Query annotation), it is possible to handle null or non-null values with one method!

[Stef]

The @Query :parameter is null doesn't work. It fails with an exception, but even if it wouldn't, SQL treats nulls as unknown. Thus unknown is not equal with unknown ... Basically null is not equal with null in SQL.

[Sam YC]

How Nullable help in this question? I don't think Spring Data will turn the query into "is null" if we annotate the parameter as @Nullable.

[dfritch]

One thing that got me was it wasn't working if I did (:value IS NULL OR table.value = :value) It was telling me I needed to cast :value But doing it in the order you wrote works.

[Anuj Pandey]

:vales is null won't work if the value is a collection.

[Paco Mendez]

I found this solution better (and simpler) rather than implementing JpaSpecificationExecutor or QueryDslPredicateExecutor; reference. Also I followed this blog

[Vivek Singh]

This handles all the cases I guess.

[Vivek Singh]

It will only skip the field for select query for null values passd. may lead to extra result set.

[Pezetter]

This is the example I've been looking for, however i'm receiving an error on build: No property findAll found for type User.

[ganaraj]

A full stack might help, or it could be due to JPA2 enabling annotations not added. In this case the findAll methods is explicitly defined.