Home Tutorial Java Core JDBC: Batch Insert Example

 
 

Share on Google+Share on Google+
JDBC: Batch Insert Example
Posted on: September 28, 2012 at 12:00 AM
Advertisement
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
Advertisement

Related Tags for JDBC: Batch Insert Example:


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.

Posted on: September 28, 2012

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com