Java JDBC批处理插入数据操作

来源：互联网发布：ai作图软件编辑：程序博客网时间：2024/05/16 09:48

原文：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 spacecom.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 recordsps.close();connection.close();

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

1 0