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

Question

I have one query that should filter based on various parameters; one of those parameters is a list. If there are entries in the list, there should be a filtering based on the entries; but if the list is empty/null, there shouldn't be any filtering on that field.

What I've thought is something like this:

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

Sending null List it throws NPE; sending an empty list it generates the following SQL, which is invalid

where alert0_.date >= '2018-01-01' and                                                       
 ((1, 123) is null or alert0_.countryId in (1, 123))

I've also tried in JPQL to have and (((:countryIds) is empty) or a.countryId in (:countryIds)) but it also doesn't work when trying to compile the JPQL (at application startup): Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: ??? is not mapped at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:171) Or using SpEL: "and (:#{countryIds.size() > 0} or (a.countryId in (:countryIds))) " but again, it doesn't compile the JPQL.

The only solution I've thought is to dynamically generate the JPQL which is ugly or to populate all existing values for countryIds and siteTypeIds which is inefficient.

JPA implementation is Hibernate and database is MySQL.

Use the criteria API or QueryDSL predicates to create dynamic queries. — M. Deinum, Aug 08 '18 at 11:21
If your JPA provider throws an NPE then you should raise a bug on your JPA provider. The JPQL ought to cater for null input parameters. — , Aug 08 '18 at 12:24

score 10 · Accepted Answer · answered Aug 08 '18 at 12:19

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

Hi, empty is also possible to write as: ( :#{#countryIds== null} = true or :#{#countryIds.size() == 0} = true or a.countryId in :#{#countryIds.size() == 0 ? null : #countryIds} ) — Michal Joštiak, Feb 07 '19 at 11:47
you could also write `COALESCE(:countryIds) IS NULL` instead of `:#{#countryIds == null} = true` — Olivier Boissé, Jun 04 '22 at 21:09

Michal Joštiak · Answer 2 · 2019-02-07T12:38:28.010

I had the same problem so im writing extended solution with using also embedded parameter

@Query("from PartPrice where "
            + "customer in :#{#customers} and "
            + "( (:#{#suppliers == null || #suppliers.size() == 0} = true and supplier is null) or (:#{#suppliers != null && #suppliers.size() > 0} = true and supplier in :#{#supplier}) ) and "
            + " productIdentifier.manufacturerId = :#{#productIdentifier.manufacturerId} and productIdentifier.productNumber = :#{#productIdentifier.productNumber} and "
            + " ( (:#{#isAbsPrice} = true and abs_price is not null) or (:#{#isAbsPrice} = false and abs_price is null) ) "
            + " and (validUntil is null or validUntil >= :#{#fromDate}) and (:#{#untilDate == null} = true or validFrom <= :#{#untilDate}) ")

where suppliers is nullable, empty or contains values and productIdentifier is embedded id containing productNumber and manufacturerId passing as

@Param("productIdentifier") ProductIdentifier productIdentifier

Also interval is valid from fromDate to null (forever) or untilDate.

The answer might be improved by just highlighting the relevant part : `:#{#suppliers != null && #suppliers.size() > 0} = true and supplier in :#{#supplier}`. Personnally, I've achieved something similar thanks to your solution : "find entities whose id is found in list_of_ids, or all entities if list_of_ids is null or empty" : `SELECT ... WHERE :#{#list_of_ids == null || #list_of_ids.size() < 1} = true OR myEntity.id IN :list_of_ids` — amanin, Sep 14 '22 at 10:56

score 0 · Answer 3 · answered Aug 08 '18 at 11:23

0

As for me the best solution for such cases is Criteria API, if you not familiar with it you can find some information here:

https://www.objectdb.com/java/jpa/query/criteria

answered Aug 08 '18 at 11:23

Vitalii Muzalevskyi

642
7
17

ilmar ferreira marques · Answer 4 · 2023-03-12T06:26:13.770

After some try and errors I got this working for those who`d like to handle the empty lists when using IN JPA clause. If there is at least one element it filters normally otherwise it will bring the entire resultset if the collection is empty:

@Query("SELECT h FROM Holiday h WHERE (:#{#ids.empty? T(java.util.Arrays).asList(null) : #ids} is null or h.id in :#{#ids.empty? T(java.util.Arrays).asList(null) : #ids} )")
Optional<List<Holiday>> listAllIn(@Param("ids") Collection<Long> ids);

Got this working on Eclipselink 2.7.11.v20220804-52dea2a3c0 + spring-data-jpa 2.7.7

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

4 Answers4