Java JDBC batch inserts and performance test with PostgreSQL

In this tutorial, we will see how to batch SQL insert queries, using Java JDBC. Then, we will do a performance test to compare batched inserts VS non-batched inserts.

1) The SQL table

We will use the following table:

CREATE TABLE customer (name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);

2) Batch inserts

Starting from a JDBC connection object called conn:

java.sql.Connection conn;

simply do:

int rowSize = 100000;
try (Statement stmt = conn.createStatement()) {
    for (int i = 0; i < rowSize; i++) {
        String sql = "insert into customer (name,email) values ('" + i + "','" + i + "')";
        stmt.addBatch(sql);
    }
    stmt.executeBatch();
}

We use the function Statement.addBatch() to build the batch. Once we are done, we send the whole batch at once to the database, using the function Statement.executeBatch().

3) Performance test VS inserts without batches

First, let’s write the same code as above without batches:

try (Statement stmt = conn.createStatement()) {
    for (int i = rowSize; i < 2 * rowSize; i++) {
        String sql = "insert into customer (name,email) values ('" + i + "','" + i + "')";
        stmt.executeUpdate(sql);
    }
}

The main difference with the previous “batched” code, is that now we send queries to the database at every loop iteration via the function Statement.executeUpdate().

Let’s run the 2 programs with a local PostgreSQL database running with TestContainers (please find a very light tutorial on how to do this here). For 100,000 entries in each case, we obtain the following performances:

With batch: 3327 ms
No batch: 13051 ms
Total rows in DB: 200000

The batched inserts are 5 times faster than the non-batched ones. So batching SQL queries with JDBC is very useful ! Also we counted the rows in database, to ensure that all 200,000 entries were created.

4) Full working Java code

That’s it for this tutorial ! Hopefully now you have a better understanding of how SQL JDBC batching works ! Please leave a reply below if you need any help, we answer within 24h.