Photo by Jonathan Farber on Unsplash

Batching Inserts in Java for Performance

When inserting a single entity into a database, executing a simple INSERT statement is sufficient.

However, when inserting a large list of records, using a loop with executeUpdate is not an efficient approach.

Each time an INSERT statement is executed, the method commits the transaction. When performed inside a loop, this can significantly impact performance.

A better alternative is batch insertion.

In this approach, the list is split into smaller chunks, and each chunk is inserted in a single batch. This reduces the number of database calls and improves overall performance.

In Java, you can use the batchUpdate method of JdbcTemplate to achieve this.

There are two main approaches:

1. Manually chunking the list into sublists and passing each chunk to batchUpdate inside a loop.
2. Using an overloaded batchUpdate method, which takes the entire list, a batch size, and a ParameterizedPreparedStatementSetter.

Manual Chunking with BatchPreparedStatementSetter

Here we manually chunk the list into sublists and then pass each chunk to the batchUpdate method within a loop.

The batchUpdate method inserts them, each chunk at once.

This method takes a parameter of BatchPreparedStatementSetter where the values are set to the insert statement.

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Repository
public class StudentRepository {

    private final JdbcTemplate jdbcTemplate;

    public StudentRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void bulkInsertStudents(List<Student> students) {
        String sql = "INSERT INTO students (id, name, age, email) VALUES (?, ?, ?, ?)";

        int batchSize = 500;
        for (int i = 0; i < students.size(); i += batchSize) {
            List<Student> batchList = students.subList(i, Math.min(i + batchSize, students.size()));

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    Student student = batchList.get(index);
                    ps.setLong(1, student.getId());
                    ps.setString(2, student.getName());
                    ps.setInt(3, student.getAge());
                    ps.setString(4, student.getEmail());
                }

                @Override
                public int getBatchSize() {
                    return batchList.size();
                }
            });
        }
    }
}

Using ParameterizedPreparedStatementSetter

Alternatively, you can use the overloaded batchUpdate method, which accepts the full list, a fixed batch size, and a ParameterizedPreparedStatementSetter.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Repository
public class StudentRepository {

    private final JdbcTemplate jdbcTemplate;

    public StudentRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void bulkInsertStudents(List<Student> students) {
        String sql = "INSERT INTO students (id, name, age, email) VALUES (?, ?, ?, ?)";

        int batchSize = 500;
        jdbcTemplate.batchUpdate(sql, students, batchSize, (ps, student) -> {
            ps.setLong(1, student.getId());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.setString(4, student.getEmail());
        });
    }
}

Choosing the Right Batch Size

One key design decision is selecting an appropriate batch size for inserts.

Since multiple records are inserted in a single batch, this impacts both memory usage and database constraints on the maximum number of statements allowed in one execution.

An ideal batch size typically ranges between 500 and 1000, depending on the list size. The total time taken for insertion varies based on this setting.

I send one interesting post about topics in software engineering and development every week. Subscribe to my newsletter and stay notified — https://referbruv.substack.com/