Spring Data - ignore parameter if it has a null value

Question

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?

The mechanism of deriving a query out of a repository method name provided by Spring Data is meant for cases where the query is known in advance. It is not practical to expect that mechanism to work with queries that are precisely known only at runtime. For dynamic situations there are several other options such as `@Query` and QueryDSL. SQL and JPA support the `COALESCE` function, which can be used to work around a parameter that can sometimes have a `NULL` value. `@Query("SELECT e FROM MyObject e WHERE COALESCE(e.parameterOne, ?1) = ?1 AND COALESCE(e.parameterOne, ?2) = ?2")` should work. — manish, May 05 '17 at 08:38
@manish I think `COALESCE(?1, e.parameterOne) = e.parameterOne` is your intation. — Blank, May 05 '17 at 10:07
@Forward, I have only given a direction to the poster as I am not sure about how the poster wants the matching to work exactly. For example, it has not been specified whether the database can contain `null` values for those columns and if yes, how should matching work, and so on. But yes, based just on what has been posted, your comment is spot on. — manish, May 05 '17 at 10:10
@manish COALESCE will return only the first non null value, what about if we have different scenarios — Rishabh Agarwal, Feb 13 '18 at 03:55
@manish Although the logic returned by the coalesce is perfectly suited for this use case, there are databases (eg MySQL) where this will not work, because you can't use arithmetic operators to test for null. https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html — Chris Neve, Apr 18 '18 at 09:33
One could use the JpaRepository along with Specification as explained here https://stackoverflow.com/a/61948111/3440284 , which allows you to handle this with a builder. — ganaraj, May 22 '20 at 05:01
Does this answer your question? [Spring data JPA and parameters that can be null](https://stackoverflow.com/questions/30219486/spring-data-jpa-and-parameters-that-can-be-null) — ganaraj, May 22 '20 at 08:25
You can use SpEL as mentioned in https://stackoverflow.com/a/63776549/5448746 — Ondřej Stašek, Jan 10 '22 at 13:10

Kadzhaev Marat · Answer 1 · 2020-01-24T11:07:21.813

60

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)

edited Jan 24 '20 at 11:07

answered May 04 '17 at 10:44

Kadzhaev Marat

1,278
1
19
33

what if its an or between the param1 and param 2 checks? the null is null will return all the table – user666 Jan 21 '21 at 09:27
2

It works only with string value. It does not work with any other type.Also it does not work in case of IN clause because null or empty check not work with list. – abhishek ringsia Feb 09 '21 at 08:16

Dovmo · Answer 2 · 2019-02-10T19:08:28.947

34

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

Yields:

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> {}

edited Feb 10 '19 at 19:08

answered Jan 09 '19 at 04:33

Dovmo

8,121
3
30
44

2

Awesome: Exactly what I was searching for. If someone is extending JPARepository then you need not extend QueryByExample interface as JPA repository already extends it. – Neeraj Jain Feb 10 '19 at 08:14
Is it possible that if 2nd parameter is null so the query should be "select * from myObject where parameter1 = "foo" and parameter2 is null" – Muhammad Waqas Dilawar Mar 28 '19 at 17:05
2

@Wiqi The suggestion above does not query `null` values, specified by the `#withIgnoreNullValues`. One can either use `withIncludeNullValues` and/or a `NullHandler` to modify and have finer control over that behavior. – Dovmo Apr 01 '19 at 15:36
what if i want to pass list of MyObject. – karthick S Nov 22 '19 at 06:19
When you were calling in your example the findAll, were you calling the one that comes from the QueryByParameters I assume. What happen if we were extending from another repository with same methods but different type of argument? I'm might be wrong, but in that case where we are forced to keep using methods from original repository, it might not work. – Columb1a May 18 '20 at 20:28

Abdullah Khan · Answer 3 · 2022-11-09T15:54:44.230

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.

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

Update:

The mentioned ticket has been closed and the Spring team is no longer interested in implementing this feature because of the complexities involved and also because Query by Example is a feasible option. Check out this comment.

score 13 · Answer 4 · answered Jul 16 '20 at 13:54

13

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

answered Jul 16 '20 at 13:54

Tsvetoslav Tsvetkov

954
10
24

1

this should be the answer – Adam Mudianto Dec 18 '20 at 09:51
unfortunately this is not applicable for ```nativeQuery``` – Serga Jun 25 '22 at 14:40
It only works with string parameters. – Tooraj Jam Sep 20 '22 at 04:54

score 9 · Answer 5 · answered Jan 14 '20 at 10:14

9

Try this Kolobok

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

answered Jan 14 '20 at 10:14

Sergey Grigorchuk

161
1
4

GYOTO · Answer 6 · 2021-07-06T10:38:59.203

3

JPA Query

@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name)")

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.

edited Jul 06 '21 at 10:38

answered Jun 05 '21 at 09:26

GYOTO

103
5

score 1 · Answer 7 · edited Feb 09 '21 at 08:59

I used Criteria Builder using 3 classes

Repository Interface using JPA

 @Repository
 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 {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    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))
            predicates.add(notification.get("notificationAckStatus").get("ackStatusCode").in(status));
        if (CollectionUtils.isNotEmpty(channels))
            predicates.add(notification.get("notificationRequestChannel").get("notificationChannel").get("channelCode").in(channels));


        if (!predicates.isEmpty()) {
            query
                    .select(notification)
                    .where(cb.and(
                            predicates.toArray(new Predicate[predicates.size()])));

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

score 0 · Answer 8 · answered Feb 15 '19 at 21:59

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() ) {
    staff.setFirstName(firstName);
    }



    if (!lastName.isEmpty() ) {
    staff.setLastName(lastName);
    }

    if (!ptAadhar.isEmpty() ) {
        patient.setPtAadhar(ptAadhar);
    }

    if (!Cell.isEmpty() ) {
        staff.setCell(Cell);
    }


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

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

score 0 · Answer 9 · answered Jul 30 '19 at 11:55

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

score 0 · Answer 10 · answered Nov 20 '21 at 09:26

0

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 = '')");

answered Nov 20 '21 at 09:26

Payam Soudachi

301
3
5

thurst0n · Answer 11 · 2022-11-29T23:37:47.073

Using Spring JPA Data Specification is a really good way to solve this issue.

This solution should work for all data types and allow all DB functions (greater/less/like/equal/or/not/etc).

I personally find that encapsulating the business logic/criteria builder into these makes the service code much more readable, especially if you give good names to your specification methods. You can even have private methods that your public methods use to make your Specification code more readable!

For OP's example, create a class with these methods.

public static Specification<Entity> equalsParameterOne(String parameterOne){
    //If the specification is null it will be excluded from the SQL and ignored
    if(parameterOne == null || parameterOne.length = 0) return null;
         
    return (root, query, cb) -> cb.equal(root.get("fieldOne"), parameterOne);
}

public static Specification<Entity> equalsParameterTwo(String parameterTwo){
    //If the specification is null it will be excluded from the SQL and ignored
    if(parameterTwo== null || parameterTwo.length = 0) return null;
         
    return (root, query, cb) -> cb.equal(root.get("fieldTwo"), parameterTwo);
}

Then in your service code where you use the jpaRepo you can use with findAll() like this.

//Be careful with your parenthesis
Specification<Entity> customDynamicSpecs = 
Specification
.where(equalsParameterOne(criteria.getParamOne()))  
.and(equalsParameterTwo(criteria.getParamTwo()));
//.or().not()
//... as many as you want.

//findAll() can take Pageable or Sort as 2nd argument for extra functionality.
repo.findAll(customDynamicSpecs);

To make this work, you must declare your repo extends JpaSpecificationExecutor as well as make a few more static imports than anyone should probably be comfortable with. But again, for me, the readable code wins out.

score 0 · Answer 12 · answered Jul 29 '23 at 03:25

If there is or condition between parmeters then below is an example working for me.

@Query(value = "select distinct P from PostEntity P where (P.community.id = :communityId or P.communityParentCategory.id in :parentCats or P.communityCategory.id in :cats or (:countryId is not null and P.country.id=:countryId) or (:stateId is not null and P.state.id=:stateId) or (:districtId is not null and P.district.id=:districtId)) and (:postType is null or P.postType = :postType) and (:isNotice is null or P.isNotice = :isNotice) and (:isReported is null or P.isReported = :isReported) and (:isInternalPost is null or P.isInternalPost = :isInternalPost)  and P.parent.id is null and P.status = :postStatus order by P.createdDate desc")
Page<PostEntity> getCommunityPosts(int communityId, Pageable pageable, PostType postType, PostStatus postStatus, Boolean isNotice, Boolean isReported, Boolean isInternalPost, Set<Long> cats, Set<Long> parentCats, Long countryId, Long stateId, Long districtId);

The condition (:stateId is not null and P.state.id=:stateId) or (:districtId is not null and P.district.id=:districtId)) is evaluating perfectly fine in this case.

score -2 · Answer 13 · answered Feb 12 '20 at 13:16

You could do that too.

Repository:

`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

score -4 · Answer 14 · answered Feb 13 '18 at 18:28

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){
      if(flag){
          queryBuilder.append("where condition...");
            flag=false;
        } 
      }
    if(parameterOne != null){
    if(flag){
     queryBuilder.append("where condition...");
     flag = false;
    }else{
      queryBuilder.append("and condition...");
    }
   Query query = entityManager.createQuery(queryBuilder.toString());

This opens a way for sql injection attack – Kamil Feb 08 '19 at 13:23 — Kamil, Feb 08 '19 at 13:23

Spring Data - ignore parameter if it has a null value

14 Answers14

Linked

Related