CODEWITHSUNDEEP
javaspring-dataspring-data-jpajpqlin-clause
amateur
amateur

Reputation: 49

IN-clause error with Spring Data JPA @Query annotation

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?

Upvotes: 2

Views: 3758

Answers (2)

Ivan Lomba
Ivan Lomba

Reputation: 60

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)

Upvotes: 3

Arun
Arun

Reputation: 2342

Did you try like this:

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

Upvotes: 4

Related Questions