When executing multiple INSERT, DELETE, or UPDATE statements, mysql-connector-java supports two modes:
Serial execution: statements are sent one by one;
Batch execution: statements are grouped and sent in batches;
Batch processing mode groups statements into packets according to a batch size algorithm and sends them to the database server together, improving performance for large-scale operations. To enable it, append rewriteBatchedStatements=true to your JDBC URL:
@Override protectedlong[] executeBatchInternal() throws SQLException { synchronized (checkClosed().getConnectionMutex()) { // Connection must not be read-only if (this.connection.isReadOnly()) { thrownew SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT); } // Batch must contain at least one statement if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) { returnnewlong[0]; }
// we timeout the entire batch, not individual statements int batchTimeout = getTimeoutInMillis(); setTimeoutInMillis(0);
resetCancelledState();
try { statementBegins();
clearWarnings();
// 1. No plain SQL statements and batch rewrite is enabled // batchHasPlainStatements means plain SQL is present // rewriteBatchedStatements enables batch rewriting if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) { // 1.1 INSERT statements can be rewritten as multi-value inserts if (getParseInfo().canRewriteAsMultiValueInsertAtSqlLevel()) { // Execute batch insert return executeBatchedInserts(batchTimeout); }
// 1.2 DELETE/UPDATE statements, no plain SQL, and batch size > 3 if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null && this.query.getBatchedArgs().size() > 3) { // Execute batch delete or update return executePreparedBatchAsMultiStatement(batchTimeout); } }
// 2. Fall back to serial execution return executeBatchSerially(batchTimeout); } finally { this.query.getStatementExecuting().set(false);
clearBatch(); } } }
The source code makes the core requirement clear: MySQL batch processing needs statements that don’t contain raw SQL strings, and the connection must support batch rewriting. INSERT and DELETE/UPDATE use different rewriting rules — INSERT merges values clauses while DELETE/UPDATE concatenates with semicolons — so the INSERT batch process gets its own implementation.
A simple example of raw SQL that would prevent batch optimization:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
PreparedStatement preparedStatement = connection.prepareStatement(""); for (int i = 0; i < 10; i++) { StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO extenal_studentcj(grade,clazz,zkzh,NAME,scoretext,times) VALUES("); sql.append("'").append(i).append("',"); sql.append("'").append(i).append("',"); sql.append("'").append(i).append("',"); sql.append("'").append(i).append("',"); sql.append("'").append(i).append("',"); sql.append("'").append(i).append("'"); sql.append(");"); pst.addBatch(sql.toString()); }
timeoutTask = startQueryTimer(batchedStatement, batchTimeout); // Calculate how many batch rounds are needed numberToExecuteAsMultiValue = numBatchedArgs < numValuesPerBatch ? numBatchedArgs : numBatchedArgs / numValuesPerBatch;
// Calculate total statements to execute in full batches int numberArgsToExecute = numberToExecuteAsMultiValue * numValuesPerBatch;
// Fill in values and execute batch by batch for (int i = 0; i < numberArgsToExecute; i++) { // When a full batch is filled, execute it before starting the next batch if (i != 0 && i % numValuesPerBatch == 0) { try { updateCountRunningTotal += batchedStatement.executeLargeUpdate(); } catch (SQLException ex) { sqlEx = handleExceptionForBatch(batchCounter - 1, numValuesPerBatch, updateCounts, ex); }
// Handle any remaining statements that didn't fill a complete batch try { if (numValuesPerBatch > 0) { batchedStatement = prepareBatchedInsertSQL(locallyScopedConn, numValuesPerBatch);
if (timeoutTask != null) { timeoutTask.setQueryToCancel(batchedStatement); }
The INSERT batch process divides all statements into groups of a fixed batch size, executes each group, then handles any leftover statements that didn’t fill a complete batch in a final pass. For 100,000 student records, the flow looks like this:
protectedlong[] executePreparedBatchAsMultiStatement(int batchTimeout) throws SQLException { synchronized (checkClosed().getConnectionMutex()) { // This is kind of an abuse, but it gets the job done if (this.batchedValuesClause == null) { this.batchedValuesClause = ((PreparedQuery<?>) this.query).getOriginalSql() + ";"; }
timeoutTask = startQueryTimer((StatementImpl) batchedStatement, batchTimeout); // Calculate the number of batch rounds numberToExecuteAsMultiValue = numBatchedArgs < numValuesPerBatch ? numBatchedArgs : numBatchedArgs / numValuesPerBatch;
// Calculate total statements to execute in full batches int numberArgsToExecute = numberToExecuteAsMultiValue * numValuesPerBatch;
// Fill in values and execute batch by batch for (int i = 0; i < numberArgsToExecute; i++) { // When a full batch is filled, execute it before starting the next batch if (i != 0 && i % numValuesPerBatch == 0) { try { batchedStatement.execute(); } catch (SQLException ex) { sqlEx = handleExceptionForBatch(batchCounter, numValuesPerBatch, updateCounts, ex); }
if (timeoutTask != null) { // we need to check the cancel state now because we loose if after the following batchedStatement.close() ((JdbcPreparedStatement) batchedStatement).checkCancelTimeout(); } } finally { if (batchedStatement != null) { batchedStatement.close(); batchedStatement = null; } }
// Handle any remaining statements that didn't fill a complete batch try { if (numValuesPerBatch > 0) {
if (!multiQueriesEnabled) { ((NativeSession) locallyScopedConn.getSession()).disableMultiQueries(); }
clearBatch(); } } }
DELETE and UPDATE batching follows the same pattern as INSERT. The difference is in how statements get combined: DELETE and UPDATE statements are joined with semicolons, while INSERT statements merge their VALUES clauses. For 100,000 student records, the flow looks like this:
