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
Statementobject.
Notice how the values to be set have to be specified with each insert query. This seems pretty tedious; hencePreparedStatementis 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)oraddString(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.
As promised, the code is available for download here : JDBC Batch Insert Example
