I have this mapper:

  <insert id="insertBatch" parameterType="java.util.Set">
    <foreach collection="filterParameterEntitySet" item="item" separator=";">
      INSERT INTO filter_parameter
      (
        filter_key,
        filter_value,
        filter_id
      )
      VALUES
      (
        #{item.filterKey},
        #{item.filterValue},
        #{item.filter.id}
      )
      ON CONFLICT DO NOTHING
    </foreach>
  </insert>

Whenever I execute it, it throws BadSQLGrammarException:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONFLICT DO NOTHING

** 

      ;
    INSERT INTO filter_parameter
    
        f' at line 13**

What could be the problem? I can't figure out where does the syntax error lie!

CodePudding user response：

Try this:

<insert id="insertBatch" parameterType="java.util.Set">
    INSERT INTO filter_parameter
      (
        filter_key,
        filter_value,
        filter_id
      ) VALUES
    <foreach collection="filterParameterEntitySet" item="item" separator=",">
      (
        #{item.filterKey},
        #{item.filterValue},
        #{item.filter.id}
      )
    </foreach>
    ON CONFLICT DO NOTHING
</insert>

CodePudding user response：

So basically, there were 2 mistakes in the query:

1. The separator, instead of this: separator=";" I should use this: separator=","
2. ON CONFLICT DO NOTHING is not valid in mysql and I was using mysql (it's for postgres), so I removed it and added the keyword IGNORE after INSERT to reserve the same functionality