mybatis批量更新update-设置多个字段值 报错 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

mybatis批量更新update-设置多个字段值

2016年08月01日 12:49:26 姚一号 阅读数:29539 标签: mysql mybatis批量更新批量更新allowMultiQuerie更多
个人分类: mybatis
 
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/mingliangniwo/article/details/52084648

mybatis由于简单易用性得到大家的认可和使用

但是在批量更新操作中,网上介绍的貌似不全,正好今天做个记录,大家一起进步

在实际项目开发过程中,常有这样的需求:根据ids更新表的某一个字段值,这时的sql语句是:

  1.  
    public interface IStaffDao {
  2.  
    void batchUpdate(@Param("list") List<Long> list);
  3.  
    }
  1.  
    <select id="getStaffsByIds" resultMap="staff_Mapper">
  2.  
    update staff set status = 0 where id in
  3.  
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
  4.  
    #{item}
  5.  
    </foreach>
  6.  
    ORDER BY id
  7.  
    </select>

还有一种情况:根据ids更新表的多个值,并且每个id对应的值也不一样,这时上述语句已经满足不了需求,需要另一种批量更新sql语句

  1.  
    public interface IStaffDao {
  2.  
    void batchUpdate(@Param("list") List<Staff> list);
  3.  
    }
  1.  
    <update id="batchUpdate" parameterType="java.util.List" >
  2.  
    <foreach collection="list" item="item" index="index" separator=";">
  3.  
    UPDATE staff set count = #{item.count} , code = #{item.code} , invalid_time = #{item.time} WHERE id = #{item.id}
  4.  
    </foreach>
  5.  
    </update>

由于这种批量更新是一次执行多个update语句,所以mybatis需要额外的配置:

在spring.datasource.url后加上allowMultiQueries=true
如:jdbc:mysql://10.10.20.36:3306/test?allowMultiQueries=true

否则,在执行sql语句时,会报下面的错误

  1.  
    [org.apache.ibatis.session.defaults.DefaultSqlSession@76a2f910]
  2.  
    org.springframework.jdbc.BadSqlGrammarException:
  3.  
    ### 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 b_email_msg_remind
  4.  
    SET send_status = 1, send_email_code='abc@abc.abc'' at line 6
  5.  
    ### The error may involve com.hhsoft.sectionservice.model.persistence.EmailMapper.updateEmailTasks-Inline
  6.  
    ### The error occurred while setting parameters
  7.  
    ### 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 staff
  8.  
    SET status = 1, send_email_code='abc@abc.abc';<span style="font-family: Helvetica, Tahoma, Arial, sans-serif;">update sta<span style="font-size:10px;">ff SET status = 2,</span> send_email_code='test@qq.com' </span>' at line 6
  9.  
    ; 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 b_email_msg_remind
  10.  
    SET send_status = 1, send_email_code='abc@abc.abc'' at line
上一篇:SpringMVC实现返回不同视图


下一篇:第九篇 Flask 中的蓝图(BluePrint)