Nov 10, 2018

Batch Update in SQL

INSERT, Update and Delete statements for many records are better executed in batch mode. It is more efficient than to execute them one by one.

Let’s see an example:

import java.sql.Connection;

import java.sql.Statement;

//…

Connection connection = new getConnection();
Statement statement = connection.createStatement();
For (Contact contact: contacts){
String query = “INSERT INTO Contact (id, name, email) values (‘ “ + Contact. getId()  + “’, ‘” + Contact.getName() +“’, ‘” + Contact.getEmail() + “’)”; 
statement.addBatch(query);
}

statement. executeBatch();

statement.close();

connection.close();

Rather than sending each query one at a time and waiting for each one to finish, send a batch of inserts to the database in one go.

Batching is very important when inserting large data sets. To get significant performance improvement, whenever possible, a programmer must run a single statement in batch mode. The other way to execute batch inserts is to use PreparedStatement object.