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, Sep 05 '19 at 13:46

score 1 · Answer 1 · answered Sep 05 '19 at 11:51

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

score 0 · Answer 2 · answered Sep 05 '19 at 11:46

0

Just modify the where condition

i.item_code = :itemCode

to

:itemCode is null or i.item_code = :itemCode

answered Sep 05 '19 at 11:46

e.g78

667
4
8

That is not what is being asked. – Alan Hay Sep 05 '19 at 13:44
sorry, why not ? – e.g78 Sep 05 '19 at 14:25
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. – Alan Hay Sep 05 '19 at 14:32

Adding parameters optionally to Spring Data JPA Native query

2 Answers2