java ResultSet.TYPE_SCROLL_SENSITIVE 的行为

Behaviour of ResultSet.TYPE_SCROLL_SENSITIVE

提问by divesh premdeep

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

我对 aResultSet类型的行为感到困惑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.

1. 我执行一个选择查询，返回一个结果集。我打印出第一行中特定列的值。
2. 然后我执行Thread.sleep(10000)，这会使程序暂停 10 秒。
3. 当程序处于休眠状态时，我手动更新数据库中的同一列（通过 SQL 提示）。
4. 10 秒后，我再次在结果集的第一行打印同一列的值。

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 ResultSetis of type SCROLL_TYPE_SENSITIVE).

在第 4 步中，我希望打印的列值与第 1 步中打印的值不同。但我总是得到相同的值（即使我ResultSet的类型是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"));
}

回答by Adeel Ansari

Am I mis-understanding something here ?

我在这里误解了什么吗？

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

是的。您必须通过SELECT自己启动或调用ResultSet.refreshRow(). 此外，ResultSet.refreshRow()在使用它之前阅读文档，否则你可能会得到意想不到的结果。

The doc states regarding TYPE_SCROLL_SENSITIVE,

文档说明了 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.

TYPE_SCROLL_SENSITIVE

指示 ResultSet 对象的类型的常量，该对象可滚动并且通常对其他人所做的更改敏感。

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.

这仅仅意味着它会对其他人在同一个 ResultSet 对象中所做的更改敏感。要理解这个概念，我建议查看这个官方JDBC 教程：更新表。

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.

有了可滚动的结果集，你就可以移动到你想要改变的行，如果类型是TYPE_SCROLL_SENSITIVE，你可以在改变后获得一行中的新值。

回答by bondkn

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

我认为您正在使用 mysql 作为您的数据库，这是一个已知的错误。

Let me elaborate fully-

让我详细说明——

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

根据 Java 站点上的 Oracle 文档，TYPE_SCROLL_SENSITIVE 用于 2 个目的-

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}

1.Mysql 驱动程序现在可以来回移动 jdbc 结果集的指针（否则只会向前移动），因此基本上启用了滚动{所以现在您可以执行 resultset.previous() 并且指针将返回}

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

2.显示更新的值（内部更改），对数据库所做的。

You are stuck at the 2nd point...

你被困在第二点......

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

看到你的程序不工作，因为你从未使用过 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)

每当使用 jdbc 时，驱动程序都会将默认数量的行提取到显示的缓存中（例如：oracle 默认加载 10 行）

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.

所以 TYPE_SCROLL_SENSITIVE 将只显示下一次缓存重新加载的更新值。就像你在数据库中有 100 行，你更新了所有，但直到那时只提取了 10 行，所以你将随后打印更新的其他 90 行，因为驱动程序将在 9 轮缓存管理中加载这些表。

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 )

为了显式定义要获取的行数（例如，将 oracle 的行数从 10 更改为 1），您可以在创建语句时显式定义 fetchSize()。（但使用缓存无效，最后会降低速度)

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:

验证数据：从 resultSet 打印 setFetchSize，如果它从语句传递到 resultSet 而 Sysout 比获取配置已被保存，如：

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();

如果 sysout 给出“1”作为获取大小，您将看到程序中的动态更新，但如果它给出“0”，则意味着您的数据库不支持 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).

这里是 mysql 的问题，mysql 默认将所有行数提取到 ResultSet 中，因此动态内部更新，不提取动态值。（内部更新是由同一程序的其他线程完成的更新）。

Here is the bug supporting my point on sql bugs:

这是支持我对 sql 错误观点的错误：

sql bugs fetchSize Bug

sql 错误 fetchSize 错误

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

orcale docs TYPE_SCROLL_SENSITIVE example ResultSet5.java

orcale 文档 TYPE_SCROLL_SENSITIVE 示例 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 ();
      }
     }