SQLSyntaxErrorException for executing native SQL IN clause query in Spring boot JPA

Asked Sep 18 '20 at 08:19

Active Sep 18 '20 at 08:19

Viewed 109 times

I am using MySql Database. I like to write a native query on 'tbl_users'. I wrote the query like below:

@Query(nativeQuery = true, value = "SELECT * FROM tbl_users AS u WHERE u.id IN (:ids)")
List<User> findByUserIdIn(@Param("ids") Set<Long> ids);

But my spring application thrown an exception like

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')'

What will be the right query or how can I overcome this problem. Pls help.

asked Sep 18 '20 at 08:19

Subarata Talukder

5,407
2
34
50

1

Are you sending empty list or null in `ids`? `()` is not needed actually in query. – Eklavya Sep 18 '20 at 08:26
@Rono: list contains id values, so not empty or null. – Subarata Talukder Sep 18 '20 at 08:36
No need to create custom `findByUserIdIn` method. `JpaRepository` already contains `findAllById` : https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html#findAllById-java.lang.Iterable- – Oleksii Valuiskyi Sep 18 '20 at 09:59
Can't reproduce. Alternatively you can use `findByIdIn` method naming query – Eklavya Sep 18 '20 at 10:04

SQLSyntaxErrorException for executing native SQL IN clause query in Spring boot JPA

0 Answers0