为什么spring jdbcTemplate batchUpdate逐行插入 [英] why spring jdbcTemplate batchUpdate insert row by row

问题描述

我有200K行要插入一个数据库表中。我试图在spring中使用 jdbcTemplate.batchUpdate ，以便每批次插入10,000个。但是，此过程会消耗太多时间（对于200K行，为7分钟）。所以在数据库方面，我检查由table_X 中的 select count（*）插入的行数。我发现行数略有增加，预计为10K。任何人都可以解释是什么原因或者是应该在数据库端配置的东西吗？

I have 200K rows to be inserted in one single database table. I tried to use jdbcTemplate.batchUpdate in spring in order to do insertion 10,000 per batch. However, this process consumes too much time (7 mins for 200K rows). So on database side, I check the number of rows inserted by select count(*) from table_X. I found the number of rows increased slightly instaed of 10K expected. Can anyone explain what's reason or is it something which should be configurated on Database side ?

PS：我正在使用sybase ....

PS: I am using sybase ....

推荐答案

网络上有很多方法可供使用。
性能直接取决于

There are lot of approaches available in web. Performance directly depend on the

1. 您编写的代码


2. 您是JDBC驱动程序使用


3. 数据库服务器和您正在使用的连接数


4. 表索引导致插入缓慢

1. Code you have written
2. JDBC driver you are using
3. database server and number of connection you are using
4. table indexes leads to slowness for insertion

如果不看任何人可以猜到的代码，但没有人能找到确切的解决方案。

Without looking at your code anyone can guess, But Nobody can find the exact solution.

方法1

//insert batch example
public void insertBatch(final List<Customer> customers){

  String sql = "INSERT INTO CUSTOMER " +
    "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        Customer customer = customers.get(i);
        ps.setLong(1, customer.getCustId());
        ps.setString(2, customer.getName());
        ps.setInt(3, customer.getAge() );
    }

    @Override
    public int getBatchSize() {
        return customers.size();
    }
  });
}

参考

https://www.mkyong.com/spring/spring-jdbctemplate-batchupdate -example /

http://docs.spring.io/spring-framework/docs/3.0.0.M4/reference/html/ch12s04.html

方法2.1

//insert batch example
public void insertBatch(final List<Customer> customers){
    String sql = "INSERT INTO CUSTOMER " +
        "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

    List<Object[]> parameters = new ArrayList<Object[]>();

    for (Customer cust : customers) {
        parameters.add(new Object[] {cust.getCustId(),
            cust.getName(), cust.getAge()}
        );
    }
    getSimpleJdbcTemplate().batchUpdate(sql, parameters);
}

或者，您可以直接执行SQL。

Alternatively, you can execute the SQL directly.

//insert batch example with SQL
public void insertBatchSQL(final String sql){

    getJdbcTemplate().batchUpdate(new String[]{sql});

}

推荐

https://www.mkyong.com/spring/spring -simplejdbctemplate-batchupdate-example /

方法2.2

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
            "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
            batch);
        return updateCounts;
    }

    //  ... additional methods
}

方法2.3

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[] {
                    actor.getFirstName(),
                    actor.getLastName(),
                    actor.getId()};
            batch.add(values);
        }
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
        return updateCounts;
    }

    //  ... additional methods
}

方法3：JDBC

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();

参考

https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-更新/

/*Happy Coding*/

这篇关于为什么spring jdbcTemplate batchUpdate逐行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！