参考链接:https://my.oschina.net/zhuguowei/blog/411853
https://www.cnblogs.com/shihaiming/p/10341241.html
1, 批量更新xml书写demo
<!-- 批量更新,通过接收传进来的参数list进行循环着组装sql -->
<update id="batchUpdate" parameterType="java.util.List" >
<!-- 接收list参数,循环着组装sql语句,注意for循环的写法
separator=";" 代表着每次循环完,在sql后面放一个分号
item="record" 循环List的每条的结果集-->
<foreach collection="list" item="record" separator=";">
update turn_over_record set
status=#{record.status, jdbcType=VARCHAR},
complete_time=#{record.completeTime}
where turn_over_id=#{record.turnOverId,jdbcType=BIGINT}
</foreach>
</update>
1.1, 解释:
这种写法,相当于循环传过来的列表,循环出 N 条sql语句,用 ; 分割,
注意,这种写法,需要在db链接url后面带一个参数 &allowMultiQueries=true,否在会报一些非常奇怪的错误,com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException,像这种,如果没大神指点,真是,,可太惨了。
2. 一个mysql jdbc待解之谜 关于jdbc url参数 allowMultiQueries
如下的一个普通JDBC示例:
String user ="root";
String password = "root";
String url = "jdbc:mysql://localhost:3306";
Connection conn = java.sql.DriverManager.getConnection(url , user, password);
Statement stmt = conn.createStatement();
String sql = "select 'hello';select 'world'";
stmt.execute(sql);
执行时会报错:
om.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 'select 'world'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
若一个sql中通过分号分割(或包含)了多个独立sql的话,如:
select 'hello';select 'world'
默认就会报上述错误,当若显式设置allowMultiQueries为true的话,就可以正常执行不会报错.如下所示:
String url = "jdbc:mysql://localhost:3306?allowMultiQueries=true";
官方文档解释:
allowMultiQueries
Allow the use of ';' to delimit multiple queries during one statement (true/false), defaults to 'false', and does not affect the addBatch() and executeBatch() methods, which instead rely on rewriteBatchStatements.
Default: false
Since version: 3.1.1