Java JDBC Batch

Java批量处理数据

import java.sql.Connection;
import java.sql.PreparedStatement;

//import

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();

 

Java进行批处理过程中，如果一次处理数据过多就会出现内存溢出错误。解决方法就是为批处理设置BatchSize。

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();
    }
}

// insert remaining records
ps.executeBatch(); 
ps.close(); 
connection.close();

 Spring Framework也有针对数据库Batch操作，摘抄Spring Framework文档片段

//Spring FrameWork JdbcTemplate
public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
      public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
      }

      //这里返回整数数组，在分批次执行一系列sql，每次都返回一次执行数据库操作影响到的数据条数.如果batch操作失败JDBC驱动则返回-2
      public int[] batchUpdate(final List<Actor> actors) {
          int[] updateCounts = jdbcTemplate.batchUpdate(
                  "update t_actor set first_name = ?, last_name = ? where id = ?",
                  new BatchPreparedStatementSetter() {
                      public void setValues(PreparedStatement ps, int i) throws SQLException {
                          ps.setString(1, actors.get(i).getFirstName());
                          ps.setString(2, actors.get(i).getLastName());
                          //longValue()
                          ps.setLong(3, actors.get(i).getId().longValue());
                      }
                      // 这里设置批处理池容量
                      public int getBatchSize() {
                          return actors.size();
                      }
                  } );
          return updateCounts;
      }

      //  ... additional methods
}

 

参考文章 http://viralpatel.net/blogs/batch-insert-in-java-jdbc/