Dealing with empty or null collection parameter in JPA

Question

Inside org.springframework.data.jpa.repository.@Query annotation I check if field in a collection and possibly ignore collection if it is empty:

@Query(""select e from #{#entityName} where e.type in :lst or 0 = :lstSize")
List<Entity> findByLst(@Param("lst") List<XEnum> lst, @Param("lstSize") int lstSize);

I call code as:

List<XEnum> lst = ...;
int lstSize = (lst == null) ? 0 : lst.size();
findByLst(lst, lstSize);

For Oracle DB in case of lst = null Hibernate logged:

DEBUG [nio-8443-exec-4] org.hibernate.SQL
entity0_.type in (?) or 0=?
TRACE [nio-8443-exec-4] org.hibernate.type.EnumType
Binding null to parameter: [1]
TRACE [nio-8443-exec-4] org.hibernate.type.descriptor.sql.BasicBinder
binding parameter [2] as [INTEGER] - [0]

For case of lst = new LinkedList<>() Hibernate logged:

DEBUG [nio-8443-exec-5] org.hibernate.SQL
entity0_.type in ( ) or 0=?
TRACE [nio-8443-exec-5] org.hibernate.type.descriptor.sql.BasicBinder
binding parameter [1] as [INTEGER] - [0]
ERROR [nio-8443-exec-5] org.hibernate.engine.jdbc.spi.SqlExceptionHelper
ORA-00936: missing expression

and it is syntactically invalid in SQL*PLUS also:

select 1 from dual where 1 in ();

Can I omit lstSize and still check if collection is not provided - return all elements?

How can I deal with empty list and Oracle () syntax error?

Actually I have large JPQL expression that handle several cases of empty arguments with single call. My goal to keep simple method instead of writing several specialized methods, using if/else or Critetia builder...

For example ignoring empty parameters can be archived with:

 ... and (e.field = :fieldVal or :fieldVal is null)

UPDATE Relevant resources:

Passing empty list as parameter to JPA query throws error
https://hibernate.atlassian.net/browse/HHH-8091 (Hibernate produces SQL - "in ()" - which is invalid in at least Oracle, MySQL and Postgres)

http://stackoverflow.com/questions/2488930/passing-empty-list-as-parameter-to-jpa-query-throws-error — Robert Niestroj, Apr 22 '17 at 19:34

score 0 · Answer 1 · answered Jun 27 '20 at 16:49

Criteria API is for a rescue.

You are building SQL queries dynamically (and there is a possibility to work type safe when you generate special classes from entities by build tool plugin).

Spring Data provides convenience in a form of org.springframework.data.jpa.domain.Specification + org.springframework.data.jpa.repository.JpaSpecificationExecutor, so querying might look like:

Specification<Book> spec = (Root<Book> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
    ...
    if (CollectinUtils.isNotEmpty(ids)) {
        root.get("id").in(ids);
    }
}
List<Book> booksByIds = repository.findAll(spec);

Hibernate Criteria API supports null in AND or OR ignoring that expression making it convenient to omit empty check if you put IN operator builder in utility function.

score 0 · Answer 2 · answered Aug 13 '20 at 03:28

You can use JPA NotEmpty or IsNotEmpty

Example Code

Entity

public class products{
  ....fields

  @OneToMany(mappedBy = "productId")
  @JsonIgnore
  private List<Rating> ratingList;

  .....geter/setter
}


public class Rating{
  ....fields
 
 @JoinColumn(name = "product_id", referencedColumnName = "id")
 @ManyToOne
 @JsonIgnore
 private Products productId;

  .....geter/setter

}

Repository

public interface ProductRepository extends JpaRepository<Products, Integer>{
   public Page<Products> findByRatingListNotEmpty(Pageable pg);
}

I believe you made typo in the method: omitted `ratingList`, note that the name of methos is `findByRatingList`... — gavenkoa, Aug 13 '20 at 09:00

Dealing with empty or null collection parameter in JPA

2 Answers2