JDBC- Batch Statement example- Execute INSERT 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));

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




Full Programs JDBC- Batch Statement - Execute INSERT 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 StatementInsertTest {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = 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.
  
                 stmt = con.createStatement();
  
     //add (sql queries) in batch Statement
                 stmt.addBatch("INSERT into EMPLOYEE (ID, NAME) values (1, 'ankit') ");
                 stmt.addBatch("INSERT into EMPLOYEE (ID, NAME) values (2, 'neha') ");
         
                 //Execute batch Statement
                 stmt.executeBatch();
                 System.out.println("Batch Statement executed");
                
                 con.commit(); //commit all the transactions
                
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
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 insert 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).



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


Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots


No comments:

Post a Comment