jdbcmysql设置rewriteBatchedStatements参数实现⾼性能批量处。。。⼀、摘要
利⽤jdbc预处理uteBatch可实现sql批处理，但是数据库层⾯是否真正实现批处理，不同数据库表现不⼀。以mysql为例，只有jdbcUrl设置了rewriteBatchedStatements=true参数，mysql驱动才会真正执⾏sql批处理，从⽽显著提⾼性能。但是⼀旦设置rewriteBatchedStatements=true后，uteBatch()的返回结果也会发⽣变化，为此代码需要特殊处理。
⼆、有⽆参数rewriteBatchedStatements⽐较
1、数据表 jdbc_student
CREATE TABLE`jdbc_student`(
`id`int(20)NOT NULL AUTO_INCREMENT,
`name`varchar(100)DEFAULT NULL,
`age`int(11)DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
2、没有rewriteBatchedStatements=true参数
java代码
public static void main(String[] args){
Connection conn = null;
try{
Class.forName("sql.jdbc.Driver").newInstance();
String jdbcUrl ="jdbc:mysql://ip:3306/dbname?useUnicode=true&characterEncoding=utf-8";
conn = Connection(jdbcUrl,"user","pwd");
int batchSize =5000;
int count =0;
conn.setAutoCommit(false);//设置⾃动提交为false
PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
for(int i =1; i <= batchSize; i++){
ps.setString(1,"name: "+ i);//设置第2个参数, name
ps.setInt(2, i %30+10);//设置第3个参数, age
ps.addBatch();//将该条记录添加到批处理中
}
Long t1 = System.currentTimeMillis();
System.out.println("开始执⾏: "+ t1);
int rows[]= ps.executeBatch();
for(int row : rows){
count += row;
}
connmit();//提交
ps.close();
Long t2 = System.currentTimeMillis();
System.out.println("执⾏结束："+ t2 +", 耗时："+(t2-t1)/1000+"秒, batchRows: "+batchSize+"条, affectedRows: "+ count);
if(conn!=null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
if(conn!=null){
try{
conn.close();
}catch(Exception ee){
//TODO
}
}
}
}
输出结果
开始执⾏: 1540629124028批量更新sql语句
执⾏结束: 1540629189490, 耗时：65秒, batchRows: 5000条, affectedRows: 5000
3、设置rewriteBatchedStatements=true参数
java代码
public static void main(String[] args){
Connection conn = null;
try{
Class.forName("sql.jdbc.Driver").newInstance();
String jdbcUrl ="jdbc:mysql://ip:3306/dbname?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8";  conn = Connection(jdbcUrl,"user","pwd");
int batchSize =5000;
int count =0;
conn.setAutoCommit(false);//设置⾃动提交为false
PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
for(int i =1; i <= batchSize; i++){
ps.setString(1,"name: "+ i);//设置第2个参数, name
ps.setInt(2, i %30+10);//设置第3个参数, age
ps.addBatch();//将该条记录添加到批处理中
}
Long t1 = System.currentTimeMillis();
System.out.println("开始执⾏: "+ t1);
int rows[]= ps.executeBatch();
for(int row : rows){
count += row;
}
connmit();//提交
ps.close();
Long t2 = System.currentTimeMillis();
System.out.println("执⾏结束："+ t2 +", 耗时："+(t2-t1)/1000+"秒, batchRows: "+batchSize+"条, affectedRows: "+ count);
if(conn!=null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
if(conn!=null){
try{
conn.close();
}catch(Exception ee){
//TODO
}
}
}
}
输出结果
开始执⾏: 1540629414786
执⾏结束: 1540629414931, 耗时：0秒, batchRows: 5000条, affectedRows: 25000000
三、executeBatch返回值处理
1、有⽆rewriteBatchedStatements参数，两者执⾏性能真是天壤之别：
⽆rewriteBatchedStatements参数，执⾏时间：65秒
有rewriteBatchedStatements参数，执⾏时间：不到1秒
2、executeBatch返回值：
⽆rewriteBatchedStatements参数，返回值：int[]累加，5000
有rewriteBatchedStatements参数，返回值：int[]累加，25000000
3、有rewriteBatchedStatements参数，executeBatch返回值解释：
1）设置rewriteBatchedStatements参数后，可以简单理解为数据库将5000条insert-sql语句打包成⼀条sql语句，执⾏这条sql语句返回总影响⾏数5000, 如：
INSERT INTO jdbc_student VALUES (‘55000’, ‘name: 1’, ‘11’), (‘55001’, ‘name: 1’, ‘11’), (‘55002’,
‘name: 2’, ‘12’),;
2）但是驱动executeBatch返回的是每条sql语句对应的影响记录数数组，即[5000,5000,5000,…,5000]，所以累计就是5000*5000 = 25000000
3）update, delete, 返回的仍然是[1,1,1…1]⽆需特殊处理。
4、executeBatch返回值应⽤
以销售模块更新订单主细表为例，假设订单细表有10条⾏项⽬，分2张出库单出库，分别有不同⼈员操作（谁先操作不确定）：
1）第⼀张出库单出库4个订单⾏项⽬，出库后
订单细表⾏项⽬状态：已调度->已出库
订单主表状态：已调度->(⾏项⽬是否全部出库 ? ‘已出库’ : ‘部分出库’)
2）第⼆张出库单出库6个订单⾏项⽬，出库后
订单细表⾏项⽬状态：已调度->已出库
订单主表状态：已调度->(⾏项⽬是否全部出库 ? ‘已出库’ : ‘部分出库’)
sql批处理的封装，以java代码为例，假设调⽤sql批处理代码封装如下：
public int executeBatch(String sql, List<Map> paras){
//预处理sql：insert into jdbc_student (name, age) values (?,?)
//paras，参数集合，[{name:"name1",age:11},{name:"name2",age:12},...]
//返回影响记录条数
}
更新订单主细表状态代码逻辑：
开启事务
更新订单细表(状态, 操作时间, 操作⼈, 出库数量等): 已调度->已出库，jdbc批处理实现，判断executeBatch(sql, paras) == paras.size()
查询细表是否全部已出库？并更新主表状态
提交事务
以出库单1为例：
没rewriteBatchedStatements参数，executeBatch(sql, paras) == paras.size() == 4
有rewriteBatchedStatements参数，executeBatch(sql, paras) = 16，paras.size() = 4，两者不等，程序报错！
实际业务中，通常paras.size即executeBatch返回结果，因此判断是否相等修改如下即可：
executeBatch(sql, paras) == paras.size() || executeBatch(sql, paras) == paras.size() * paras.size()