CODEWITHSUNDEEP
javaspringhibernatejpaspring-data-jpa
Random42
Random42

Reputation: 9159

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

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.

Upvotes: 6

Views: 5699

Answers (4)

ilmar ferreira marques
ilmar ferreira marques

Reputation: 1

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

Upvotes: 0

Michal Joštiak
Michal Joštiak

Reputation: 171

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.

Upvotes: 2

Random42
Random42

Reputation: 9159

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

Upvotes: 12

Vitalii Muzalevskyi
Vitalii Muzalevskyi

Reputation: 662

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

Upvotes: 0

Related Questions