3

Is there a way to check util.List size? for example, if is it empty or not?

I tried the below query and it generates QuerySyntaxException with a message ??? is not mapped

..where (:filters IS EMPTY or  (d.id in (:filters))")
    List<EntityXX> getXXX(List<Integer> filters);
Oussama Werfelli
  • 513
  • 3
  • 14
  • 1
    There is no such technique to check the size of passed collection...Maybe do it like: `where (:areFiltersEmpty IS TRUE OR (d.id in (:filters)))`, where the value for parameter `areFiltersEmpty` will be the result of `filters.isEmpty()` – dbl Jan 04 '19 at 15:00
  • Another [reference](https://stackoverflow.com/questions/3760711/hql-query-to-check-if-size-of-collection-is-0-or-empty) that could be useful. – dbl Jan 04 '19 at 15:26

1 Answers1

3

As dbl answered in his comment, it is NOT possible to check the size of a List<> passed as @Param.

You're using HQL, so since a JPQL-query is always a valid HQL-query, I want to share my solution. 

@Query("SELECT p FROM Product p "
            + "LEFT JOIN p.categories category "
            + "WHERE p.name LIKE CONCAT('%', :searchRequest, '%') "
            + "AND p.description LIKE CONCAT('%', :description, '%') "
            + "AND p.price BETWEEN :priceLow AND :priceHigh "
            + "AND p.archived = :archived "
            + "AND category.name IN :selectedCategories "
            + "GROUP BY p "
            + "HAVING SIZE(p.categories) >= :amountOfSelectedCategories"

    )
    Page<Product> findAllBySearchModel(
            Pageable pageable,
            @Param("searchRequest") String searchRequest,
            @Param("description") String description,
            @Param("priceLow") BigDecimal priceLow,
            @Param("priceHigh") BigDecimal priceHigh,
            @Param("archived") boolean archived,
            @Param("selectedCategories") List<String> selectedCategories,
            @Param("amountOfSelectedCategories") int amountOfSelectedCategories
    );

Since a WHERE ... IN clause always uses an OR condition, and I wanted to narrow down my search, I had to find another way to further limit my result.

Therefore, I needed the size of my passed in List. In JPQL you can easily use SIZE(), except on @Param.

Which led to a total workaround, adding the size of my List as an extra @Param. I also check for an empty List in my Service-layer. 

if (!searchModel.getSelectedCategories().isEmpty()) {
                return productService.findAllBySearchModel(
                                pageable,
                                searchModel.getSearchRequest(),
                                searchModel.getDescription(),
                                searchModel.getPriceLow(),
                                searchModel.getPriceHigh(),
                                searchModel.isArchivedView(),
                                searchModel.getSelectedCategories(),
                                searchModel.getSelectedCategories().size()
                );
            }

*NOTE: that my code is a bit simplified and does not meet production-environment standards. I merely wanted to provide a small example for those struggling with the same problem.

Paul
  • 841
  • 13
  • 20