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.

Answer 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.

Answer 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.

Answer 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.

Answer 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] - [testing@hotmail.com] 

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.

Answer 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);

Answer 6

While this has been answered and the accepted answer is relevant to the current question but there is another way to handle your null parameters in a JpaRespository. Posting this here as this can be leveraged when someone wants to query by ignoring fields when null and have dynamic query built. The below code sample should demonstrate the same

public class User{
  private String firstName;
  private String lastName;
}

import javax.persistence.criteria.Predicate;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User,Long>{

  public Page<AppUser> findAll(Specification<AppUser> user,Pageable page);

  public default Page<AppUser> findAll(User user,Pageable page){
    return findAll(search(user),page);
  }

  static Specification<User> search(User entity) {
    return (root, cq, cb) -> {
      //To ensure we start with a predicate
      Predicate predicate = cb.isTrue(cb.literal(true));
      if(entity.getFirstName() != null && !entity.getFirstName().isBlank()) {
        Predicate _predicate = cb.like(cb.lower(root.get("firstName")), "%"+entity.getFirstName().toLowerCase()+"%");
        predicate = cb.and(predicate,_predicate);
      }
      if(entity.getLastName() != null && !entity.getLastName().isBlank()) {
        Predicate _predicate = cb.like(cb.lower(root.get("lastName")), "%"+entity.getLastName().toLowerCase()+"%");
        predicate = cb.and(predicate,_predicate);
      }
      return predicate;
    }
  }
}

Answer 7

I was able to apply IS NULL appropriately in case of null input using below workaround.

    @Query("SELECT c FROM ConfigRLLOAContent c WHERE ((:suffixId IS NOT NULL AND c.suffixId = :suffixId) OR (:suffixId IS NULL AND c.suffixId IS NULL))")
Optional<ConfigRLLOAContent> findByRatableUnitId(@Param("suffixId") String suffixId);

Above approach will apply filters only when suffixId is non-null, else, IS NULL filter will be applied.

There's also an issue raised on github, to which introduction of @NullMeans is proposed here.

Answer 8

I had the same issue with similar task - one parameter in the query was optional, so to get rid of this error, I managed to use the following query with 2 casts:

@Query(value = "select distinct name from table "
        + "where coalesce(cast(table.field_optional as text) = cast(?1 as text), true) "
        + "and lower(table.non_optional_field) like ?2 "
        + "limit ?3", nativeQuery = true)
List<String> method(String optionalParam, String param, int limit);

This coalesce part would transform into simple 'true' if optionalParam is null