Adding parameters optionally to Spring Data JPA Native query

Question

I am using Spring Data JPA with native queries like below

public interface ItemRepository extends JpaRepository<ItemEntity, Long> {

  @Query(value = "select * from items i where i.category = :itemCategory and i.item_code = :itemCode", nativeQuery = true)
  Page<ItemEntity> search(@Param("itemCategory") String itemCategory, @Param("itemCode") String itemCode, Pageable pageable);
}

Now, my use case is

It itemCode is available, only the item with that code from that category should be returned.
But if itemCode is not available then all items in that category should be returned.

So the problem with the above category is when itemCode is passed as NULL no data is returned since it does not match anything. whereas the requirement is it should be ignored.

So is there a way to optionally add a clause to Spring Data JPA native query. I know it is possible with CriteriaQuery but can we do something similar for native queries?

Thanks

Look at using the querydsl extension. That would let you call by either category, code or category and code without having to write any query implementations. — Alan Hay
– Alan Hay, Commented Sep 5, 2019 at 13:46

Maheshwar Ligade · Accepted Answer · 2019-09-05 11:51:17Z

Yes, it's feasible with native query too. Very Good explanation here read this

#Approach1

@NamedQuery(name = "getUser", query = "select u from User u"
            + " where (:id is null or u.id = :id)"    
            + " And :username"

:itemCode is null or i.item_code = :itemCode

#Approach2

# UserDao.java 

  public User getUser(Long id, String usename) {
        String getUser = "select u from user u where u.id " + Dao.isNull(id) 
                       + " And u.username " + Dao.isNull(username);
        Query query = Dao.entityManager.createQuery(getUser);
    }

# Dao.java

   public static String isNull(Object field) {
        if (field != null) {
                if (field instanceof String) {
                    return " = " + "'" + field + "'";
                } else {
                    return " = " + field;
                }

            } else {
                return " is NULL ";
            }
    }

How to handle null value of number type in JPA named query

e.g78 · Accepted Answer · 2019-09-05 11:46:35Z

0

Just modify the where condition

i.item_code = :itemCode

to

:itemCode is null or i.item_code = :itemCode

answered Sep 5, 2019 at 11:46

e.g78

6974 silver badges10 bronze badges

3 Comments

Alan Hay Over a year ago

That is not what is being asked.

e.g78 Over a year ago

sorry, why not ?

Alan Hay Over a year ago

If the passed param is null then ignore it otherwise return matching. Your answer does something else entirely. Essentially, do not add the condition i.item_code = :itemCode if it param is null.

Collectives™ on Stack Overflow

Adding parameters optionally to Spring Data JPA Native query

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related