JDBC- Statement example - Execute DELETE query(DML command) using Statement's executeUpdate method in java


In this tutorial we will learn how to Execute DELETE query(DML command) using Statement's executeUpdate method in java.

java.sql.Statement's executeUpdate method can be used for executing DELETE queries and
executeUpdate method returns number of rows deleted



--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;


Example/ Full Programs JDBC- 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 StatementDeleteExample {
   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!");
                
                 stmt = con.createStatement();
                 //execute delete query
                 int numberOfRowsDeleted = stmt.executeUpdate("DELETE from EMPLOYEE "
                                                                                                                                                  + " where ID=2 ");
                
                 System.out.println("numberOfRowsDeleted=" + numberOfRowsDeleted);
          } 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!
numberOfRowsDeleted=1
*/



Must read : java.sql.Statement - using executeUpdate  and executeQuery methods CREATE, SELECT,INSERT, UPDATE
java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE


Must Know :
By default connection is auto-commit, so all delete queries will be committed automatically.
(Also must know : delete is a DML(Data Manipulation Language) command, queries are not committed automatically in database)

We may stop connection from doing auto-commits by setting auto-commit to false using > con.setAutoCommit(false);
Well in that we may commit transactions any time using con.commit();

Also, We may set connection back to default behavior of doing auto-commit by using > con.setAutoCommit(true) or simply using con.setAutoCommit();

But what will be benefit of using con.setAutoCommit(false)?
If any transaction goes wrong than we may halt execution further related transactions before calling con.commit() and call con.rollback() in catch block.


NOTE : Statement is suitable for executing DDL commands - create, drop, alter and truncate. This article demonstrates that Statement can also be used to serve the purpose.
We must prefer PreparedStatement, as it is suitable for executing DML commands - SELECT, INSERT, UPDATE and DELETE.

So, in this tutorial we learned how to Execute DELETE query(DML command) using Statement's executeUpdate method in java.


RELATED LINKS>

java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE

Difference between CLOB and CLOB data type in Oracle


JDBC Transactions - commit and rollback(TCL command) - using PreparedStatement


Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from 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