Java往事之《批量插入数据》
批量插入数据
今天上午，一同事问我:批量插入数据怎么弄，只有遍历调用插入方法才能实现吗？那种方式呢，不言而喻对吧！肯定是可以实现的，但是呢？你插入1000条数据就遍历1000次，可以想象一下那种情景，是不是心里很慌？下面为大家介绍三种批量插入的方式。一、JDBC方式插入。二、MyBatis批量插入。三、iBatis批量插入。
一、JDBC方式插入。
int batchSize = 1000; //connection 先创建JDBC连接
PreparedStatement ps = connection.prepareStatement("insert into tb1 (c1,c2,c3...) values (?,?,?...)");//定义插入sql语句 for (int i = 0; i < list.size(); i++) { ps.setXXX(list.get(i).getC1()); ps.setYYY(list.get(i).getC2()); ps.setZZZ(list.get(i).getC3()); ps.addBatch();//mysql驱动内部在应用端会把多次addBatch()的参数合并成一条multi value的insert语句发送给db去执行 if ((i + 1) % batchSize == 0) { ps.executeBatch();//数据足够1000条了，我就做一次插入的操作 } } if (list.size() % batchSize != 0) { ps.executeBatch(); }
下面看一下完整的例子:
package cyl.demo.ipsearcher;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DbStoreHelper {
private String insert_sql;
private String charset;
private boolean debug;
private String connectStr;
private String username;
private String password;
public DbStoreHelper() {
connectStr = "jdbc:mysql://localhost:3306/db_ip";
// connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
insert_sql = "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)";
charset = "gbk";
debug = true;
username = "root";
password = "***";
}
public void storeToDb(String srcFile) throws IOException {
BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream(srcFile), charset));
try {
doStore(bfr);
} catch (Exception e) {
e.printStackTrace();
} finally {
bfr.close();
}
}
private void doStore(BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(connectStr, username,password);
conn.setAutoCommit(false); // 设置手动提交
int count = 0;
PreparedStatement psts = conn.prepareStatement(insert_sql);
String line = null;
while (null != (line = bfr.readLine())) {
String[] infos = line.split(";");
if (infos.length < 5) continue;
if (debug) {
System.out.println(line);
}
psts.setLong(1, Long.valueOf(infos[0]));
psts.setLong(2, Long.valueOf(infos[1]));
psts.setString(3, infos[2]);
psts.setString(4, infos[3]);
psts.setString(5, infos[4]);
psts.addBatch(); // 加入批量处理
count++;
}
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
System.out.println("All down : " + count);
conn.close();
}
}
执行完成以后：
[plain] view plain copy print?
All down : 103498
Convert finished.
All spend time/s : 47
一共10W+，执行时间一共花费 47 秒.
这个效率仍然不高，似乎没有达到想要的效果，需要进一步改进。
在MySQL JDBC连接字符串中还可以加入参数，
rewriteBatchedStatements=true，mysql默认关闭了batch处理，通过此参数进行打开，这个参数可以重写向数
useServerPrepStmts=false，如果不开启(useServerPrepStmts=false)，使用
com.mysql.jdbc.PreparedStatement进行本地SQL拼装，最后送到db上就是已经替换了?后的最终SQL.
在此稍加改进，连接字符串中加入下面语句（代码构造方法中去掉注释）：
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
再次执行如下：
[plain] view plain copy
print?
All down : 103498
Convert finished.
All spend time/s : 10
同样的数据量，这次执行只花费了10秒 ，处理效率大大提高.
二、MyBatis批量插入。
Java部分：
注意：这里循环的时候需new 出来新的对象，而不能通过循环改变属性的值就认为这是一个新的对象了,通俗的说就是new ReddemCode()要放在for循环的里面.
xxxMapper.xml
<!-- 批量插入生成的兑换码 -->
<insert id ="insertCodeBatch" parameterType="java.util.List" >
<selectKey resultType ="java.lang.Integer" keyProperty= "id"
order= "AFTER">
SELECT LAST_INSERT_ID()
</selectKey >
insert into redeem_code
(bach_id, code, type, facevalue,create_user,create_time)
values
<foreach collection ="list" item="reddemCode" index= "index" separator =",">
(
#{reddemCode.batchId}, #{reddemCode.code},
#{reddemCode.type},
#{reddemCode.facevalue},
#{reddemCode.createUser}, #{reddemCode.createTime}
)
</foreach >
</insert >
xxxMapper.java(部分)
int insertCodeBatch(List<ReddemCode > reddemCodeList);
对于foreach标签的解释参考了网上的资料，具体如下：
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。
foreach元素的属性主要有 item，index，collection，open，separator，close。
item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔 符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：
1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map
使用批量插入执行的SQL语句应该等价于：
insert into redeem_code (batch_id, code, type, facevalue,create_user,create_time)
values
(?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? )
三、iBatis批量插入。
同上MyBatis一样，换掉xxxMapper.xml部分就可以了
<insert id="batchInsert" parameterClass="java.util.List">
<![CDATA[
INSERT INTO wx_group (id,name,count,wxAccountId) values
]]>
<iterate conjunction=",">
<![CDATA[
(#wxg[].id#,#wxg[].name#,#wxg[].count#,#wxg[].wxAccountId#)
]]>
</iterate>
</insert>
