How can I ensure that a JDBC batch insert is done atomically?

user_766736 0
Viewed 123

I have the following (pseudoish)code to insert ~5000 rows into a SQL Server table. I'm using Hikari (ds, below, is my HikariDataSource).

try (Connection connection = ds.getConnection();
     PreparedStatement statement = connection.prepareStatement(
          "insert into Dog (name, age, breed) values (?, ?, ?)");) {
        
        
        for (Dog d : dogs) {
            statement.setString(1, d.getName());
            statement.setInt(2, d.getAge());
            statement.setString(3, d.getBreed());
            statement.addBatch();
         }
        
         statement.executeBatch();
} 
// catch exceptions, etc.

This is working fine (in that the inserts work as expected), but if someone queries the table in the middle of the batch insert (which takes a few seconds), they can get an incomplete set of rows. I want them to either get nothing (assuming the table is empty to start), or every row I insert.

I assume I need to do something special to lock the table or otherwise perform all of the inserts as a single transaction (I assumed that that's what the batch insert was but I was wrong).

How can I do this?

hikaricp jdbc sql-server java
1 Answers

By default a connection is in auto-commit mode. The transactional behaviour of batch execution in auto-commit mode depends on the JDBC driver implementation. If you want to ensure they are done atomically, you need to disable auto-commit mode and explicitly commit after executing the batch.

try (Connection connection = ds.getConnection();
     PreparedStatement statement = connection.prepareStatement(
          "insert into Dog (name, age, breed) values (?, ?, ?)");) {
    connection.setAutoCommit(false);
        
    for (Dog d : dogs) {
        statement.setString(1, d.getName());
        statement.setInt(2, d.getAge());
        statement.setString(3, d.getBreed());
        statement.addBatch();
    }
    
    statement.executeBatch();
    connection.commit();
}
user_4668620
Related