【mybatis】mybatis批量更新

配置Mapper.xml

  <update id="updateBatch" parameterType="java.util.Map" >
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
      update t_user set user_name = #{item.name,jdbcType=VARCHAR},
      user_age = #{item.age,jdbcType=VARCHAR}
      where id = #{item.id,jdbcType=BIGINT}
    </foreach>
  </update>

注意

如果想要上面的配置生效，需要在YML中添加如下配置

• 在jdbcUrl中添加参数&allowMultiQueries=true 即： jdbc:mysql://127.0.0.1:3306/abc?characterEncoding=utf-8&allowMultiQueries=true

如果不按照注意事项处理，将会报如下错误

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update t_user set user_name = '02',
      user_age = '02'
      where id = '35' at line 5
### The error may involve com.viewhigh.group.ruleengine.dao.PrgAdrgMapper.updateBatch-Inline
### The error occurred while setting parameters
### SQL: update t_user set user_name = ?,       user_age = ?       where id = ?      ;        
update t_user set user_name = ?,       user_age = ?       where id = ?      ;        
update t_user set user_name = ?,       user_age = ?       where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update t_user set user_name = '02',
      user_age = '02'
      where id = '35' at line 5
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update t_user set user_name = '02',
      user_age = '02'
      where id = '35' at line 5