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

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.