Spring Data (REST) & @Query: optional list as param

Question

I'm having the following function in one of my repositories:

@RestResource(path = "filter", rel = "filter")
@Query("SELECT t "
        + "FROM Trip t "
        + "WHERE "
        + "(:from IS NULL OR t.startTs >= :from) "
        + "AND (:categories IS NULL OR t.category IN :categories) ")
Page<Trip> filter(
        @Param("from") Instant from,
        @Param("categories") List<Category> categories,
        Pageable pageable);

The Category is an enum which is stored as:

@Enumerated(EnumType.STRING)

in the Trips table.

When I'm doing my HTTP request with exactly one category I'm getting the correct results. Same behaviour when doing a request without categories key.

htt*://localhost/filter?categories=PRIVATE ==> ok

htt*://localhost/filter ==> ok

When using more than one category:

htt*://localhost/filter?categories=PRIVATE,BUSINESS

I'm getting the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} [select count(t) FROM foo.bar.services.trips.model.Trip t WHERE (:from IS NULL OR t.startTs >= :from) AND (:categories_0_, :categories_1_ IS NULL OR t.category IN (:categories_0_, :categories_1_)) ]

Anybody have an idea what I'm doing wrong here?

Maciej Kowalski · Accepted Answer · 2017-02-14 13:00:25Z

3

Try one of the following:

1) Try to enclose the statements involving the list in parentheses:

@Query("SELECT t "
        + "FROM Trip t "
        + "WHERE "
        + "(:from IS NULL OR t.startTs >= :from) "
        + "AND ((:categories IS NULL) OR (t.category IN :categories)) ")

2) Enclose the :categories in parentheses here

t.category IN (:categories)

answered Feb 14, 2017 at 13:00

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StephanM Over a year ago

Tried both but weren't working. But you pointed me to the right direction. This is what finally is working: AND ((:categories) IS NULL OR (t.category IN (:categories)). Thx!

Maciej Kowalski Over a year ago

Great! Glad i could help

Collectives™ on Stack Overflow

Spring Data (REST) & @Query: optional list as param

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related