JDBC: Batch Update Example


 

JDBC: Batch Update Example

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

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
			Class.forName(driverName);
			con = DriverManager.getConnection(url + dbName, userName, password);
			try {
				// creating object of Statement
				Statement st = con.createStatement();

				// Setting false value of auto_commit
				con.setAutoCommit(false);

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

				// Adding to the batch
				st.addBatch(sql);

				sql = "UPDATE student SET course='BCA' WHERE roll_no=4";
				st.addBatch(sql);

				sql = "UPDATE student SET course='MTech' WHERE roll_no=5";
				st.addBatch(sql);

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

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

Output : 

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

Ads