mybatis 實現多條update同時執行
想在mapper的一個更新節點進行多條update語句的操作:
<update id="cleanUserByPhone" parameterType="java.lang.String"> update user set valid_status = 1 where mobile_phone = #{mobilePhone}; update user_account set valid_status = 1 where mobile_phone = #{mobilePhone} ; </update>
mybatis是默認不支持的,需要在數據庫配置中配置相關參數:
propertes 或者yml配置 文件中的jdbc後追加&allowMultiQueries=true
jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
補充:mybatis批量更新update-設置多個字段值
mybatis由於簡單易用性得到大傢的認可和使用
但是在批量更新操作中,網上介紹的貌似不全,正好今天做個記錄,大傢一起進步
在實際項目開發過程中,常有這樣的需求:根據ids更新表的某一個字段值,這時的sql語句是:
public interface IStaffDao { void batchUpdate(@Param("list") List<Long> list); }
<select id="getStaffsByIds" resultMap="staff_Mapper"> update staff set status = 0 where id in <foreach collection="list" item="item" index="index" open="(" separator="," close=")" > #{item} </foreach> ORDER BY id </select>
還有一種情況:根據ids更新表的多個值,並且每個id對應的值也不一樣,這時上述語句已經滿足不瞭需求,需要另一種批量更新sql語句
public interface IStaffDao { void batchUpdate(@Param("list") List<Staff> list); }
<update id="batchUpdate" parameterType="java.util.List" > <foreach collection="list" item="item" index="index" separator=";"> UPDATE staff set count = #{item.count} , code = #{item.code} , invalid_time = #{item.time} WHERE id = #{item.id} </foreach> </update>
由於這種批量更新是一次執行多個update語句,所以mybatis需要額外的配置:
在spring.datasource.url
後加上allowMultiQueries=true
如:jdbc:mysql://10.10.20.36:3306/test?allowMultiQueries=true
否則,在執行sql語句時,會報下面的錯誤
[org.apache.ibatis.session.defaults.DefaultSqlSession@76a2f910] 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 b_email_msg_remind SET send_status = 1, send_email_code='[email protected]'' at line 6 ### The error may involve com.hhsoft.sectionservice.model.persistence.EmailMapper.updateEmailTasks-Inline ### The error occurred while setting parameters ### 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 SET status = 1, send_email_code='[email protected]';<span style="font-family: Helvetica, Tahoma, Arial, sans-serif;">update sta<span style="font-size:10px;">ff SET status = 2,</span> send_email_code='[email protected]' </span>' at line 6 ; 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 SET send_status = 1, send_email_code='[email protected]'' at line 6
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- mybatis update更新字段的使用操作
- 解決mybatis批量更新出現SQL報錯問題
- Mybatis動態SQL foreach批量操作方法
- Mybatis 批量更新實體對象方式
- Mybatis如何使用ognl表達式實現動態sql