追求無止境

1、可滾動的結果集（Scrollable Result Sets）

（1）創建可滾動的結果集：

  Statement stmt = con.createStatement(
                       ResultSet.TYPE_SCROLL_INSENSITIVE, 
                       ResultSet.CONCUR_READ_ONLY);

ResultSet.TYPE_FORWARD_ONLY:結果集是不能滾動的；他的游標只能向前移動；

ResultSet.TYPE_SCROLL_INSENSITIVE:結果是可滾動的；游標可以向前也可以后退。也可以移動到一個絕對位置。

ResultSet.TYPE_SCROLL_SENSITIVE:結果是可滾動的；游標可以向前也可以后退。也可以移動到一個絕對位置。

ResultSet.CONCUR_READ_ONLY：結果集是只讀的。

ResultSet.ResultSet.CONCUR_UPDATABLE:結果集是可以更新的。

  ResultSet srs = stmt.executeQuery("SELECT COF_NAME, 
                       PRICE FROM COFFEES");

盡管我們可以在這里設置創建的是可滾動結果集，但是如果廠商的JDBC實現不支持，我們獲取到的結果將不具有可滾動屬性。

可以使用ResultSet.getType()方法來獲取是否支持滾動：

 int type = rs.getType();

The variable type will be one of the following:

1003 to indicate ResultSet.TYPE_FORWARD_ONLY

1004 to indicate ResultSet.TYPE_SCROLL_INSENSITIVE

1005 to indicate ResultSet.TYPE_SCROLL_SENSITIVE

TYPE_SCROLL_INSENSITIVE和TYPE_SCROLL_SENSITIVE的主要區別是在如果發生改變他們的敏感度。前一個將不會很敏感后一個則會。

（2）移動游標，使用以下方法可以移動游標：

rs.next();

rs.previous();

rs.absolute();

rs.relative();

rs.first();

rs.last();

rs.beforeFirst();

rs.afterLast();

使用rs.getRow()獲取游標當前行。

rs.isAfterLast();

rs.isBeforeFirst();

rs.isLast();

rs.isFirst();

rs.hasNext();

等等方法。

2、更新結果集

（1）創建可以更新的結果集

Statement stmt = con.createStatement(
               ResultSet.TYPE_SCROLL_SENSITIVE, 
               ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
               "SELECT COF_NAME, 
               PRICE FROM COFFEES");

在JDBC 2.0中，我們可以向可以更新的結果集中插入行或刪除行，或者修改其中的行。

下面的方法用于判斷結果集是否可以更新：

  int concurrency = uprs.getConcurrency();

The variable concurrency will be one of the following:

1007 to indicate ResultSet.CONCUR_READ_ONLY

1008 to indicate ResultSet.CONCUR_UPDATABLE

（2）更新結果集

JDBC 1.0中可以這樣更新： 

 stmt.executeUpdate(
    "UPDATE COFFEES SET PRICE = 10.99 " +
    "WHERE COF_NAME = 'French_Roast_Decaf'");

在JDBC2.0中。則可以：

 uprs.last();

  uprs.updateFloat("PRICE", 10.99f);

uprs.updateRow();

在移動游標前，必須先調用updateRow方法。否則更新信息會丟失。調用cancelRowUpdates可以取消對行的更新。

（3）向結果集中插入或者刪除行

  Connection con = DriverManager.getConnection(
                      "jdbc:mySubprotocol:mySubName");
  Statement stmt = con.createStatement(
                       ResultSet.TYPE_SCROLL_SENSITIVE, 
                       ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
                      "SELECT * FROM COFFEES");

  uprs.moveToInsertRow();

  uprs.updateString("COF_NAME", "Kona");
  uprs.updateInt("SUP_ID", 150);
  uprs.updateFloat("PRICE", 10.99f);
  uprs.updateInt("SALES", 0);
  uprs.updateInt("TOTAL", 0);
  
  uprs.insertRow();

在移動游標前，必須要先調用insertRow否則插入的信息將丟失。

uprs.absolute(4);
uprs.deleteRow();

刪除行。

（4）查看結果集中的變化（其實就是說了一個意思，用TYPE_SCROLL_SENSITIVE對數據很敏感，一旦數據變化就會反映在ResultSet中）

Result sets vary greatly in their ability to reflect changes made in their underlying data. If you modify data in a ResultSet object, the change will always be visible if you close it and then reopen it during a transaction. In other words, if you re-execute the same query after changes have been made, you will produce a new result set based on the new data in the target table. This new result set will naturally reflect changes you made earlier. You will also see changes made by others when you reopen a result set if your transaction isolation level makes them visible.

So when can you see visible changes you or others made while the ResultSet object is still open? (Generally, you will be most interested in the changes made by others because you know what changes you made yourself.) The answer depends on the type of ResultSet object you have.

With a ResultSet object that is TYPE_SCROLL_SENSITIVE, you can always see visible updates made to existing column values. You may see inserted and deleted rows, but the only way to be sure is to use DatabaseMetaData methods that return this information. ("New JDBC 2.0 Core API Features" on page 371 explains how to ascertain the visibility of changes.)

You can, to some extent, regulate what changes are visible by raising or lowering the transaction isolation level for your connection with the database. For example, the following line of code, wherecon is an active Connection object, sets the connection's isolation level to TRANSACTION_READ_COMMITTED:

  con.setTransactionIsolation(
              Connection.TRANSACTION_READ_COMMITTED);

With this isolation level, a TYPE_SCROLL_SENSITIVE result set will not show any changes before they are committed, but it can show changes that may have other consistency problems. To allow fewer data inconsistencies, you could raise the transaction isolation level to TRANSACTION_REPEATABLE_READ. The problem is that, in most cases, the higher the isolation level, the poorer the performance is likely to be. And, as is always true of JDBC drivers, you are limited to the levels your driver actually provides. Many programmers find that the best choice is generally to use their database's default transaction isolation level. You can get the default with the following line of code, where con is a newly-created connection:

  int level = con.getTransactionIsolation();

The explanation of Connection fields, beginning on page 347, gives the transaction isolation levels and their meanings.

If you want more information about the visibility of changes and transaction isolation levels, see "What Is Visible to Transactions" on page 597.

In a ResultSet object that is TYPE_SCROLL_INSENSITIVE, you cannot see changes made to it by others while it is still open, but you may be able to see your own changes with some implementations. This is the type of ResultSet object to use if you want a consistent view of data and do not want to see changes made by others.