Program to use insertRow() 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 insertRow() when  CONCUR_UPDATABLE ResultSet concurrency is used in java

CONCUR_UPDATABLE  - CONCUR_UPDATABLE mode means ResultSet object can be read and updated  (means inserting row in ResultSet object) as well.
About moveToInsertRow(), updateString() and insertRow() methods >
  • moveToInsertRow() method moves the cursor to the insert row. The current cursor position is remembered while the cursor is positioned on the insert row. The insert row is a special row associated with an updatable result set. It is essentially a buffer where a new row may be constructed by calling the updater methods prior to inserting the row into the result set.
  • updateString() method does not update underlying database
  • inserting row in resultSet using insertRow() method inserts row in underlying database as well.
  • If in case we are working in transaction where autoCommit is set to false, then it will insert row in underlying database when transaction is committed using connection.commit().


--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_insert {
   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_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                
                 rs = prepStmt.executeQuery();
                
                 System.out.println("CONCUR_UPDATABLE - "+rs.getConcurrency()); //1008 is CONCUR_READ_ONLY
                 System.out.println("\nLet's display all records >");
                 while (rs.next()) {
                       System.out.println(rs.getString("NAME"));
                 }
                
                
                 rs.moveToInsertRow();
                 rs.updateString("NAME", "ankitNew"); //updateString() method does not update underlying database
                
                 //inserting row in resultSet using insertRow() method inserts row in underlying database as well.
                 //If in case we are working in transaction where autoCommit is set to false then it will insert row in underlying database when transaction is committed using connection.commit().
                 rs.insertRow();
                
                
          } 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
Let's display all records >
ankit
rohit
amy
*/

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

No comments:

Post a Comment