In this tutorial we will learn What is ResultSet in java, Types of ResultSet, concurrency of ResultSet, holdability of ResultSet in java.
What is java.sql.ResultSet in JDBC java?
- java.sql.ResultSet is an interface
- ResultSet is a table of data which represent a database result set, result is generated by executing a statement that queries the database.
- ResultSet object maintains a cursor pointing to its current row of data. cursor initially is pointing before the first row in java jdbc.
- The next method moves the cursor to the next row.
It returns false when there are no more rows in the ResultSet object that's the reason why
it can be used in a while loop to iterate through the ResultSet.
- If ResultSet object does not contain any row and is used in while loop then while loop will never be executed in java jdbc.
Types of ResultSet in java JDBC>
TYPE_FORWARD_ONLY:
- In TYPE_FORWARD_ONLY cursor can only move forward on the result set, not backward in java.
- It is default ResultSet type in java.
Note : TYPE_FORWARD_ONLY is the default ResultSet type in java.
TYPE_SCROLL_INSENSITIVE:
- In TYPE_SCROLL_INSENSITIVE cursor can move (scroll) both forward and backward,
- Cursor can be moved to absolute/specific/relative position as well.
- TYPE_SCROLL_INSENSITIVE is not sensitive to the changes made to the data that underlies the ResultSet. (It means that if some thread modifies the data in the database which ResultSet currently holds won’t impact/change the already opened ResultSet’s data).
TYPE_SCROLL_SENSITIVE:
- In TYPE_SCROLL_SENSITIVE cursor can move (scroll) both forward and backward,
- Cursor can be moved to absolute/specific/relative position as well in java JDBC.
- TYPE_SCROLL_SENSITIVE is sensitive to the changes made to the data that underlies the ResultSet. (It means that if some thread modifies the data in the database which ResultSet currently holds will impact/change the already opened ResultSet’s data).
Must know : getType() - method returns ResultSet object type. It may be TYPE_FORWARD_ONLY (1003), TYPE_SCROLL_INSENSITIVE (1004) or TYPE_SCROLL_SENSITIVE (1005)
What is difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE ResultSet type in java JDBC?
TYPE_SCROLL_INSENSITIVE
|
TYPE_SCROLL_SENSITIVE
|
TYPE_SCROLL_INSENSITIVE is not sensitive to the changes made to the data that underlies the ResultSet. (It means that if some thread modifies the data in the database which ResultSet currently holds won’t impact/change the already opened ResultSet’s data) in java.
|
TYPE_SCROLL_SENSITIVE is sensitive to the changes made to the data that underlies the ResultSet. (It means that if some thread modifies the data in the database which ResultSet currently holds will impact/change the already opened ResultSet’s data) in java.
|
getType() - method returns ResultSet object type. It returns 1004 if ResultSet object type is TYPE_SCROLL_INSENSITIVE.
In ResultSet interface TYPE_SCROLL_INSENSITIVE = 1004
|
getType() - method returns ResultSet object type. It returns 1005 if ResultSet object type is TYPE_SCROLL_SENSITIVE.
In ResultSet interface TYPE_SCROLL_SENSITIVE = 1005;
|
Example -
Let’ say you have multiple users in the database.
You issued select query at 1:00
Than some other user updated the underlying database at 1:01
With TYPE_SCROLL_INSENSITIVE ResultSet, at 1:02 you will still be scrolling (using next(), previous(), first(), last(), absolute() and relative() methods) at old data fetched the at 1:00 and not the updated one.
|
Example -
Let’ say you have multiple users in the database.
You issued select query at 1:00
Than some other user updated the underlying database at 1:01
With TYPE_SCROLL_SENSITIVE ResultSet, at 1:02 you will be scrolling (using next(), previous(), first(), last(), absolute() and relative() methods) at fresh data updated at 1:01 and not the old one.
|
ResultSet concurrency read/update in java>
CONCUR_READ_ONLY - CONCUR_READ_ONLY mode means ResultSet object is read only it may not be updated in java.
CONCUR_UPDATABLE - CONCUR_UPDATABLE mode means ResultSet object can be read and updated as well in java.
Note : All databases and drivers may not support CONCUR_UPDATABLE mode in java.
Must know : getConcurrency() - method returns concurrency mode of this ResultSet object. It may be CONCUR_READ_ONLY (1007) or CONCUR_UPDATABLE (1008)
ResultSet holdability in java JDBC>
HOLD_CURSORS_OVER_COMMIT - HOLD_CURSORS_OVER_COMMIT holdability indicates that open ResultSet objects will remain open when the current transaction is committed in java.
CLOSE_CURSORS_AT_COMMIT - CLOSE_CURSORS_AT_COMMIT holdability indicates that open ResultSet objects will be closed when the current transaction is committed in java.
Note : All databases and drivers may not support ResultSet holdability.
Must know : getHoldability() - method returns holdability of this ResultSet object. It may be HOLD_CURSORS_OVER_COMMIT (1) or CLOSE_CURSORS_AT_COMMIT (2)
ResultSet methods - To navigate over ResultSet in java JDBC>
first() - first method makes cursor to point to the first row in the ResultSet object.
last() - last method makes cursor to point to the last row in the ResultSet object.
next() - next method makes cursor to point to the next row in the ResultSet object.
previous() - previous method makes cursor to point to the previous row in the ResultSet object.
beforeFirst() - beforeFirst method makes cursor to point to the front of the ResultSet object, just before the first row.
afterLast() - afterLast method makes cursor to point to the last/end of the ResultSet object, just after the last row.
absolute(int row) - absolute method moves the cursor to the specified row number in this ResultSet object.
relative(int rows ) - relative method moves the cursor a relative number of rows, either positive or negative.
ResultSet information methods in java JDBC>
isFirst() - method returns true if cursor points to first row in ResultSet object.
isBeforeFirst() - method returns true if cursor is before the first row in ResultSet object.
isAfterLast() - method returns true if cursor is after the last row in ResultSet object.
getRow() - method retrieves the current row number. The first row is number 1, the second number 2.
getType() - method returns ResultSet object type. It may be TYPE_FORWARD_ONLY (1003), TYPE_SCROLL_INSENSITIVE (1004) or TYPE_SCROLL_SENSITIVE (1005)
getConcurrency() - method returns concurrency mode of this ResultSet object. It may be CONCUR_READ_ONLY (1007) or CONCUR_UPDATABLE (1008)
getHoldability() - method returns holdability of this ResultSet object. It may be HOLD_CURSORS_OVER_COMMIT (1) or CLOSE_CURSORS_AT_COMMIT (2)
refreshRow() - method refreshes the current row with its most recent value available in the database.
Few more most important and frequently used ResultSet methods in java JDBC >
getMetaData() - Method retrieves the number, types and properties of this ResultSet object's columns.
getString(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as a String in the Java.
getInt(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an int in the Java.
getTime(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as a java.sql.Time object in the Java.
getByte(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an byte in the Java.
getDouble(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an double in the Java.
getFloat(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an float in the Java.
getLong(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an long in the Java.
getBlob(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an Blob object in the Java.
getClob(int) - Method retrieves the value of the specified column in the current row of this ResultSet object as an Clob object in the Java.
Program 1.1 to demonstrate TYPE_FORWARD_ONLY ResultSet type in java JDBC
--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 TYPE_FORWARD_ONLY_ResultSetType {
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 type = TYPE_FORWARD_ONLY
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = prepStmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
System.out.println("\nTYPE_FORWARD_ONLY - "+rs.getType()); //1003 is TYPE_FORWARD_ONLY
} 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!
ankit
rohit
amy
TYPE_FORWARD_ONLY - 1003
*/
|
//Note : TYPE_FORWARD_ONLY is the default ResultSet type in java, but program shows how to declare it in program
Program 1.2 to demonstrate TYPE_SCROLL_INSENSITIVE ResultSet type in java JDBC
--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 TYPE_SCROLL_INSENSITIVE_ResultSetType {
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 type = TYPE_SCROLL_INSENSITIVE
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
System.out.println("\nLet's display all records >");
rs = prepStmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
System.out.println("\nTYPE_SCROLL_INSENSITIVE - "+rs.getType()); //1004 is TYPE_SCROLL_INSENSITIVE
System.out.println("\nLet's move cursor to first position in ResultSet object");
rs.first();
System.out.println(rs.getString("NAME"));
System.out.println("\nLet's move cursor to last position in ResultSet object");
rs.last();
System.out.println(rs.getString("NAME"));
System.out.println("\nLet's move cursor to previous position in ResultSet object");
rs.previous();
System.out.println(rs.getString("NAME"));
System.out.println("\nLet's move cursor to third row in ResultSet object > using absolute");
rs.absolute(3);
System.out.println(rs.getString("NAME"));
System.out.println("\nLet's move cursor from third to first row in ResultSet object > using relative > (3rd row - 2 = 1st row)");
rs.relative(-2);
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
TYPE_SCROLL_INSENSITIVE - 1004
Let's move cursor to first position in ResultSet object
ankit
Let's move cursor to last position in ResultSet object
amy
Let's move cursor to previous position in ResultSet object
rohit
Let's move cursor to third row in ResultSet object > using absolute
amy
Let's move cursor from third to first row in ResultSet object > using relative > (3rd row - 2 = 1st row)
ankit
*/
|
Program 2.1 to show updating ResultSet will throw java.sql.Exception when CONCUR_READ_ONLY ResultSet concurrency is used in java JDBC
CONCUR_READ_ONLY - CONCUR_READ_ONLY mode means ResultSet object is read only it may not be updated in java JDBC.
--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_READ_ONLY_ResultSetConcurrency {
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_READ_ONLY
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = prepStmt.executeQuery();
System.out.println("CONCUR_READ_ONLY - "+rs.getConcurrency()); //1007 is CONCUR_READ_ONLY
rs.next();
System.out.println(rs.getString("NAME"));
rs.updateString("NAME", "AnkitUpdated"); //java.sql.SQLException: Invalid operation for read only resultset: updateString
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!
CONCUR_READ_ONLY - 1007
ankit
java.sql.SQLException: Invalid operation for read only resultset: updateString
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.BaseResultSet.updateString(BaseResultSet.java:302)
at oracle.jdbc.driver.OracleResultSet.updateString(OracleResultSet.java:819)
at CONCUR_READ_ONLY_ResultSetConcurrency.main(CONCUR_READ_ONLY_ResultSetConcurrency.java:37)
*/
|
Program 2.2 to show deleteRow() when CONCUR_READ_ONLY ResultSet concurrency is used in java JDBC
CONCUR_UPDATABLE - CONCUR_UPDATABLE mode means ResultSet object can be read and updated (means deleting row from ResultSet object) as well in java JDBC.
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.
Program 2.3 to show insertRow() when CONCUR_UPDATABLE ResultSet concurrency is used in java JDBC
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 >
Program 2.4 to show updateRow() when CONCUR_UPDATABLE ResultSet concurrency is used in java JDBC
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 in java JDBC >
- 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 >
Program 3.1 to demonstrate HOLD_CURSORS_OVER_COMMIT ResultSet holdability in java JDBC
HOLD_CURSORS_OVER_COMMIT - HOLD_CURSORS_OVER_COMMIT holdability indicates that open ResultSet objects will remain open when the current transaction is committed.
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 HOLD_CURSORS_OVER_COMMIT_ResultSetHoldability {
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!");
//Begin Transaction T1
con.setAutoCommit(false);
//ResultSet holdability = HOLD_CURSORS_OVER_COMMIT
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
rs = prepStmt.executeQuery();
// LOGIC........................
//Commit Transaction T1
con.commit();
//HOLD_CURSORS_OVER_COMMIT holdability indicates that open ResultSet
//objects will remain open when the current transaction is committed.
} 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();
}
}
}
}
|
Program 3.2 to demonstrate CLOSE_CURSORS_AT_COMMIT ResultSet holdability in java JDBC
CLOSE_CURSORS_AT_COMMIT - CLOSE_CURSORS_AT_COMMIT holdability indicates that open ResultSet objects will be closed when the current transaction is committed.
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 CLOSE_CURSORS_AT_COMMIT_ResultSetHoldability {
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!");
//Begin Transaction T1
con.setAutoCommit(false);
//ResultSet holdability = CLOSE_CURSORS_AT_COMMIT
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = prepStmt.executeQuery();
// LOGIC........................
//Commit Transaction T1
con.commit();
//CLOSE_CURSORS_AT_COMMIT holdability indicates that open ResultSet
//objects will be closed when the current transaction is committed.
} 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();
}
}
}
}
|
Programs where java.sql.ResultSet is used >
java.sql.Statement
JDBC- Statement example - Execute SELECT query(DML command) using Statement's executeQuery method and ResultSet in java
java.sql.PreparedStatement
JDBC tutorial- PreparedStatement example - Execute SELECT query(DML command) using PreparedStatement's executeQuery method and ResultSet in java
JDBC tutorial - Retrieve IMAGE from database by using PreparedStatement's executeQuery, ResultSet's getBlob method - using BLOB data type - in java
JDBC tutorial- Retrieve FILE from database by using PreparedStatement's executeQuery and ResultSet's getClob method, using CLOB data type - in java
Statement - using getResultSet in java JDBC >