Spring JDBCTemplate批处理更新快速替代

问题描述

我正尝试使用Spring JDBC模板批量更新将大约5000个值插入MySql表中，如下所示

I am trying to insert around 5000 values into the MySql table using Spring JDBC template batch update like it is shown here

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

据我了解，它在一次事务中执行的插入次数与行数一样多.但是它仍然很慢.

As I understand it is doing as many inserts as many rows I am giving it in one transaction. But it is still slow.

我尝试过形成类似的查询

I've tried forming a query like

INSERT INTO CUSTOMER " +
    "(CUST_ID, NAME, AGE) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?),(?, ?, ?)....

我有尽可能多的行.它的执行速度更快，但我必须手动形成查询.我想知道在这种情况下是否有其他替代方法可以批量更新?

for as many rows I have. It performed much faster but I had to form the query manually. I wonder are there any alternatives for batch update for such cases?

P.S.我知道一个人应该考虑最大的程序包大小，在构建这么大的查询时，查询的大小不应超过限制(尽管可以在MySql服务器中配置限制).

P.S. I know that one should consider maximum package size, the query's size should not exceed the limit(though the limit can be configured in MySql server) when building such big queries.

推荐答案

您可以按照以下步骤进行操作

You can do this as follows

1. 获取连接对象
2. 将连接自动提交属性设置为false.使用connection.autocommit(false)
3. 运行您的插入查询语句.
4. 执行connection.commit();

1. Get connection object
2. set connections autocommit property to false. using connection.autocommit(false)
3. Run your insert query statement.
4. execute connection.commit();

这篇关于Spring JDBCTemplate批处理更新快速替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！