Incorrect Syntax near ',' in SQL query execution in Spring JPA but executes successfully in database

Question

I am using Java 8 and have an update query with 3 columns concatenated to one in the where clause. the query works perfectly in the SQL Server, but when I execute from the code, it results in Incorrect syntax error.

Query used in the code:

@Modifying
@Transactional
@Query(value = "UPDATE ListView SET stre_group = :streGroup, method = :mthodSelected, " +
                "alloc_schedule = :allocationSchedule " +
                "WHERE concat(job_id,':',item_nr,':',whse_nr) IN (:resultValue)", nativeQuery = true)
void updateWorklist(String streGroup, String mthodSelected,
                                    List<String> allocationSchedule ,
                                    List<String> resultValue);

DB:

UPDATE tablename  SET stre_group = 'store 4', method = 'store 4',
auto_alloc_action = 'store 4', alloc_schedule = 'store 4'
WHERE concat(job_id,':',item_nbr,':',whse_nbr) IN ('1:10:1')

I tried to check on the syntax and not finding the cause of the issue.

ERRO o.h.engine.jdbc.spi.SqlExceptionHelper : Incorrect syntax near ','.
? ERROR 29524 i.m.ca.service.updateDataService: Error occurred while updating records: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

Answer 1

Your problem is happening because one of the parameters being passed to the query has a List type and in the query you are using an equals sign =. The reason it is working in the database is because you are not using the exact same parameter in the application.

Essentially this part of the query: alloc_schedule = :allocationSchedule

Because the parameter is a List<String> allocationSchedule the final query being executed will be something like:

UPDATE ...., alloc_schedule = 'store 4', 'SomeOtherValueInTheList' WHERE ...

Hence the error: SqlExceptionHelper : Incorrect syntax near ','.

So you need to fix the parameter so it is a plain String (and obviously the callers of this method)