Share on Google+Share on Google+

JDBC: Batch Insert Example

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

Advertisements

Advertisement

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.