JDBC: Batch Insert Example


 

JDBC: Batch Insert Example

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

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

JDBC: Batch Insert Example

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

Batch Insert :

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

  • 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 insertion 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 insert 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 : 

package jdbc;

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

class BatchInsertion {
	public static void main(String[] args) {
		System.out.println("Batch Insertion 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 {
			Class.forName(driverName);
			con = DriverManager.getConnection(url + dbName, userName, password);
			try {
				Statement st = con.createStatement();
				con.setAutoCommit(false);
				String sql = "INSERT INTO student VALUES(5,'Jesali','MCA','London')";
				st.addBatch(sql);
				sql = "INSERT INTO student VALUES(6,'Linda','BCA','NewYork')";
				st.addBatch(sql);
				sql = "INSERT INTO student VALUES(7,'Lori','MTech','Kolkata')";
				st.addBatch(sql);
				st.executeBatch();
				con.commit();
				System.out.println("Batch insertion done successfully");
			} catch (SQLException s) {
				System.out.println(s);
			}
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Output : 

Batch Insertion Example....
Batch insertion done successfully

Ads