JDBC高级特性

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.