Java JDBC批处理插入数据操作

原文：http://www.cnblogs.com/kahreman/archive/2012/08/06/2625827.html
方法一：

String[] queries = { 
        "insert into employee (name, city, phone) values ('A', 'X', '123')",
        "insert into employee (name, city, phone) values ('B', 'Y', '234')",
        "insert into employee (name, city, phone) values ('C', 'Z', '345')"};
Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
 for (String query : queries) {
    statemenet.addBatch(query);
}
statemenet.executeBatch();
statemenet.close();
connection.close();

使用addBatch方法添加SQL，使用executeBatch一次性执行批量插入操作。

方法二：
当插入的数据是动态的，需要用PreparedStatement对象实现功能。

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
 for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
}
ps.executeBatch();
ps.close();
connection.close();

操作类似Statement，只是增加了设置插入值的功能。

方法三：
上面的解决方案仍然存在一个问题。考虑这样一个场景，在您想要插入到数据库使用批处理上万条记录。嗯，可能产生的OutOfMemoryError：

java.lang.OutOfMemoryError: Java heap space
com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)

分批次执行批量插入操作

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
final int batchSize = 1000;
int count = 0;
for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
    if(++count % batchSize == 0) {
        ps.executeBatch();
    }
}
ps.executeBatch(); // insert remaining records
ps.close();
connection.close();

考虑批量大小为1000，每1000个查询语句为一批插入提交。