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 deleteRow() when CONCUR_READ_ONLY ResultSet concurrency is used in java
CONCUR_UPDATABLE - CONCUR_UPDATABLE mode means ResultSet object can be read and updated (means deleting row from ResultSet object) as well.
About deleteRow() method >
- Deleting row from resultSet using deleteRow() method deletes row from database as well.
- If in case we are working in transaction where autoCommit is set to false, then it will delete row from underlying database when transaction is committed using connection.commit().
- Note : all Databases and JDBC drivers doesn't support rs.deleteRow() 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_delete {
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.first(); //move cursor to first position in ResultSet object
//Delete resultSet's first row
//Note : Deleting row from resultSet using deleteRow() method deletes row from database as well.
//If in case we are working in transaction where autoCommit is set to false
//then it will delete row from underlying database when transaction is committed using connection.commit().
rs.deleteRow();
rs.beforeFirst(); //beforeFirst method makes cursor to point to the front of the ResultSet object, just before the first row.
System.out.println("\nLet's display all records, after first record was deleted using rs.deleteRow() method >");
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
} 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!
Let's display all records >
ankit
rohit
amy
Let's display all records, after first record was deleted using rs.deleteRow() method >
rohit
amy
*/
|
After execution of above program EMPLOYEE table will look like this >
Question. But, how to use rs.deleteRow() such that it doesn't delete row from underlying database?
Answer. As, rs.deleteRow() operation deletes underlying row from database as well because resultSet stays connected to database.
So, we must use CachedRowSet, CachedRowSet allows you to create a ResultSet which is disconnected from database, so you may delete row from ResultSet without affecting underlying database.