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

  

• 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 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.