JDBC- Batch Statement example- Execute UPDATE 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 UPDATE 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 StatementUpdateTest {
   private static Connection con;
   public static void main(String... arg) throws SQLException {
          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("UPDATE EMPLOYEE set NAME='amy' where ID=1 ");
                 stmt.addBatch("UPDATE EMPLOYEE set NAME='sam' 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 updating  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).


JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods 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

Related Posts Plugin for WordPress, Blogger...
//start paginationJs //end paginationJs