Oracle 12c下JDBC批量插入原理及最优实现方式咨询

Great question about Oracle JDBC batch inserts—let's break this down clearly since there's a lot of nuance here with Oracle's driver behavior.

1. What Happens When You Call PreparedStatement.executeBatch()? #

Let's start with the under-the-hood mechanics for Oracle Database 12c and oracle.jdbc.driver.OracleDriver:

• Default behavior (no special config): When you add statements via addBatch() and call executeBatch(), the driver sends each individual INSERT to the database one at a time. It doesn't combine them into a single database call—this is just a convenience wrapper for executing multiple statements in sequence, not true "batch" processing in the efficient sense.
• With rewriteBatchedStatements=true: If you enable this connection property (add it to your JDBC URL like jdbc:oracle:thin:@//host:port/service?rewriteBatchedStatements=true), the driver rewrites all your batched INSERTs into a single statement with multiple VALUES clauses (e.g., INSERT INTO some_tab(id, val) VALUES (1, 'v1'), (2, 'v2'), ...). This reduces network round-trips drastically, which is where most performance gains come from.

When executeBatch() runs, it either:

• Sends each statement individually (default), or
• Sends one combined multi-value INSERT (with rewrite enabled), then returns an array of update counts for each original batch entry.

2. Does Each Batch Execute Only One INSERT Statement? #

It depends entirely on whether you've enabled rewriteBatchedStatements:

• Without rewrite: No. Each entry you added with addBatch() becomes a separate INSERT statement sent to the database during executeBatch(). So if you added 3000 entries, the driver sends 3000 separate INSERTs.
• With rewrite: Yes (sort of). The driver combines all batched entries into a single multi-value INSERT statement, so the database executes one statement that inserts all your rows at once.

3. Which Batch Execution Method Is Best? #

The approach you showed—preparing the PreparedStatement once outside the loop, then setting parameters and adding batches inside the loop—is absolutely the right foundation. Here's how to optimize it for maximum performance with Oracle:

Key Optimizations: #

• Enable rewriteBatchedStatements=true: This is non-negotiable for true batch efficiency with Oracle's driver.
• Turn off auto-commit: Setting conn.setAutoCommit(false) avoids committing after every single statement, which saves massive overhead from transaction log writes. Commit only after executing full batches.
• Use a reasonable batch size: Aim for 1000–5000 rows per batch (adjust based on row size—larger rows need smaller batches to avoid memory issues).
• Clean up batches after execution: Call clearBatch() after executeBatch() to free up resources.

Optimized Example Code: #

Connection conn = ...; // Initialize your connection with rewriteBatchedStatements=true
conn.setAutoCommit(false); // Disable auto-commit
String sql = "insert into some_tab(id, val) values (?, ?)";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    int batchSize = 1000;
    int rowCount = 0;

    for (int i = 0; i < 3000; i++) {
        ps.setLong(1, i);
        ps.setString(2, "value" + i);
        ps.addBatch();
        rowCount++;

        // Execute batch when we hit our target size
        if (rowCount % batchSize == 0) {
            ps.executeBatch();
            ps.clearBatch();
        }
    }

    // Execute any remaining rows that didn't fill a full batch
    if (rowCount % batchSize != 0) {
        ps.executeBatch();
    }

    conn.commit(); // Commit all batches at once
} catch (SQLException e) {
    conn.rollback(); // Roll back on failure
    e.printStackTrace();
}

Why This Is Better: #

• The PreparedStatement is pre-compiled once, avoiding repeated parsing of the same SQL by Oracle.
• Batches combined with rewrite minimize network round-trips.
• Disabling auto-commit reduces transaction overhead significantly.

内容的提问来源于stack exchange，提问作者Wicia