日志如下:
2015-10-15 10:18:00,070 DEBUG - JDBC Connection [jdbc:mysql://localhost:3306/section?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver] will not be managed by Spring
2015-10-15 10:18:00,070 DEBUG - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76a2f910] was not registered for synchronization because synchronization is not active
2015-10-15 10:18:00,073 DEBUG - ==> Executing: update b_email_msg_remind SET send_status = ?, send_email_code='abc@abc.abc' where 1 = 1 AND email_remind_id = ? and send_status = 0 ; update b_email_msg_remind SET send_status = ?, send_email_code='abc@abc.abc' where 1 = 1 AND email_remind_id = ? and send_status = 0
2015-10-15 10:18:00,073 DEBUG - ==> Parameters: 1(Integer), 234746e8-4cab-444c-86ee-ea73c57cb7de(String), 1(Integer), 48d4a578-141e-421c-9eed-0c26de4b8f48(String)
2015-10-15 10:18:00,075 DEBUG - Closing no transactional SqlSession [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='abc@abc.abc'' 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 b_email_msg_remind
SET send_status = 1, send_email_code='abc@abc.abc'' 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='abc@abc.abc'' at line 6
Mapper.xml配置
<update id="updateEmailTasks" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" separator=";" > update b_email_msg_remind SET send_status = #{item.sendStatus}, send_email_code='abc@abc.abc' where email_remind_id = #{item.emailRemindId} and send_status = 0 </foreach> </update>
原因分析:
目前定位如果SET 只修改一个字段,则正常执行,或者SET 两个字段但是foreach只循环一次,也可以正常执行
由于时间较紧,暂时没有解决此问题,而是找了替代方法,并且效率更高
<update id="updateEmailTasks" parameterType="java.util.List"> update b_email_msg_remind <trim prefix="set" suffixOverrides=","> <trim prefix="send_status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when email_remind_id = #{item.emailRemindId} then #{item.sendStatus} </foreach> </trim> <trim prefix="send_time =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when email_remind_id = #{item.emailRemindId} then #{item.sendTime} </foreach> </trim> </trim> </update>这样配置生成的sql 是
update b_email_msg_remind set send_status =case when email_remind_id = ? then ? when email_remind_id = ? then ? end, send_email_code =case when email_remind_id = ? then 'abc@abc.abc' when email_remind_id = ? then 'abc@abc.abc' end
相关推荐
Mybatis框架下,数据库更新操作时只更新set过的字段,原1.0版本由于CSDN不支持修改已经删除了。
主要给大家介绍了关于mybatis拦截器实现通用权限字段添加的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用mybatis具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
mybatis批量添加的时候报错总结报错 parameter'_frch_item_0 not found
Mybatis批量foreach merge into的用法,这是介绍Mybatis批量foreach merge into的用法的文档
主要介绍了Mybatis批量更新报错的问题及解决办法,包括mybatis批量更新的两种方式,需要的的朋友参考下
Mybatis Plus 自定义批量插入或批量更新(根据唯一索引)
MyBatis防止批量更新1
只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢
mybatis 框架批量增删改代码 示例 含java与xml
Mybatis与JDBC批量插入MySQL数据库性能测试,资源包含文档、代码和数据库。
主要介绍了Mybatis批量更新三种方式的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
今天用到了mybatis的逆向工程,生成实体类和mapper文件,无奈数据库字段太多,逆向工程生成的字段都没有注释,每次需要了解字段时候都需要去数据库去看,很繁琐,于是就搜了一下如何在生成时候自动带上数据库的字段...
解决在springboot+mybatis+postgresql时,数据库字段类型为json时,如何与mybatis进行映射
MyBatis动态SQL,MyBatis批量插入(Oracle数据库)[收集].pdf
通过foreach实现ibatis的批量插入
使用方法请看博客https://blog.csdn.net/bandaotixiruiqiang/article/details/72478361#comments_12931827
Java实现Mybatis将数据批量插入到Oracle中
使用方法请看博客 https://blog.csdn.net/bandaotixiruiqiang/article/details/72478361#comments_12931827
mybatis 批量插入 嵌套select
主要介绍了mybatis执行批量更新batch update 的方法,提供oracle和mysql两种方法,非常不错,需要的朋友参考下