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


In this tutorial we will learn how to Execute DELETE 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 in java jdbc.

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 UPDATE  in java.


Example/ Full Programs JDBC- Batch PreparedStatement - Execute DELETE 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 PreparedStatementDeleteExample {
   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("DELETE from EMPLOYEE where ID=? ");
  
                 //1) add set of parameters in PreparedStatement's object - BATCH of commands
                 prepStmt.setInt(1, 7); //substitute first occurrence of ? with 7
                 prepStmt.addBatch();
                 //2) add set of parameters in PreparedStatement's object - BATCH of commands                 
                 prepStmt.setInt(1, 8); //substitute first occurrence of ? with 8
                 prepStmt.addBatch();
                
                
                 //Execute PreparedStatement batch
                 prepStmt.executeBatch();
                 System.out.println("PreparedStatement Batch executed, DELETE 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, DELETE done
*/



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



RELATED LINKS>

java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE

JDBC- Insert/Store/save FILE in database

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


Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java

Difference between CLOB and CLOB data type in Oracle


No comments:

Post a Comment