Spring Data - ignore parameter if it has a null value

SpringSpring BootSpring Data

Spring Problem Overview

I want to have a spring data repository interface that takes two parameters. Is there a way to make it have the following behaviour?

MyObject findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);

If both parameters have a value, I would like it to behave normally and do an "AND" for both values.

If for example the second parameter is null, then it would search only by ParameterOne

Any suggestions?

Spring Solutions

Solution 1 - Spring

I'm not sure it is possible with repository methods naming but you can use @Query like

(:parameterOne is null or parameter1 = :parameterOne) and (:parameterTwo is null or parameter2 = :parameterTwo)

Solution 2 - Spring

One solution that's missing here is Spring Data JPA's Query By Example feature and leverage the ExampleMatcher#ignoreNullValues, which is built exactly to solve this problem. A custom query and query builder are not necessary.

This Spring Data query:

ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject("foo", null), matcher);
List<MyObject> results = repository.findAll(exampleQuery);

Yields a query that looks like:

select * 
from myObject 
where parameter1 = "foo"

While the following:

ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject("foo", "bar"), matcher);
List<MyObject> results = repository.findAll(exampleQuery);


select * 
from myObject 
where parameter1 = "foo"
and parameter2 = "bar"

Very cool!

Note: One thing you'll have to do to your Repository interface is add the QueryByExample interface. You can do this either by extending the QueryByExample interface directly, or implicity via the JpaRepository:

public interface MyObjectRepository extends JpaRepository<MyObject, Long> {}

Solution 3 - Spring

Currently this is not possible in Spring-data-jpa.

There is a JIRA ticket regarding this which is still under investigation by the Spring team.

enter image description here

However if you want a workaround you can checkout a simple criteria query example.

Solution 4 - Spring

Here is the way for do so:

@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(@Param("name") String name, @Param("email") String email);

Solution 5 - Spring

Try this Kolobok

Iterable<MyObject> findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);

Solution 6 - Spring

  1. JPA Query
@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name)")
  1. JPA Query (nativeQuery = true)
@Query(nativeQuery = true, "SELECT id, name FROM Customer WHERE (false = :nameOn OR name = :name)")
List<Entity> findAll(@Param(value = "nameOn") Boolean nameOn, @Param(value = "name ") String name);
  • nativeQuery need change name to EMPTY String if name is null.

Solution 7 - Spring

I used Criteria Builder using 3 classes

Repository Interface using JPA

 public interface NotificationRepository extends JpaRepository<Notification, 
 Long>, NotificationRepositoryCustom {

custom interface

public interface NotificationRepositoryCustom {

    List<Notification> findNotificationByCustomerIdAndRecipientAndNotificationAckStatusAndNotificationRequestChannel
            (Long customerId, String userId, List<String> status, List<String> channels);

implementation of NotificationRepositoryCustom

public class NotificationRepositoryCustomImpl implements NotificationRepositoryCustom {

    private EntityManager entityManager;

    public List<Notification> findNotificationByCustomerIdAndRecipientAndNotificationAckStatusAndNotificationRequestChannel(Long customerId, String userId, List<String> status, List<String> channels) {

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Notification> query = cb.createQuery(Notification.class);
        Root<Notification> notification = query.from(Notification.class);

        List<Predicate> predicates = new ArrayList<Predicate>();
        if (userId != null)
            predicates.add(cb.equal(notification.get("recipient"), userId));
        if (customerId != null)
            predicates.add(cb.equal(notification.get("customerId"), customerId));
        if (CollectionUtils.isNotEmpty(status))
        if (CollectionUtils.isNotEmpty(channels))

        if (!predicates.isEmpty()) {
                            predicates.toArray(new Predicate[predicates.size()])));

        return entityManager.createQuery(query).getResultList();

Solution 8 - Spring

I am new in Spring/JPA space,

use 'Query By Example'

i am using (in seviceImp) , all below arguments are optional/ depends on user choice

	if (!firstName.isEmpty() ) {
	if (!lastName.isEmpty() ) {
	if (!ptAadhar.isEmpty() ) {
	if (!Cell.isEmpty() ) {

      Example<StaffEntity> example = Example.of(staff);  

      List<StaffEntity> staffList =staffRepository.findAll(example);

Solution 9 - Spring

Try this one,

      @Query(value = "SELECT pr FROM ABCTable pr " +
        "WHERE((pr.parameterOne = :parameterOne) or (pr.parameterOne = null and :parameterOne = null)) and 
		((pr.parameterTwo = :parameterTwo) or (pr.parameterTwo = null and :parameterTwo = null)) ")
      List<PaymentRequest> getSomething (@Param("parameterOne") String parameterOne,
                                             @Param("parameterTwo") String parameterTwo);

Solution 10 - Spring

If you want to check the parameter has a null or empty value, you should do like this:

@Query("SELECT t FROM Test t WHERE (:parameterOne IS NULL) OR (:parameterOne = '')");

Solution 11 - Spring

You could do that too.


`MyObject findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);`

if you pass a null parameterTwo, the generated JPQL will include the IS NULL condition:

`myobject0_.parameterTwo is null`

Ex: repository.findByParameterOneAndParameterTwo("D", null);

Reference: https://www.baeldung.com/spring-data-jpa-null-parameters#query-methods

Solution 12 - Spring

I am not sure if its possible using Repo as a separate class but you can use StringBuilder append query with option parameter. This will definitely work

 StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select p.name from personDemographic p "); 
    Boolean flag = true;
    if(parameterOne != null){
          queryBuilder.append("where condition...");
    if(parameterOne != null){
     queryBuilder.append("where condition...");
     flag = false;
      queryBuilder.append("and condition...");
   Query query = entityManager.createQuery(queryBuilder.toString());


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPanosView Question on Stackoverflow
Solution 1 - SpringKadzhaev MaratView Answer on Stackoverflow
Solution 2 - SpringDovmoView Answer on Stackoverflow
Solution 3 - SpringAbdullah KhanView Answer on Stackoverflow
Solution 4 - SpringTsvetoslavView Answer on Stackoverflow
Solution 5 - SpringSergey GrigorchukView Answer on Stackoverflow
Solution 6 - SpringGYOTOView Answer on Stackoverflow
Solution 7 - Springabhishek ringsiaView Answer on Stackoverflow
Solution 8 - SpringM SinghView Answer on Stackoverflow
Solution 9 - SpringSanaView Answer on Stackoverflow
Solution 10 - SpringPayam SoudachiView Answer on Stackoverflow
Solution 11 - SpringMatheus AlmeidaView Answer on Stackoverflow
Solution 12 - SpringRishabh AgarwalView Answer on Stackoverflow