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.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • 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

    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

    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

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

  • http://wfs.hu 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()!!!

  • 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

  • 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?

  • bitpattern

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

  • 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

      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.

  • Pingback: JDBC Tutorials()