How to insert bulk of records in Java – JDBC?

Sometimes we need to perform a bulk of data insertion/updation in the application. Batch Processing facilitates you to group related SQL statements into a batch and submit them as group of statements rather to execute each statement independently. For batch processing you need to set auto-commit false using setAutoCommit(false) as by default it is ON.

There are following three methods for batch processing in JDBC API.

  1. addBatch() : This is used to add individual statements to the batch
  2. executeBatch() : This is used to start the execution of all the statements grouped together.
  3. clearBatch() : This method removes all the statements you added with the addBatch() method.

Case Study

For large bulk operations like if you need to insert 10,00000 records, we can’t group it into single batch for execution. As there are some limitation of database vendors for executing numbers of statements in single execution. In this case you need to break number of statements into several batches and process single batch at once. Like we can divide 10 lakh records into 500-1000 statements and execute them as batch.

Smart way of Batch Processing

String sql = "insert into customers (name, city, phone) values (?, ?, ?)";
PreparedStatemen ps = conn.prepareStatement(sql);
final int BATCH_SIZE = 1000;
int count = 0;

for (Customer customer: customers) {
	ps.setString(1, customer.getName());
	ps.setString(2, customer.getCity());
	ps.setString(3, customer.getPhone());
	ps.addBatch();
	
	if(++count % BATCH_SIZE == 0) {
		ps.executeBatch(); // here we are inserting 1000 statements as single batch
	}
}
ps.executeBatch(); // insert remaining records
ps.close();
connection.close();

 

Leave a Reply