JDBC PreparedStatement example – Batch Update

Here’s an example to show you how to insert few records in batch process, via JDBC PreparedStatement.


dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT INTO DBUSER"
			+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
			+ "(?,?,?,?)";				
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();
Note
Batch Update is not limit to Insert statement, it’s apply for Update and Delete statement as well.

See full JDBC batch update example …


package com.mkyong.jdbc;

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

public class JDBCBatchUpdateExample {

	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			batchInsertRecordsIntoTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void batchInsertRecordsIntoTable() throws SQLException {

		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";

		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			dbConnection.setAutoCommit(false);

			preparedStatement.setInt(1, 101);
			preparedStatement.setString(2, "mkyong101");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, getCurrentTimeStamp());
			preparedStatement.addBatch();

			preparedStatement.setInt(1, 102);
			preparedStatement.setString(2, "mkyong102");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, getCurrentTimeStamp());
			preparedStatement.addBatch();

			preparedStatement.setInt(1, 103);
			preparedStatement.setString(2, "mkyong103");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, getCurrentTimeStamp());
			preparedStatement.addBatch();

			preparedStatement.executeBatch();

			dbConnection.commit();

			System.out.println("Record is inserted into DBUSER table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());
			dbConnection.rollback();

		} finally {

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

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

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                              DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Timestamp getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());

	}

}

Result

3 records are inserted into database via batch update process.

Why need to use Batch Update?

Alternatively, you can use normal executeUpdate() method like this :


String insertTableSQL = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";				
PreparpreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 111);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.executeUpdate(); 

preparedStatement.setInt(1, 112);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.executeUpdate();			

The above code snippet is works as well, but has performance issue if you are try to insert many records, let say 1000 records, because every executeUpdate() will hits database once. For batch update process, it hits database when executeBatch() is called.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
ganesh
Guest
ganesh

Hi Mkyong,

Nice site and articles.

Is there anyway to get the count of records using jdbc template prepare statement. I have list of employees and wanted to check any of them existed in DB or not.

Thanks,
Gani

bitpattern
Guest
bitpattern

Mkyoung! you codes are really helpful for me many times . So thank you .

Ali Shaikh
Guest
Ali Shaikh

How to execute batch (multiple queries) with preparared statement to update in different tables

vaat666
Guest
vaat666

Great, simple, and works. What else?

Alexandru Matei
Guest
Alexandru Matei

Do you know if dbConnection.rollback(); works with MySQL ?

I’ve tested version 5.6 and latest Connector-J 5.1.33 and unfortunately the records that succeeded
are still in the database even preparedStatement.executeBatch(); throws an exception

I was expecting that dbConnection.rollback() will remove the records that succeeded.

Joshi Nath
Guest
Joshi Nath

Is it possible to perform Batch Updations using Prepared Statements using different queries?

Ronn
Guest
Ronn

Hi im getting an error while doing executeBatch().
org.netezza.error.NzSQLException: ERROR: Update canceled: attempt to update a target row with values from multiple join rows..
Please enlighten me.

Please check this link for more info:

http://stackoverflow.com/questions/21086372/unable-to-do-a-executebatch-on-netezza-jdbc

K E Naresh
Guest
K E Naresh

con.setAutoCommit(false);
pst.setInt(1, 119);
pst.setString(2, “Praveen”);
pst.addBatch();

Hi,
pst.setInt(1, 120);
pst.setString(2, “Giri”);
pst.addBatch();
int[] count=pst.executeBatch();

pst.close();
//con.commit();

while executing the above code and i go to database and checked, i found 119,120 records inserted permanantly.

Ramsharan Shrestha
Guest
Ramsharan Shrestha

In case of INSERT why do we need to use batch if we want to insert in same table since we can insert multiple row using a single query in mysql.

vsriram
Guest
vsriram

Hi sir,Im having one doubt,i read somewhere that direct linking with prepared Statement is Good rather than Binding with String (like above “insertTableSQL”) is it Correct Sir?

tuanlv
Guest
tuanlv

tks u so much!
now, i understand batch how to work…:D

Roboz Bela
Guest
Roboz Bela

With mysql maybe you need to use this url paramaters: “?useServerPrepStmts=false&rewriteBatchedStatements=true” because without these the performance will be equal with executeUpdate()!!!

roby
Guest
roby

if i want to batch 10000 record, do i need to close every time i addBatch() to prepared statement? if i not close will it return too many open cursor?

hari
Guest
hari

hi,i had a doubt by seeing above example? i.e.,suppose if i have 2 differnet querys then can i u prepared statement and batch update?Is it possible?

bitpattern
Guest
bitpattern

I was doing the same thing for two different queries. I can do that from Statement but ………….I have one way to do it and i did it. let me know if anybody want that code.

trackback
JDBC Tutorials

[…] JDBC PreparedStatement example – Batch Update Example to insert records in batch process, via JDBC PreparedStatement. […]