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');
commit;
--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
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("UPDATE EMPLOYEE set NAME='amy' where ID=1 ");
stmt.addBatch("UPDATE EMPLOYEE set NAME='sam' where ID=2 ");
//Execute batch Statement
stmt.executeBatch();
System.out.println("Batch Statement executed");
con.commit(); //commit all the transactions
} catch (ClassNotFoundException e) {
e.printStackTrace();
con.rollback();
} 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 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).
RELATED LINKS>