Pages Navigation Menu

Coding is much easier than you think

JDBC Batch Updates

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.
 

Note : Batch Update is not limit to Insert statement, it’s applied for Update and Delete statement as well.

 

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;
	}
}

 

dwd2
Download It – BatchUpdates

 

About Mohaideen Jamil


Am currently working as a Struts 2 Developer in a reputed IT Organisations. I can help you with teaching Core java and Struts 2. Follow me on Facebook or Google Plus. If you like my tutorials, consider making a donation to this charity, thanks.