javaspringjpaspring-el

SPEL doesn't work with 'in' operator for collections inside an object

@Query("select distinct product from Product product where :#{#activeFilters.flowerSorts} is null or product.name in :#{#filters.names}")
Set<Product> findAllForFilter(Filters filters);

public class Filters {
    private Set<String> names;
}

enter image description here

enter image description here

2023-09-19 12:02:17.183 WARN 6316 --- [nio-8080-exec-6] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} [select distinct product from ...

Hello, I'm trying to execute query with several elements in my collections. However it doesn't work, but it does with single elements.

Solution

  • I think the problem is in query syntax:

    You should have next one @Query body

    @Query("select distinct product from Product product where product.name in (:#{#filters.names})")
Set<Product> findAllForFilter(Filters filters);

    Update

    It should generated something like this:

    from Product product where product.name in (?, ?…)

    But I am not sure in it, I need to test it. As solution you also can write the query in this one way:

    @Query("select distinct product from Product product where product.name in (:filters)")
    Set<Product> findAllForFilter(@Param(“filters”) Set<String> filters);

    And when you are going to invoke the method you will pass a Set of strings aka filters, I think it is better than SPEL