Passing Empty List as Parameter to JPA Query Throws Error

Passing empty list as parameter to JPA query throws error

According to the section 4.6.8 In Expressions from the JPA 1.0 specification:

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

In other words, regardless of Hibernate's ability to parse the query and to pass an IN(), regardless of the support of this syntax by particular databases (PosgreSQL doesn't according to the Jira issue), you should use a dynamic query here if you want your code to be portable (and I usually prefer to use the Criteria API for dynamic queries).

JPA 'where in' to be considered only if List passed as parameter has elements

After lots of trial and error I found an acceptable working solution with SpEL; thought some might find it useful:

@Query("select a from Alert a where a.date >= :startDate " 
        "and (:#{#countryIds == null} = true or (a.countryId in (:countryIds))) " +
        "and (:#{#siteTypeIds == null} = true or (a.siteTypeId in (:siteTypeIds))) ")
List<Alert> findBy(@Param("startDate") Date startDate, 
                   @Param("countryIds") Set<Long> countryIds,
                   @Param("siteTypeIds") Set<Long> siteTypeIds);

The Sets sent as parameters have to be null instead of empty sets.
It yields an acceptable SQL:

select alert0_.alertId              as alertId1_0_, [...]
from alert alert0_
where alert0_.date >= '2018-01-01' and
      (0 = 1 or alert0_.countryId in (1, 123)) and
      (1 = 1 or alert0_.siteTypeId in (null));

Empty in expression cause an exception in native query

The postgresql documentation states: in predicate can not hold empty list

expression IN (value [, ...])

and as hibernate passes the native queries as is, it's predictable that this leads to a problem.

But as workaround you can follow this suggestion: initialize the list with an impossible value.

EDIT

Actually, this behaviour was changed in hibernate 5.4.10. See the commit:

SHA-1: ab9ae431858dc6727023d7f03bd6925f99011c62

* HHH-8901 replace "in ()" SQL with "in (null)" in QueryParameterBindingsImpl

And for your case the following sql will be generated:

select * from history h where h.project_id in (null)

It looks strange that the task HHH-8901 description completely irrelevant to these changes.

Spring Data JPA: return empty List instead of null

I have noticed few thing in the question: (update the answer)

1. No need to extends both CrudRepository<HotelPrice, Long>, PagingAndSortingRepository<HotelPrice, Long>. PagingAndSortingRepository<HotelPrice, Long> internally extends CrudRepository<HotelPrice, Long>.
2. Instead of passing the entire Hotel object you can pass only primary key of Hotel (id). Below given the syntax for it:
   Page<HotelPrice> getByHotelIdAndUpdateDateGreaterThan (Long id, Date date, Pageable pageable);. You can send the PageRequest from service
   but you must have Pageable in Repository.
3. When you use pagination, you won't get the response as List<HotelPrice>, Instead of that you will get the Page<HotelPrice> object. To extract the List<HotelPrice> use the below:
   List<HotelPrice> hotelPrice = hotelPricePage.get().getContent();.

Hibernate JPA: 'IS EMPTY' condition doesn't work for collections passed as parameters

The JPA documentation clearly states, that you should avoid empty collections as parameters.

4.6.8 In Expressions from the JPA 1.0 specification

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If you think about it, JPA can't do much about it. if the query contains WHERE id in ?0 - how to handle an empty collection? WHERE id in () is invalid syntax, as well as WHERE id in... So it would neet to remove the whole where-constraint, which is not what is desired in most situations.

Guess you need to handle this in your Application rather than on a query level.

Side-Node: If you are working with filter values with only little options, you could get the result by adding ALL ids, if the user does not select something, i.e.

where color_id in (1,2,..,10) and type_id in (1,2,3) AND tile_id in (1,2,..,5)
instead of the invalid
where color_id in () and type_id in () AND tile_id in ()

Just not working, if you have thousands of options, or you'll exceed the query length.

Spring Data: findByMyListIsEmpty() throws a compilation error

Firstly, there is no keyword as IsEmpty/Empty that can be used while querying using spring data JPA findBy queries.Therefore, you are getting compilation error-since it is not supported. Please refer to this link for supported keywords by spring- https://docs.spring.io/spring-data/jpa/docs/current/reference/html/ - Table 3. Supported keywords inside method names provided by spring.

IsNull is used to check against Null values. Since in your case list is empty, I would suggest try using findByMyListIn(List list) method and pass empty list as argument. I have not checked this solution practically but as per spring doc, it should work.

Apart from that, also check relationship between entities since you have used @JoinTable annotation but there is no mentioning of associative entity/join table in your post.

As an alternate approach, you can always write your own custom queries if builtin methods do not give proper results.

Hope this help.

---

Related Topics