CODEWITHSUNDEEP
javasqloracle-databasejdbc
A_J
A_J

Reputation: 1003

Sensitive Scrollable Resultset not reflecting database changes in Oracle database

Consider the following customer table I have in database:

custId        customerName         emailId
1001            James             jAMES@Xyz

Consider the below lines of code:

 String sql="select t.custId,t.customerName,t.emailId from customer t";    
       Connection connection = DriverManager.getConnection(DBConnectionURL,DBUserName, DBPassword);
        PreparedStatement pStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet scrollableResultSet = pStatement.executeQuery();
            System.out.println("Initial cursor position : "+ scrollableResultSet.getRow()); //line 1
scrollableResultSet.next();
System.out.println("Customer Name : "+ scrollableResultSet.getString("customerName"));
        String sql1 = "UPDATE customer SET customername=? where custid=?";
                    PreparedStatement pStatement1 = connection.prepareStatement(sql1);
                    pStatement1.setString(1, "Ramesh");
                    pStatement1.setInt(2, 1001);
                    int row = pStatement1.executeUpdate();
                    pStatement1.execute("COMMIT");

scrollableResultSet.beforeFirst();
scrollableResultSet.next();
System.out.println("Customer Name : "+ scrollableResultSet.getString("customerName")); //line 2

In the above code, 'line 1' should show the output as "James" and 'line 2' should show the output as 'Ramesh' but both show the output as 'James' only.

Why is the resulset not sensing the changes to the database ?

I referred the following link http://docs.oracle.com/cd/B10501_01/java.920/a96654/resltset.htm

and found that we can use getType() method of the result-set after executing the query to find which type of result-set was used to execute the query but I was surprised to find that it gave me output as 1005 which means I am using 'Scrollable sensitive' result-set.

I also found a note on this link as follows:

http://docs.oracle.com/cd/B10501_01/java.920/a96654/resltset.htm#1023726

Whenever the window is redefined, the N rows in the database corresponding to the rows in the new window are automatically refetched through an implicit call to the refreshRow() method (described in "Refetching Rows"), thereby updating the data throughout the new window. So external updates are not instantaneously visible in a scroll-sensitive result set; they are only visible after the automatic refetches just described.

But I am not able to understand what change I should make in the code with respect to this note.

Any help is appreciated!

Upvotes: 1

Views: 695

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

The refreshRow() method belongs to the class oracle.jdbc.driver.UpdatableResultSet. So, cast your result set to this class, call the method and you should be able to see the updated row:

scrollableResultSet.beforeFirst();
scrollableResultSet.next();
((oracle.jdbc.driver.UpdatableResultSet)scrollableResultSet).refreshRow();
System.out.println("Customer Name : "+ scrollableResultSet.getString("customerName")); //line 2

However, I can only conclude by iterating Oracle's performance warning about using this method:

Because this kind of refetching is not a highly efficient or optimized methodology, there are significant performance concerns. Consider carefully before using scroll-sensitive result sets as currently implemented.

Upvotes: 1

Related Questions