IN-clause error with Spring Data JPA @Query annotation

Question

I'm getting an IN OUT error when using IN-clause with Spring Data @Query.

Query:

@Query("SELECT m FROM message m WHERE m.user = :user AND m.tags IN :tags")
List<Message> findByUserAndTags(@Param("user") User user, @Param("tags") List<Tag> tags);

Error:

Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2

IN-clause works perfectly when using Spring Data automatic queries:

List<Message> findByUserAndTagsIn(User user, List<Tag> tags);

I'm missing something when using @Query annotation?

Answer 1

You can solve it using a LEFT JOIN:

@Query("SELECT m FROM message m LEFT JOIN m.tags t
        WHERE m.user = :user 
        AND t IN :tags")
List<Message> findByUserAndTags(@Param("user") User user, 
        @Param("tags") List<Tag> tags)