Home » Core Java » sql » JDBC Batch Insert Example

About Joormana Brahma

Joormana Brahma
She has done her graduation in Computer Science and Technology from Guwahati, Assam. She is currently working in a small IT Company as a Software Engineer in Hyderabad, India. She is a member of the Architecture team that is involved in development and quite a bit of R&D. She considers learning and sharing what has been learnt to be a rewarding experience.

JDBC Batch Insert Example

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

    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
    firstNamelastNameageID

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 PreparedStatement object. As can be seen from the code snippets, it allows reusing the basic SQL query. The '?' acts as a placeholder for the parameter values which can be supplied later using the addInt(index,value) or addString(index,value) method as appropriate. Thus, unlike the previous case it is more neat and it also helps to check against SQL Injection threat. The steps are listed as below:

  • Create PreparedStatement
  • Turn off autocommit
  • Add the parameter values for each query using addInt(index,value) or addString(index,value)
  • Add to the batch using addBatch()
  • Execute the batch
  • Commit or roll-back the transaction

ExampleUsingPreparedStatement.java

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();
}

6. Download the source code

Here we come to the end of this article. Hope it was an interesting and helpful read.

Download
As promised, the code is available for download here : JDBC Batch Insert Example
(+1 rating, 1 votes)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of