JDBC Batch Updates

 
You can send multiple queries to the database at a time using batch update feature of Statement/PreparedStatement objects this reduces the number of JDBC calls and improves performance.
 

Statement Batch Updates

 
To issue a Batch update , call the addBatch() and executeBatch() methods as shown below.
 

dbConnection.setAutoCommit(false);
 
statement = connection.createStatement();
statement.addBatch(insertTableQuery1);
statement.addBatch(insertTableQuery2);
statement.addBatch(insertTableQuery3);
 
statement.executeBatch();
dbConnection.commit();

 
Full example – Batch Update using JDBC Statement
 

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStmtBatchUpdate
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

	public static void main(String[] argc)
	{

		try
		{
			batchInsertRecordsIntoTable();
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
	}

	private static void batchInsertRecordsIntoTable() throws SQLException
	{
		Connection connection = null;
		Statement statement = null;

		String insertTableQuery1 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1001,'Jamil','admin')";

		String insertTableQuery2 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1002,'Ameer','admin')";

		String insertTableQuery3 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1003,'Nilafar','admin')";

		try
		{
			connection = getDBConnection();
			statement = connection.createStatement();

			connection.setAutoCommit(false);

			statement.addBatch(insertTableQuery1);
			statement.addBatch(insertTableQuery2);
			statement.addBatch(insertTableQuery3);

			statement.executeBatch();
			connection.commit();
			System.out.println("Records are inserted into EMPLOYEE table!");

		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
		finally
		{
			if (statement != null)
			{
				statement.close();
			}

			if (connection != null)
			{
				connection.close();
			}

		}

	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;

		}
		catch (SQLException e)
		{
		System.out.println(e.getMessage());
		}

		return dbConnection;
	}
}

PreparedStatement Batch Updates

 
You can also use a PreparedStatement object to execute batch updates. The PreparedStatement enables you to reuse the same SQL statement, and just insert new parameters into it, for each update to execute. This method also avoids SQL Injection issue.
 
Here is an example (Batch Update using PreparedStatement):
 

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPstmtBatchUpdate
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

	public static void main(String[] argc)
	{
		try
		{
			batchInsertRecordsIntoTable();
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
	}

private static void batchInsertRecordsIntoTable() throws SQLException
{
Connection connection = null;
PreparedStatement preparedStmt = null;
String insertTableQuery="INSERT INTO EMPLOYEE (USER_ID,USERNAME,CREATED_BY) VALUES (?,?,?)";

	try
	{
		connection = getDBConnection();
		preparedStmt = connection.prepareStatement(insertTableQuery);

		connection.setAutoCommit(false);

			preparedStmt.setInt(1, 1000);
			preparedStmt.setString(2, "Nilafar");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1001);
			preparedStmt.setString(2, "Azar");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1002);
			preparedStmt.setString(2, "Gokul");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1003);
			preparedStmt.setString(2, "Haripriya");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.executeBatch();
			connection.commit();
			System.out.println("Record is inserted into EMPLOYEE table!");

		}

		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		} finally
		{
			if (preparedStmt != null)
			{
				preparedStmt.close();
			}

			if (connection != null)
			{
				connection.close();
			}
		}
	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);

		}
		catch (ClassNotFoundException e)
		{
			System.out.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}

		return dbConnection;
	}
}

Download It – BatchUpdates

About Mohaideen Jamil