JDBC- Batch PreparedStatement example- Execute UPDATE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java



In this tutorial we will learn how to Execute UPDATE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java JDBC.



java.sql.Statement's addBatch() method can be used for executing Batch statements.

JDBC tutorial - What is java.sql.CallableStatement in java

12 Differences between Statement, PreparedStatement and CallableStatement in JDBC java



--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4),NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;

--If table already exists then execute the DROP command >
drop table EMPLOYEE;


Before execution of below program EMPLOYEE table will look like this >

After execution of below program EMPLOYEE table will look like this >


PreparedStatement BATCH - using executeUpdate methods - INSERT and DELETE  in java

Example/ Full Programs JDBC- Batch PreparedStatement - Execute UPDATE query using PreparedStatement's executeUpdate method in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementUpdateExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                
                 con.setAutoCommit(false); //Now, transactions won't be committed automatically.
                
                 prepStmt = con.prepareStatement("UPDATE EMPLOYEE set NAME=? where ID=? ");
  
                 //1) add set of parameters in PreparedStatement's object - BATCH of commands
                 prepStmt.setString(1, "amy"); //substitute first occurrence of ? with "amy"
                 prepStmt.setInt(2, 7); //substitute second occurrence of ? with 7
                 prepStmt.addBatch();
                
                 //2) add set of parameters in PreparedStatement's object - BATCH of commands
                 prepStmt.setString(1, "sam"); //substitute first occurrence of ? with "sam"
                 prepStmt.setInt(2, 8); //substitute second occurrence of ? with 8
                 prepStmt.addBatch();
                
                
                 //Execute PreparedStatement batch
                 prepStmt.executeBatch();
                 System.out.println("PreparedStatement Batch executed, UPDATE done");
                
                 con.commit(); //commit all the transactions
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
PreparedStatement Batch executed, UPDATE done
*/


So in this tutorial we learned how to Execute UPDATE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java JDBC.


RELATED LINKS>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


12 Differences between Statement, PreparedStatement and CallableStatement in java


JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment