Behaviour of ResultSet.TYPE_SCROLL_SENSITIVE

Question

Hi,

I am confused about the behaviour of a ResultSet that is of type TYPE_SCROLL_SENSITIVE.

My understanding of this is -
1.) I execute a select query that returns me a result set. I print out the value of a particular column in the first row.
2.) I then execute Thread.sleep(10000), which halts the program for 10 seconds.
3.) While the program is sleeping, I manually do an update to the same column in the DB (through the SQL prompt).
4.) After 10 seconds, I again print the value of the same column in the first row of the result set.

In Step 4, I expect the printed column value to be different from the value printed in step 1. But I always get the same value (even if my ResultSet is of type SCROLL_TYPE_SENSITIVE).

Am I mis-understanding something here ?

Below is the code I use.

private void doStuff() throws Exception
{
    final String query = "select * from suppliers where sup_id=420";

    Statement stmt = this.con.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet rs = stmt.executeQuery(query);

    rs.next();

    System.out.println("City : " + rs.getString("city"));

    Thread.sleep(10000); // While this executes, I do a manual update !

    System.out.println("City : " + rs.getString("city"));
}

Thanks for your help in advance !

Answer 1

Am I mis-understanding something here ?

Yes. You must fetch again to get the latest state of the table, either by firing up a SELECT yourself, or calling ResultSet.refreshRow(). Moreover, read the docs of ResultSet.refreshRow() before using it, otherwise you might get unexpected results.

The doc states regarding TYPE_SCROLL_SENSITIVE,

TYPE_SCROLL_SENSITIVE

The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes made by others.

Which merely means that it would be sensitive to the changes made by others in the same ResultSet object. To understand the concept, I would advise to look at this official JDBC Tutorial: Updating Tables.

Okay, editing my post to include the specific line from the original tutorial,

With a scrollable result set, you can move to rows you want to change, and if the type is TYPE_SCROLL_SENSITIVE, you can get the new value in a row after you have changed it.