JDBC Batch Insert Example

Joormana BrahmaApril 27th, 2015Last Updated: March 5th, 2019

0 1,747 3 minutes read

1. Introduction

In this article we are going to present a simple example of using JDBC Batch for doing bulk inserts into a relational database. As stated in a previous article, the Batch operation exposed in JDBC (Java DataBase Connectivity API) helps to bundle together a group of operations and execute them as a single unit. This helps to avoid making repeated database calls for each operation and thereby saves the number of network calls to be made to the database.

It is worth noting that when executing a bunch of operations in a batch one or more operations could fail leading to an unstable state of the database; hence we are going to run the batch operations in transaction units. Think of it as atomic units. This would ensure that if any one of the operations in the batch fails, the whole batch fails. And if all operations in the batch succeed, the whole batch succeeds. To achieve this, the autocommit property of the connection object would be turned off and an explicit commit/rollback of the entire batch would be done as shown in the provided code snippets.

This article will discuss three approaches of batching the ‘insert’ operation. First it will demonstrate using the Statement Object, then PreparedStatement Object and finally, it will show how a large batch of operations could be batched/chunked in Batching the Batch section. The entire example code is available for download at the end of the article.

2. Project Set-up

Project Structure
- An Eclipse project would be set up as shown below
- Note the use of the external jar: ‘mysql-connector-java‘ for connecting to the database from Eclipse
Project structure
DataBase Connection
- A JDBC Connection will be made to a MySQL database
- We will use a persons table with the following schema in the database
firstName lastName age ID

3. Batch Using Statement

The first approach is using the Statement object. It involves the following steps:

Create a Statement object.
Notice how the values to be set have to be specified with each insert query. This seems pretty tedious; hence PreparedStatement is preferred in most cases which is demonstrated next.
Turn autocommit off to run the batch in a single transaction
Add the SQL query to be executed to the Connection object using the addBatch() method
Execute the batch
Then do a commit or roll-back

ExampleUsingStatement.java

try{
      Statement stmt = connection.createStatement();
      connection.autoCommit(false);
      for(int i=1; i<= 200;i++){
          stmt.addBatch("insert into PERSONS values ('Java','CodeGeeks',"+i+","+i+")");
      }
      int[] result = stmt.executeBatch();
      System.out.println("The number of rows inserted: "+ result.length);
      connection.commit();
}catch(Exception e){
      e.printStackTrace();
      connection.rollBack();
} finally{
      if(stmt!=null)
      stmt.close();
if(connection!=null)
       connection.close();
}

4. Batch Using PreparedStatement

This section uses Download NOW!

String sqlQuery = "insert into PERSONS values (?,?,?,?)"; try{ PreparedStatement pstmt = connection.prepareStatement(sqlQuery); connection.autoCommit(false); for(int i=1; i<= 200;i++){ pstmt.setString(1,"Java"); pstmt.setString(2,"CodeGeeks"); pstmt.setInt(3,i); pstmt.setInt(4,i); pstmt.addBatch(); } int[] result = pstmt.executeBatch(); System.out.println("The number of rows inserted: "+ result.length); connection.commit(); }catch(Exception e){ e.printStackTrace(); connection.rollBack(); } finally{ if(pstmt!=null) pstmt.close(); if(connection!=null) connection.close(); }

5. Batching the Batch

In the examples above, we batched around 200 rows together and inserted them into the table. But what if thousands or more records were to be batched and inserted at one go? Well, this could choke the database. The below snippet demonstrates how batch operations could be performed in chunks to avoid such a scenario.

ExampleUsingPreparedStmtBatchSize.java

String sqlQuery = "insert into PSERSONS values (?,?,?,?)"; int count = 0; int batchSize = 50; try{ connection.setAutoCommit(false); PreparedStatement pstmt = connection.prepareStatement(SQL); for(int i=1;i<=1000;i++){ pstmt.setString(1,"Java"); pstmt.setString(2,"CodeGeeks"); pstmt.setInt(3,i); pstmt.setInt(4, i); pstmt.addBatch(); count++; if(count % batchSize == 0){ System.out.println("Commit the batch"); result = pstmt.executeBatch(); System.out.println("Number of rows inserted: "+ result.length); connection.commit(); } } }catch(Exception e){ e.printStackTrace(); connection.rollBack(); } finally{ if(pstmt!=null) pstmt.close(); if(connection!=null) connection.close(); }