Behaviour of ResultSet.TYPE_SCROLL_SENSITIVE

Question

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 misunderstanding 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"));
}

Answer 1

I Think you are using mysql as your db,and this is a known bug.

Let me elaborate fully-

As per Oracle documentation on the java site TYPE_SCROLL_SENSITIVE is used for 2 purposes-

1.Mysql driver can now move the jdbc result set's pointer to and fro (which otherwise just goes in the forward direction),so basically scrolling is enabled {so now you can do resultset.previous() and the pointer will go back}

2.To show updated values(the internal changes),made to the database.

You are stuck at the 2nd point...

See your program is not working,because you never used the concept of fetchSize();

whenever using jdbc,the driver fetches a default number of rows into the cache that is displayed(for ex:oracle loads 10 rows by default)

so TYPE_SCROLL_SENSITIVE will display the updated value of the next cache reload only. it is like you have 100 rows in the DB,you updated all,but till then only 10 rows were fetched,so you will get the other 90 rows updated printed subsequently ,as the driver will load these tables in 9 rounds of cache management.

for explicitly defining the number of rows to be fetched(for example changing the no. of rows from 10 to 1 for oracle) you can explicitly define the fetchSize() while creating statement.(but using cache ineffectively,at the end slows the speed )

so while initializing statement as:

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

add a line as:

stmt.setFetchSize(1); //1 is the no. of rows that will be fetched.

create resultSet as:

ResultSet rset = stmt.executeQuery("select * from persons");

to verify the data: print setFetchSize from resultSet,if it passes from statement to resultSet while Sysout than the fetching configuration has been saved,as:

System.out.println("fetch size: " + resultSet.getFetchSize());

if the sysout gives '1' as fetch size,you will see your dynamic updates from the program as it is, but if it gives '0' that means your DB doesnot support dynamic initialization of fetchSize();

Here is the problem with mysql,mysql by default fetches all the number of rows into the ResultSet,and thus the dynamic internal update,does not fetch the dynamic values. (internal update is the update done by some other thread of the same program).

Here is the bug supporting my point on sql bugs:

sql bugs fetchSize Bug

if you use oracle,this java doc copied from oracle documentation will just work fine:

orcale docs TYPE_SCROLL_SENSITIVE example ResultSet5.java

import java.sql.*;

public class ResultSet5
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
    DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");
    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, 
                                     ResultSet.CONCUR_UPDATABLE);
    // Set the statement fetch size to 1
    stmt.setFetchSize (1);
    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
    // List the result set's type, concurrency type, ..., etc
    showProperty (rset);


// List the query result 
System.out.println ("List ENO, ENAME and SAL from the EMP table: ");
while (rset.next())
{
  System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                      rset.getInt(3));
}
System.out.println ();

// Do some changes outside the result set
doSomeChanges (conn);

// Place the cursor right before the first row
rset.beforeFirst ();

// List the employee information again
System.out.println ("List ENO, ENAME and SAL again: ");
while (rset.next())
{
  // We expect to see the changes made in "doSomeChanges()"
  System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                      rset.getInt(3));
}

// Close the RseultSet
rset.close();

// Close the Statement
stmt.close();

// Cleanup
cleanup(conn);

// Close the connection
conn.close();   
  }

  /**
   * Update the EMP table.
   */ 
  public static void doSomeChanges (Connection conn)throws SQLException
  {
    System.out.println ("Update the employee salary outside the result set\n");

    Statement otherStmt = conn.createStatement ();
    otherStmt.execute ("update emp set sal = sal + 500");
    otherStmt.execute ("commit");
    otherStmt.close ();
  }

  /**
   * Show the result set properties like type, concurrency type, fetch 
   * size,..., etc.
   */
  public static void showProperty (ResultSet rset) throws SQLException
  {
    // Verify the result set type
switch (rset.getType())
{
  case ResultSet.TYPE_FORWARD_ONLY:
    System.out.println ("Result set type: TYPE_FORWARD_ONLY");
    break;
  case ResultSet.TYPE_SCROLL_INSENSITIVE:
    System.out.println ("Result set type: TYPE_SCROLL_INSENSITIVE");
    break;
  case ResultSet.TYPE_SCROLL_SENSITIVE:
    System.out.println ("Result set type: TYPE_SCROLL_SENSITIVE");
    break;
  default: 
    System.out.println ("Invalid type");
    break;
}

// Verify the result set concurrency
switch (rset.getConcurrency())
{
  case ResultSet.CONCUR_UPDATABLE:
    System.out.println 
               ("Result set concurrency: ResultSet.CONCUR_UPDATABLE");
    break;
  case ResultSet.CONCUR_READ_ONLY:
    System.out.println 
               ("Result set concurrency: ResultSet.CONCUR_READ_ONLY");
    break;
  default: 
    System.out.println ("Invalid type");
    break;
}
// Verify the fetch size
System.out.println ("fetch size: "+rset.getFetchSize ());
System.out.println ();
  }

  /* Generic cleanup.*/
      public static void cleanup (Connection conn) throws SQLException
      {
        Statement stmt = conn.createStatement ();
        stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
        stmt.execute ("COMMIT");
         stmt.close ();
      }
     }