Mybatis 批量更新 set 多个字段你的时候报错 mysql

日志如下：

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='[e-mail]' where 1 = 1 AND email_remind_id = ? and send_status = 0 ; update b_email_msg_remind SET send_status = ?, send_email_code='[e-mail]' 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='[e-mail]'' 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='[e-mail]'' 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='[e-mail]'' 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='[e-mail]'
			  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 '[e-mail]' when email_remind_id = ? then '[e-mail]' end 

 

评论

1 楼 静夜独窗 2016-08-01  

出现这个错误是因为mysql配量更新需要配置一下，在spring.datasource.url后加上allowMultiQueries=true
如：jdbc:mysql://10.10.20.36:3306/test?allowMultiQueries=true
ok,解决