Skip to content
Related Articles

Related Articles

Inserting Records in Batch Using JDBC
  • Difficulty Level : Hard
  • Last Updated : 22 Feb, 2021

It is carried out using the functions namely addBatch() and executeBatch() methods. For that lets us do have a prior understanding of JDBC drivers. So, in order to connect to your database in Java, you need a JDBC driver. Every database (MySQL, Oracle, etc.) comes with its own JDBC driver, usually built by the database vendor and found on the database’s website. Considering the example of the MySQL database, you will need to go to the MySQL website, download the MySQL JDBC driver .jar file (also called Connector/J), and add it to your project.

Now let’s discuss the methods that they operate on. So, the above two methods namely addBatch() and executeBatch() method is as follows:

Method 1: addBatch()

This method adds the parameter values to the batch internally. You can now add another set of values, to be inserted into the SQL statement. Each set of parameters are inserted into the SQL and executed separately, once the full batch is sent to the database.

Method 2: executeBatch()

This method is called, which executes all the batch updates. The SQL statement plus the parameter sets are sent to the database in one go. The int[] array returned by the executeBatch() method is an array of int telling how many records were affected by each executed SQL statement in the batch.

Syntax: Creation of the database 



create table emp(eid varchar(200) unique,ename varchar(200)

Example:

Java




// Step 1: Importing DB(SQL) classes
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
  
// Class 
public class insertion {
  
    // Main driver method
    public static void main(String[] args) throws Exception
    {
  
        // Try block to check for exceptions
        try {
            // Step 2: Loading and registering drivers
  
            // Loading drivers using forName() method
            Class.forName("com.mysql.jdbc.Driver");
  
            // Registering driver using DriverManager
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost/test", "root", "");
  
            // Step 3: Create a statement
            Statement st = con.createStatement();
            String sql
                = "insert into emp (eid,ename) values (?, ?)";
  
            // Step 4: Execute the querry
            PreparedStatement pstmt;
            pstmt = con.prepareStatement(sql);
            final int batchSize = 5000;
            int count = 0;
            for (int i = 4000; i <= 4500; i++) {
  
                pstmt.setString(1, "181FA0" + i);
                pstmt.setString(2, "181FA0" + i);
  
                pstmt.addBatch();
  
                count++;
  
                if (count % batchSize == 0) {
                    System.out.println("Commit the batch");
                    pstmt.executeBatch();
                }
                pstmt.executeBatch();
            }
        }
        catch (Exception e) {
            System.out.println("Error:" + e.getMessage());
        }
    }
}

Output:

eidename
181FA04001181FA04001
181FA04002181FA04002
. . .. . .
181FA04500181FA04500

Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more,  please refer Complete Interview Preparation Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :