Using MyBatis Update with foreach

Question

i am struggling with update statement in MyBatis. I want to put multiple strings in one row:

UPDATE MY_FILTERS
SET
GROUPS =
  <foreach item="item" collection="selectedGroups" open="" separator="," close="">
    #{item}
  </foreach>,
TEMPLATES = 
  <foreach item="item" collection="selectedTemplates" open="" separator="," close="">
    #{item}
  </foreach>
where ID = #{id}

I've tried the following alternatives:

open = "'" close = "'"

or

separator = "','"    

but still getting exceptions like this:

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database. Cause: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification 
### The error may involve my.mybatis.PRPMapper.saveParamsToDB-Inline 
### The error occurred while setting parameters 
### SQL: UPDATE MY_FILTERS SET GROUPS = ? , ? , TEMPLATES = ? , ? where ID = ? 
### Cause: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification ; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification javax.faces.event.AbortProcessingException: org.springframework.jdbc.BadSqlGrammarException: 

Answer 1

try with

open = "'" close = "'"

and

separator = ","  

and

${item}

With these settings mybatis does not bind the variables but performs a String substitution. Be careful that string substittution is vulnerable to sql injection. If you face some more problems please post the updated sql query built by mystatis