Spring JDBC Batch Inserts: Boost Bulk Operation Performance

Let me paint a picture: It's 3 AM, and you're on call for a fintech startup processing loan applications. The system is ingesting a nightly batch of 50,000 transactions, but it's taking over an hour. The database CPU is pegged, and the application logs show thousands of individual INSERT statements flying by. You've seen this before—the dreaded N+1 insert problem. Your boss is breathing down your neck, and you need to fix it now.

If you've ever faced a scenario where you need to insert thousands of records into a database, you know the pain of slow performance. Each insert involves network round-trips, transaction overhead, and statement parsing. Spring JDBC's batch insert support is designed to solve this by grouping multiple inserts into a single database round-trip. But here's the thing: many developers think they're doing batch inserts when they're really not. The default behavior in many JDBC drivers is to send inserts one by one, just wrapped in a loop. You have to explicitly enable batching, and even then, there are pitfalls.

In this article, I'll show you how to properly implement batch inserts with Spring JDBC, what the official docs gloss over, and how to avoid the mistakes I've seen cost teams hours of debugging. We'll use a realistic payment processing domain—because nobody cares about another User/Order example. By the end, you'll be able to batch-insert 100,000 records in seconds, not hours.

Why Single Inserts Kill Performance

Let's start with the obvious: inserting one row at a time is terrible for performance. Each insert requires a network round-trip, statement parsing, transaction overhead, and logging. If you're inserting 100,000 records, you're making 100,000 round-trips. Even with a fast network, that adds up.

I once worked with a team that used a loop with JdbcTemplate.update() for each record. They thought it was fine because the database was fast. But when data volume grew from 10,000 to 500,000 records, the job went from 5 minutes to 2 hours. The fix was simple: batch inserts.

Batch inserts work by grouping multiple INSERT statements into a single network call. The database receives them as a batch and executes them together, often with a single parse and commit. This can reduce overhead by orders of magnitude.

Here's a concrete example. Suppose we have a payment table with columns id, amount, currency, status. The naive approach:

``java for (Payment payment : payments) { jdbcTemplate.update("INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)", payment.amount(), payment.currency(), payment.status()); } ``

This sends one INSERT per payment. With batch, we do:

``java jdbcTemplate.batchUpdate("INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Payment p = payments.get(i); ps.setBigDecimal(1, p.amount()); ps.setString(2, p.currency()); ps.setString(3, p.status()); } @Override public int getBatchSize() { return payments.size(); } }); ``

But wait—this might still send individual inserts if your JDBC driver doesn't support batching. That's the gotcha.

// Naive single insert - SLOW
for (Payment payment : payments) {
    jdbcTemplate.update("INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)",
        payment.amount(), payment.currency(), payment.status());
}

// Proper batch insert - FAST (with driver support)
jdbcTemplate.batchUpdate("INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)",
    new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Payment p = payments.get(i);
            ps.setBigDecimal(1, p.amount());
            ps.setString(2, p.currency());
            ps.setString(3, p.status());
        }
        @Override
        public int getBatchSize() {
            return payments.size();
        }
    });

Configuring JDBC Driver for Real Batching

Here's the hard truth: most JDBC drivers don't batch inserts by default. MySQL, for example, sends each INSERT separately unless you set rewriteBatchedInserts=true in the connection URL. PostgreSQL has a similar parameter: reWriteBatchedInserts=true. Oracle? It's more complex—you need to use batch update with Oracle's proprietary batching.

Let's focus on MySQL since it's common. The magic parameter is:

jdbc:mysql://localhost:3306/payments?rewriteBatchedInserts=true

What does this do? It tells the MySQL driver to rewrite batched INSERTs into a single multi-value INSERT statement. Instead of sending:

INSERT INTO payment VALUES (...); INSERT INTO payment VALUES (...); ...

It sends:

INSERT INTO payment VALUES (...), (...), (...);

This is significantly faster because the database parses the statement once and executes it as a single operation.

But there's a catch: this only works if your batch size is reasonable. If you batch 100,000 records at once, the multi-value INSERT string becomes huge, potentially causing memory issues on both client and server. The sweet spot is usually between 50 and 500 records per batch.

Here's how to configure it in Spring Boot's application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/payments?rewriteBatchedInserts=true

And in your code, you can use a NamedParameterJdbcTemplate for cleaner code:

``java String sql = "INSERT INTO payment (amount, currency, status) VALUES (:amount, :currency, :status)"; SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(payments.toArray()); namedJdbcTemplate.batchUpdate(sql, batch); ``

This internally uses PreparedStatement batching, and with rewriteBatchedInserts=true, it becomes a multi-value INSERT.

# MySQL with batch insert optimization
spring.datasource.url=jdbc:mysql://localhost:3306/payments?rewriteBatchedInserts=true&useServerPrepStmts=false
spring.datasource.username=root
spring.datasource.password=secret
spring.datasource.hikari.maximum-pool-size=10

What the Official Docs Won't Tell You

Spring's official documentation on JdbcTemplate.batchUpdate() is accurate but incomplete. Here are the gotchas I've learned the hard way:

1. Batch size matters more than you think. The official docs show examples with a list of all records, but in production, you can't load millions of records into memory. You need to paginate your input and flush batches periodically. A common pattern is to stream from a file or database cursor and commit every N records.
2. Transaction boundaries are critical. If you wrap a huge batch in a single transaction, you risk locking the table for a long time and causing deadlocks. Also, if the transaction fails mid-way, you lose all work. Instead, use smaller transactions per batch.
3. Driver compatibility is a minefield. MySQL's rewriteBatchedInserts works with simple INSERTs, but if you have ON DUPLICATE KEY UPDATE or other clauses, it may not rewrite them. Test thoroughly.
4. PreparedStatement caching can interfere. Some connection pools cache PreparedStatements. If you reuse the same SQL, the batch might use a cached statement that doesn't have the batch enabled. Disable statement caching or flush it between batches.
5. Batch inserts with auto-generated keys are tricky. If you need to retrieve generated keys (e.g., auto-increment IDs), you must use the overloaded batchUpdate method that returns int[]. But even then, some drivers don't support returning keys for batch inserts. MySQL does, but it's slower. Consider using a sequence or UUID instead.
6. Not all databases benefit equally. SQL Server, for example, has its own batching mechanism via Table-Valued Parameters (TVPs). Spring doesn't support TVPs natively; you'd need to use SqlParameterSource with a custom type. For high-volume inserts, TVPs can be faster than batched INSERTs.

Here's a production example: I was inserting 1 million payment records into PostgreSQL. Using reWriteBatchedInserts=true with batch size 500, it took 30 seconds. Without it, it took 8 minutes. The difference was entirely in the driver parameter.

// Streaming paginated batch insert
int batchSize = 500;
List<Payment> batch = new ArrayList<>(batchSize);
for (Payment payment : paymentStream) {
    batch.add(payment);
    if (batch.size() == batchSize) {
        jdbcTemplate.batchUpdate("INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Payment p = batch.get(i);
                    ps.setBigDecimal(1, p.amount());
                    ps.setString(2, p.currency());
                    ps.setString(3, p.status());
                }
                @Override
                public int getBatchSize() {
                    return batch.size();
                }
            });
        batch.clear();
    }
}
// Flush remaining
if (!batch.isEmpty()) {
    // same batchUpdate call
}

Batch Insert with NamedParameterJdbcTemplate

If you prefer named parameters (and you should, for readability), NamedParameterJdbcTemplate provides a convenient batchUpdate method that accepts an array of SqlParameterSource. This is especially useful when you have a list of objects.

Here's how to use it:

```java @Autowired private NamedParameterJdbcTemplate namedJdbcTemplate;

public int[] batchInsert(List<Payment> payments) { String sql = "INSERT INTO payment (amount, currency, status) VALUES (:amount, :currency, :status)"; SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(payments.toArray()); return namedJdbcTemplate.batchUpdate(sql, batch); } ```

This is cleaner than using BatchPreparedStatementSetter. Under the hood, it still uses PreparedStatement batching, so the same driver parameters apply.

But there's a catch: SqlParameterSourceUtils.createBatch() uses reflection to map fields to parameter names. This is fine for moderate-sized batches, but for millions of records, the reflection overhead can add up. In those cases, consider using a custom BatchPreparedStatementSetter for maximum performance.

Also, note that NamedParameterJdbcTemplate.batchUpdate() returns an int[] of update counts. You can use this to verify the number of rows affected, but be aware that some drivers return Statement.SUCCESS_NO_INFO (-2) for batch operations.

Let's see a realistic example with a payment processing system:

``java public void processPayments(List<Payment> payments) { String sql = "INSERT INTO payment (id, amount, currency, status, created_at) " + "VALUES (:id, :amount, :currency, :status, :createdAt)"; List<SqlParameterSource> batch = new ArrayList<>(); for (Payment p : payments) { batch.add(new MapSqlParameterSource() .addValue("id", p.id()) .addValue("amount", p.amount()) .addValue("currency", p.currency()) .addValue("status", p.status().name()) .addValue("createdAt", Timestamp.from(p.createdAt()))); } namedJdbcTemplate.batchUpdate(sql, batch.toArray(new SqlParameterSource[0])); } ``

This approach is easy to read and maintain. For most applications, the performance is excellent.

@Autowired
private NamedParameterJdbcTemplate namedJdbcTemplate;

public void batchInsertPayments(List<Payment> payments) {
    String sql = "INSERT INTO payment (id, amount, currency, status, created_at) " +
                 "VALUES (:id, :amount, :currency, :status, :createdAt)";
    SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(payments.toArray());
    int[] updateCounts = namedJdbcTemplate.batchUpdate(sql, batch);
    // Verify updateCounts if needed
}

Tuning Batch Size and Transaction Management

Finding the right batch size is more art than science. Too small, and you don't get the full benefit of batching. Too large, and you risk memory issues, long transactions, and database contention.

Here's my rule of thumb: start with 100, then benchmark. Increase by 50 until you see diminishing returns. In most databases, the sweet spot is between 100 and 500. For MySQL with rewriteBatchedInserts, I've found 500 to be optimal for rows with 5-10 columns. For PostgreSQL, 200-300 is common.

But batch size isn't the only factor. Transaction management is equally important. If you wrap the entire batch job in a single transaction, you risk: - Holding locks for too long, causing deadlocks. - Rolling back a huge amount of work if something fails. - Running out of transaction log space (especially in MySQL with innodb_log_file_size).

Instead, use smaller transactions per batch. Here's a pattern:

``java @Transactional(propagation = Propagation.REQUIRES_NEW) public void insertBatch(List<Payment> batch) { // batch insert logic } ``

But be careful: if you call this from a loop, each call creates a new transaction, which adds overhead. A better approach is to manually control the transaction:

```java @Autowired private PlatformTransactionManager transactionManager;

public void insertInBatches(List<Payment> allPayments, int batchSize) { TransactionTemplate template = new TransactionTemplate(transactionManager); template.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); for (int i = 0; i < allPayments.size(); i += batchSize) { int end = Math.min(i + batchSize, allPayments.size()); List<Payment> batch = allPayments.subList(i, end); template.execute(status -> { // batch insert return null; }); } } ```

This ensures each batch is committed independently, reducing lock contention and making failures recoverable.

@Autowired
private PlatformTransactionManager transactionManager;

public void insertInBatches(List<Payment> allPayments, int batchSize) {
    TransactionTemplate template = new TransactionTemplate(transactionManager);
    template.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    for (int i = 0; i < allPayments.size(); i += batchSize) {
        int end = Math.min(i + batchSize, allPayments.size());
        List<Payment> batch = allPayments.subList(i, end);
        template.execute(status -> {
            String sql = "INSERT INTO payment (id, amount, currency, status) VALUES (?, ?, ?, ?)";
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int j) throws SQLException {
                    Payment p = batch.get(j);
                    ps.setString(1, p.id());
                    ps.setBigDecimal(2, p.amount());
                    ps.setString(3, p.currency());
                    ps.setString(4, p.status().name());
                }
                @Override
                public int getBatchSize() {
                    return batch.size();
                }
            });
            return null;
        });
    }
}

Comparison: JdbcTemplate vs. JPA Batch Inserts

You might be wondering: should I use Spring JDBC batch inserts or JPA/Hibernate batch inserts? The answer depends on your use case, but I'll give you my opinion: for bulk operations, use JDBC. JPA's batch support is an afterthought and has many pitfalls.

Let's compare:

JPA can do batch inserts if you configure hibernate.jdbc.batch_size and enable order_inserts. But I've seen cases where Hibernate still sends individual INSERTs because of identity generators or other settings. With JDBC, you have full control.

Here's a rule of thumb: if you're inserting more than 1,000 records at a time, use JDBC batch inserts. For transactional CRUD with a few objects, JPA is fine.

Let's see a side-by-side example:

JPA approach:

``java @Transactional public void insertPaymentsJpa(List<Payment> payments) { for (Payment p : payments) { entityManager.persist(p); } entityManager.flush(); entityManager.clear(); } ``

With proper configuration (hibernate.jdbc.batch_size=50, hibernate.order_inserts=true), this will batch. But if you use IDENTITY ID generation, batching is disabled because Hibernate needs to get the ID after each insert.

JDBC approach (no such issue):

``java public void insertPaymentsJdbc(List<Payment> payments) { String sql = "INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)"; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { ... }); } ``

Always works, no hidden surprises.

// JPA batch insert (requires configuration)
@PersistenceContext
private EntityManager entityManager;

@Transactional
public void insertPaymentsJpa(List<Payment> payments) {
    for (int i = 0; i < payments.size(); i++) {
        entityManager.persist(payments.get(i));
        if (i % 50 == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
}

// JDBC batch insert (always works)
public void insertPaymentsJdbc(List<Payment> payments) {
    String sql = "INSERT INTO payment (amount, currency, status) VALUES (?, ?, ?)";
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Payment p = payments.get(i);
            ps.setBigDecimal(1, p.amount());
            ps.setString(2, p.currency());
            ps.setString(3, p.status());
        }
        @Override
        public int getBatchSize() {
            return payments.size();
        }
    });
}

Common Mistakes and How to Avoid Them

Over the years, I've seen the same mistakes repeated. Here are the top ones:

1. Not setting rewriteBatchedInserts=true (MySQL). This is the #1 mistake. Without it, batchUpdate() sends individual INSERTs. Always set it.
2. Using too large a batch size. I've seen people batch 10,000 records at once, causing OutOfMemoryError. Start small and benchmark.
3. Not handling transaction failures. If a batch fails, you need to decide whether to roll back the entire batch or skip failed records. Use savepoints or validate data beforehand.
4. Ignoring connection pool settings. If your pool is too small, batch inserts can exhaust connections. Ensure max pool size is adequate.
5. Forgetting to clear the batch list. When using a loop with batch collection, remember to clear the list after each flush. Otherwise, you'll insert duplicates.
6. Using JPA with IDENTITY generators for bulk inserts. As mentioned, this disables batching. Use SEQUENCE or UUID.
7. Not testing with realistic data volumes. A batch insert that works with 100 records may fail with 100,000. Always test with production-like data.

// Not a code example, but a checklist comment
// 1. Set rewriteBatchedInserts=true in JDBC URL
// 2. Choose batch size (start with 100)
// 3. Use transaction per batch
// 4. Monitor logs: logging.level.org.springframework.jdbc=DEBUG
// 5. Test with production data volume