Program to use updateRow() in CONCUR_UPDATABLE ResultSet concurrency


Before reading this article I’ll recommend you to read : JDBC - What is ResultSet in java - Types, concurrency, holdability of ResultSet in java

Program to use updateRow() when  CONCUR_UPDATABLE ResultSet concurrency is used in java

CONCUR_UPDATABLE  - CONCUR_UPDATABLE mode means ResultSet object can be read and updated  (means updating row in ResultSet object) as well.
About updateString() and updateRow() methods >
  • Updating resultSet using updateString() method does not update database data.
  • updateRow() method will update the underlying database with the new contents of the current row of this ResultSet object, updateRow() method cannot be called when the cursor is on the insert row
  • If in case we are working in transaction where autoCommit is set to false then it will update row in underlying database when transaction is committed using connection.commit().
  • Note : all Databases and JDBC drivers doesn't support rs.updateRow() operation

--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');
insert into EMPLOYEE values(3, 'amy');
commit;

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


EMPLOYEE table will look like this >

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CONCUR_UPDATABLE_ResultSetConcurrency_update {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = 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!");
                
                 //ResultSet concurrency = CONCUR_UPDATABLE
                 prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                
                 rs = prepStmt.executeQuery();
                
                 System.out.println("CONCUR_UPDATABLE - "+rs.getConcurrency()); //1008 is CONCUR_READ_ONLY
                
                 rs.next(); //move cursor to first row in ResultSet object
                 //Print first row data before update
                 System.out.println("\nPrint first row data before update >");
                 System.out.println(rs.getString("NAME")); //print ankit
                
                 //Update resultSet data in first row
                 //It will update data in first row (i.e. in columnName NAME from ankit to ankitUpdated)
                 //Note : Updating resultSet using updateString method does not update database data.
                 rs.updateString("NAME", "ankitUpdated");
                
                 //Print first row data after update
                 System.out.println("\nPrint first row data after update >");
                 System.out.println(rs.getString("NAME")); //print ankitUpdated
                
                 //updateRow() method will update the underlying database with the new contents of the current row of this ResultSet object, updateRow() method cannot be called when the cursor is on the insert row
                 //If in case we are working in transaction where autoCommit is set to false then it will update row in underlying database when transaction is committed using connection.commit().
                 //Note : all Databases and JDBC drivers doesn't support rs.updateRow() operation
                 rs.updateRow();
                
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
CONCUR_UPDATABLE - 1008
Print first row data before update >
ankit
Print first row data after update >
ankitUpdated
*/

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


No comments:

Post a Comment