JDBC Type Scroll Insensitive and Sensitive

Question

When going through Java JDBC ResultSet Types, there is two scroll type TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE, which I understood. But when I go to practical implementation, I didn't see the effect. Below is the code :

package com.jdbc.resultsettypeandconcurrency;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TypeInSensitiveConcurUpdate {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;

        try {
            System.out.println("loading the driver.....");
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("Driver loaded");
            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "user", "pass");
            if(con!=null) {
                System.out.println("Connected to database");

            } else {
                System.out.println("Could not Get Connection");
            }

            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
            ResultSet rs = stmt.executeQuery(query);
            System.out.println();
            int cnt = 1;
            while(rs.next()) {
                System.out.print(rs.getString("COF_NAME")+", ");
                System.out.print(rs.getInt("SUP_ID")+", ");
                System.out.print(rs.getFloat("PRICE")+", ");
                System.out.print(rs.getInt("SALES")+", ");
                System.out.print(rs.getInt("TOTAL")+"\n");
                if(cnt == 2){
                    try {
                        Thread.sleep(20 * 1000);/**LINE:39*/
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
                cnt++;
            }
            System.out.println();

        } catch(ClassNotFoundException e) {
            System.out.println("ClassNotFoundException : Driver Class not found");
            System.out.println(e.getMessage());

        } catch(SQLException e) {
            System.out.println("SQL Error "+e.getMessage());
            System.out.println(e.getErrorCode());

        } finally {
            if(stmt!=null) {
               stmt.close();
            }
            if(con!=null) {
               con.close();
            }
            System.out.println("All Connection closed");
        }
    }
}

When program reaches at LINE 39, I update the DB from backend for a record. For TYPE_SCROLL_INSENSITIVE it does not show the updated record which it should do but for TYPE_SCROLL_SENSITIVE does not doing the desired behavior. It must show the updated record but not showing. Can anyone tell why is it so ?

I read somewhere when googled(not Java doc or JLS) that ODBC thin driver, OCI driver support INSENSITIVE ResultSet object and not sensitive. Is it so? If yes why and which driver supports both? If no then where I am going wrong.

I went through the link, but didn't point to my question. Any Suggestion will be appreciated.

EDIT: Added these lines to check the support

DatabaseMetaData meta = con.getMetaData();
System.out.println("{TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE} -> "+
                meta.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));
System.out.println("{TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE} -> "+
                    meta.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));
System.out.println("{TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY} -> "+
                    meta.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));

Below is the result I got:

{TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE} -> true

{TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE} -> true

{TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY} -> true

Answer 1

As with other features that doesn't work you must read the documentation before using them.

The importatant thing is the notion of the window

The Oracle implementation of scroll-sensitive result sets involves the concept of a window, with a window size that is based on the fetch size. The window size affects how often rows are updated in the result set.

So to observe the change of every single row, you must set the fetch size to 1.

Note that it is not enought to set the fetch size for the resultSet, because the default fetch size is 10 and the change is valid only for the 11th and the subsequent rows.

Therefore the fetch size must be set on the prepareStatement:

 def stmt = con.prepareStatement("""select id, val from test
 where  id between ? and ?  order by id""", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
 stmt.setFetchSize(1)
 // set bind variables and execute statement

Now on each call of rs.next() a new window is opened, which leads to an internal call of refreshRow

which gets the current values from the database.

Note that this bahaviour is performed ony for TYPE_SCROLL_SENSITIVE for TYPE_SCROLL_INSENSITIVE no refreshRow is called, so you see the constant data as of the initial query even is you switch the window. You may call refreshRow explicitely to see the same effect.

Technically the functionality is implemented using two cursors. The first one corresponds to the used query, only adding the ROWID column.

 select rowid as "__Oracle_JDBC_internal_ROWID__", id, val from test
 where  id between :1  and :2   order by id

The second cursor called on each window switch (i.e. for fetch size = 1 for each row fetched) simple outer joins the the saved rowid with the query from the first cursor to refetch the current data.

WITH "__JDBC_ROWIDS__" AS (SELECT COLUMN_VALUE ID, ROWNUM NUM FROM TABLE(:1 ))
SELECT "__JDBC_ORIGINAL__".*
FROM (select rowid as "__Oracle_JDBC_internal_ROWID__", id, val from test
where  id between :2  and :3   order by id) "__JDBC_ORIGINAL__", "__JDBC_ROWIDS__"
WHERE "__JDBC_ORIGINAL__"."__Oracle_JDBC_internal_ROWID__"(+) = "__JDBC_ROWIDS__".ID
ORDER BY "__JDBC_ROWIDS__".NUM 

There are similar question out there, but none of them realy explains the problem, so I do not mark this question as duplicated:

Behaviour of ResultSet.TYPE_SCROLL_SENSITIVE

JDBC ResultSet Type_Scroll_Sensitive

JDBC result set type scroll sensitive

The short answer is that the default fetch size you used is to high to observe an update of a single row.

The test were done on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 DriverVersion 12.2.0.1.0