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 >
