Java 为什么 spring jdbcTemplate batchUpdate 逐行插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39576061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
why spring jdbcTemplate batchUpdate insert row by row
提问by Ensom Hodder
I have 200K rows to be inserted in one single database table. I tried to use jdbcTemplate.batchUpdatein 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 ?
我有 200K 行要插入到一个数据库表中。我尝试jdbcTemplate.batchUpdate在春季使用，以便每批次插入 10,000 个。但是，此过程消耗太多时间（20 万行需要 7 分钟）。所以在数据库方面，我检查了插入的行数select count(*) from table_X。我发现行数比预期的 10K 略有增加。任何人都可以解释是什么原因还是应该在数据库端配置的东西？
PS: I am using sybase ....
PS：我正在使用 sybase ....
回答by Sheetal Mohan Sharma
Try setting below for connection string - useServerPrepStmts=false&rewriteBatchedStatements=true. Have not tried but its from my bookmarks. You can search on these lines..
尝试在下面设置连接字符串 - useServerPrepStmts=false&rewriteBatchedStatements=true。没试过，但它来自我的书签。您可以在这些行上搜索..
Connection c = DriverManager.getConnection("jdbc:<db>://host:<port>/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password");
回答by Sanka
There are lot of approaches available on the web. Performance directly depends on the
网络上有很多可用的方法。性能直接取决于
- Code you have written
- JDBC driver you are using
- database server and number of connection you are using
- table indexes leads to slowness for insertion
- 你写的代码
- 您正在使用的 JDBC 驱动程序
- 您使用的数据库服务器和连接数
- 表索引导致插入缓慢
Without looking at your code anyone can guess, but no one can find the exact solution.
不看你的代码，任何人都可以猜到，但没有人能找到确切的解决方案。
Approach 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();
}
});
}
reference
参考
https://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/
https://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/
http://docs.spring.io/spring-framework/docs/3.0.0.M4/reference/html/ch12s04.html
http://docs.spring.io/spring-framework/docs/3.0.0.M4/reference/html/ch12s04.html
Approach 2.1
方法 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);
}
Alternatively, you can execute the SQL directly.
或者，您可以直接执行 SQL。
//insert batch example with SQL
public void insertBatchSQL(final String sql){
getJdbcTemplate().batchUpdate(new String[]{sql});
}
reference
参考
https://www.mkyong.com/spring/spring-simplejdbctemplate-batchupdate-example/
https://www.mkyong.com/spring/spring-simplejdbctemplate-batchupdate-example/
Approach 2.2
方法 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
}
Approach 2.3
方法 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
}
Approach 3 :JDBC
方法三：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();
reference
参考
https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
/*Happy Coding*/
