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.
 
 
 
 

Want to be a JDBC Master ?
Subscribe to our newsletter and download the JDBC Ultimate Guide right now!
In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Thank you!

We will contact you soon.


 
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

3. Batch Using Statement

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

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:

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
Exit mobile version