batchUpdate inserts one row at a time on MySQL
jdbcTemplate.batchUpdate() takes seconds for a few thousand rowsShort answer
By default MySQL Connector/J sends each statement of a JDBC batch separately, so batchUpdate costs one round trip per row. Add rewriteBatchedStatements=true to the JDBC URL and the driver rewrites batched INSERT statements into multi-row inserts. Also give Spring a sensible batch size.
Fix it fast
1Find out which cause you have
Print the JDBC URL your app really uses and look for the property. Then count the INSERT statements the server receives for a batch of 1000 rows.
grep -rn "jdbc:mysql" src/main/resources/2Match what you see, then jump to its fix
- 1
The URL has no rewriteBatchedStatements parameterrewriteBatchedStatements is still falseFix - 2
The property is on but UPDATE batches stay slowThe statements are UPDATE or mixed, not INSERTFix - 3
Memory grows during the call, or the server rejects the packet as too largeOne huge batch instead of sized chunksFix
- 1
What does the error look like?
jdbcTemplate.batchUpdate() takes seconds for a few thousand rowsMySQL general log shows one INSERT per row, not one multi-row INSERT
Cause 1 of 3
rewriteBatchedStatements is still false
1Check
The property is off by default. With it off, executeBatch() does not combine the rows.
SET GLOBAL general_log = 'ON';
-- run the batch, then count INSERT lines in the log, then switch it off again
SET GLOBAL general_log = 'OFF';2Fix
Turn it on in the connection URL. Then the driver can use multi-queries and rewrite batched prepared INSERT and REPLACE statements into multi-values statements when executeBatch() is called.
# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/app?rewriteBatchedStatements=true3Verify
Rerun the batch and time it; the general log now shows far fewer INSERT statements.
SELECT COUNT(*) FROM my_table;Confirmed by dev.mysql.com
Cause 2 of 3
The statements are UPDATE or mixed, not INSERT
1Check
The documentation names INSERT and REPLACE prepared statements for rewriting into multi-values form. Other statement types go as multi-queries at best, so gains are smaller.
grep -rn "batchUpdate" src/main/java/2Fix
For big updates, load the new values into a staging table with a batched INSERT, then run one set-based UPDATE ... JOIN.
INSERT INTO price_stage (id, price) VALUES (?, ?);
UPDATE product p JOIN price_stage s ON s.id = p.id SET p.price = s.price;3Verify
Compare run time of the old loop and the two-step version on the same data.
SELECT COUNT(*) FROM price_stage;Confirmed by dev.mysql.com
Cause 3 of 3
One huge batch instead of sized chunks
1Check
The overload with a batchSize argument splits a collection into several JDBC batches, and returns int[][]. The plain overload sends the whole list as one batch.
grep -rn "batchUpdate(" src/main/java/2Fix
Use the overload that takes a batch size and a ParameterizedPreparedStatementSetter, with a few hundred to a few thousand rows per chunk.
jdbcTemplate.batchUpdate(
"INSERT INTO account (id, name) VALUES (?, ?)",
accounts, 1000,
(ps, a) -> { ps.setLong(1, a.id()); ps.setString(2, a.name()); });3Verify
Watch heap use while the batch runs; it should stay flat.
jcmd <pid> GC.heap_infoConfirmed by docs.spring.io
How do I stop it coming back?
- Put rewriteBatchedStatements=true in the base datasource URL of every service that bulk-writes to MySQL.
- Remember the documented side effects: with ON DUPLICATE KEY UPDATE you get SUCCESS_NO_INFO instead of per-row counts.
- Never build plain Statement batches from user input with this flag on; the docs warn about SQL injection.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
EntityNotFoundException when loading an entity that has a missing related row
ConstraintViolationException on an orphaned foreign key
EntityNotFoundException means Hibernate followed an id and no row was there.
3 causes, each with a fixDatabases
Hibernate fails on a CREATE or ALTER TABLE statement at startup
Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement
Hibernate generated a CREATE, ALTER or DROP statement and MySQL refused it.
3 causes, each with a fixDatabases
MySQL Connector/J: EOFException, expected to read 4 bytes, read 0 bytes
Communications link failure
The MySQL server or something between you and it closed the connection, and the driver found out only when it tried to use it.
3 causes, each with a fixDatabases
Java cannot log in to MySQL 8: authentication plugin caching_sha2_password not supported
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
MySQL 8 accounts use the caching_sha2_password plugin by default, and Connector/J releases before 8.0.9 cannot use it.
3 causes, each with a fixDatabases
MySQL refuses to delete a row because other rows point to it
SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails
Error 1451 means at least one child row still references the parent row you are deleting or changing.
3 causes, each with a fixDatabases
JDBC cannot talk to MySQL: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago.
Communications link failure means the TCP link to MySQL could not be opened or was closed.
3 causes, each with a fix