JDBC- Batch Statement example- Execute DELETE query(DML command) using Statement's executeUpdate addBatch("sql"), executeBatch() methods in java

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

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

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

Full Programs JDBC- Batch Statement - Execute DELETE query using Statement's executeUpdate method in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementDeleteTest {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = null;
          try {
                 // registering Oracle driver class
                 // getting connection
                 con = DriverManager.getConnection(
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                 con.setAutoCommit(false); //Now, transactions won't be committed automatically.
                 stmt = con.createStatement();
                 // add (sql queries) in batch Statement
                 stmt.addBatch("DELETE from EMPLOYEE where ID=1 ");
                 stmt.addBatch("DELETE from EMPLOYEE where ID=2 ");
                 //Execute batch Statement
                 System.out.println("Batch Statement executed");
                 con.commit(); //commit all the transactions
          } catch (ClassNotFoundException e) {
          } catch (SQLException e) {
                 try {
                       if(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
Connection established successfully!
Batch Statement executed

Note :
Statement does not provide addBatch() method, it provides only addBatch(String sql) method.
Hence, same SQL query can’t be executed repeatedly in Statement (Every Time we have to pass SQL query to be executed in addBatch(String sql) method, as done in above program).
We must prefer to use PreparedStatement for deleting  as it provides addBatch()  method which adds a set of parameters to the PreparedStatement object's batch of commands.
Hence,  same SQL query can be executed repeatedly in PreparedStatement.
(Only once we need to pass SQL query to be executed in addBatch() method, then all we need to do at runtime is to set different parameters as done in the program).


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

Labels: Core Java JDBC
Must read for you :