MyBatis Batch update update-set multiple field values

MyBatis is recognized and used by everyone because of its simplicity and ease of use.

But in the batch update operation, the online introduction of seemingly incomplete, just do a record today, we progress together

In the actual project development process, there is often such a requirement: According to the IDs update the table of a field value, the SQL statement is:

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 C ollection= "List" item= "item" index= "Index" open= "(" separator= "," close= ")" >
		#{item}
	</foreach> ORDER by
	ID
</select>

There is also a situation: based on IDs to update multiple values of the table, and each ID corresponding to the value is not the same, then the above statement has not met the requirements, need another batch update SQL statement

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}< C2/></foreach>

Because this batch update executes multiple UPDATE statements at a time, the MyBatis requires additional configuration:

After the Spring.datasource.url, add allowmultiqueries=true.
such as: Jdbc:mysql://10.10.20.36:3306/test?allowmultiqueries=true

Otherwise, the following error is reported when executing the SQL statement

[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 ' 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= ' 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; Bad SQL Grammar []; Nested exception is Com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have a 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