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