Spring jdbctemplate batch update fast alternative

3
votes

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. 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.

1
javamysqlspring
Do you have tried preparedStatement.addBatch(); preparedStatement.executeBatch(); ? - Xstian
Yes, no significant influence - lopushen
For batch updates to work, make sure you have a JDBC driver version (and database) that supports it, if it doesn't single queries will be executed instead of a batch. Also make sure you have proper transaction setup and are using transactional tables. - M. Deinum

1 Answers

2
votes

You can do this as follows

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