Share on Google+Share on Google+

JDBC: Batch Update Example

In this tutorial, you will learn how to do batch update of records using JDBC API.

JDBC: Batch Update Example

In this tutorial, you will learn how to do batch update of records using JDBC API.

Batch Update :

When you want to update multiple records, then opening and closing MySql connection for every updating may cause communication overhead. So to reduce time and cost, we use batch update concept. We can update multiple rows using single connection in batch update process.

  • addBatch() method is used to add each statement of insertion to the batch individually.
  • executeBatch() method submits a batch of commands to the database for execution and it returns an array of update counts if all the commands execute successfully.

Following are the steps to write batch update process -

  1. Create object of Statement using createStatement() methods.
  2. Set false value to the auto-commit by using method setAutoCommit(false).
  3. Add sql update statements into your batch by using method addBatch().
  4. Execute all the sql statements by using method executeBatch().
  5. Finally, call commit() method to commit all changes.

Example : In this example we are updating multiple records by using single MySql connection.

package jdbc;

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

class BatchUpdate {
	public static void main(String[] args) {
		System.out.println("Batch Update Example....");
		Connection con = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "students";
		String driverName = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "root";
		try {
			// Connecting to the database
			con = DriverManager.getConnection(url + dbName, userName, password);
			try {
				// creating object of Statement
				Statement st = con.createStatement();

				// Setting false value of auto_commit

				// Creating SQL statement
				String sql = "UPDATE student SET course='MTech' WHERE roll_no=1";

				// Adding to the batch

				sql = "UPDATE student SET course='BCA' WHERE roll_no=4";

				sql = "UPDATE student SET course='MTech' WHERE roll_no=5";

				// Executing all batch statements
				int[] counts = st.executeBatch();

				System.out.println("Number of rows updated : " + counts.length);
			} catch (SQLException s) {
		} catch (Exception e) {

Output :

Batch Update Example....
Number of rows updated : 3


Posted on: September 28, 2012 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.