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



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

JDBC tutorial - What is java.sql.CallableStatement in java


java.sql.PreparedStatement's executeUpdate method can be used for executing UPDATE queries and
executeUpdate method returns number of rows updated
 


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;
--If table already exists then execute the DROP command >
drop table EMPLOYEE;



Before execution of below program EMPLOYEE table will look like this >

After execution of below program EMPLOYEE table will look like this >


Example/ Full Programs JDBC- Execute UPDATE query using PreparedStatement's executeUpdate method in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementUpdateExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = 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!");
                
                 prepStmt = con.prepareStatement("UPDATE EMPLOYEE set NAME=? where ID=? ");
                 prepStmt.setString(1, "amy"); //substitute first occurrence of ? with "amy"
                 prepStmt.setInt(2, 8); //substitute second occurrence of ? with 8
                 // execute update query
                 int numberOfRowsUpdated = prepStmt.executeUpdate();
                 System.out.println("numberOfRowsUpdated=" + numberOfRowsUpdated);
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
numberOfRowsUpdated=1
*/


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

Must Know :

By default connection is auto-commit, so all update queries will be committed automatically by java connection.
(Also must know : update 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.


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


RELATED LINKS>

12 Differences between Statement, PreparedStatement and CallableStatement in java


JDBC tutorial- Insert/Store/save IMAGE in database using BLOB data type - in java


JDBC tutorial- Retrieve IMAGE from database using BLOB data type


JDBC tutorial- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC program- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC tutorial- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC example- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment