ResultSet参数设置和更新

ResultSet中保存了 query查询语句执行的结果，因此被用来扫描（检索，定位）数据。不过，也可以用来导航数据和进行个别的更新。接下来我们来看下细节：

一、导航 ResultSet数据

只要数据库允许这些操作，JDBC提供了下面几个方法都可以用来导航ResultSet中数据。

ResultSet’s Methods for Navigating Rows

boolean next();

boolean previous();

boolean first();

boolean last();

void beforeFirst();

void afterLast();

boolean relative(int n);

boolean absolute(int n);

boolean next()游标位置后移一个，如果当前是最后一行了，那么返回false

boolean previous()游标位置前移一个，如果当前是第一行，则返回false

oolean first() 游标位置返回第一行，如果不存在（查询结果为空），那么返回false

boolean last()游标位置返回最后一行，如果不存在（查询结果为空），那么返回fals

void beforeFirst()返回到最开始，它的下一行是第一行数据。

void afterLast()返回到最后，它的上一行是最后一行数据

boolean relative(int n) 以当前位置为基准，跳n个位置，如果n为正那么往后移动n行，如果为负那往前移动n行。

boolean absolute(int n)为正，则以第一行为基准，跳到第n行，为负，则从最后一行往前跳n行.

二、ResultSet的三种类型参数

缺省情况下创建的ResultSet 是一种只能访问一次（one-time-through），只能向前访问（forward-only），只读的对象。您只能访问数据一次，如果再次需要该数据，必须重新查询数据库。

ResultSet.TYPE_SCROLL_SENSITIVE

双向滚动，并及时跟踪数据库的更新,以便更改ResultSet中的数据。允许在记录中定位。这种类型受到其他用户所作更改的影响。如果用户在执行完查询之后删除一个记录，那个记录将从 ResultSet 中消失。类似的，对数据值的更改也将反映在 ResultSet 中。(其它人对记录的修改会反应到你打开的记录集   敏感)

ResultSet.TYPE_SCROLL_INSENSITIVE

双向滚动，但不及时更新，就是如果数据库里的数据修改过，并不在ResultSet中反应出来。允许在列表中向前或向后移动，甚至可以进行特定定位，例如移至列表中的第四个记录或者从当前位置向后移动两个记录。不会受到其他用户对该数据库所作更改的影响。(其它人对记录的修改不会反应到你打开的记录集   不敏感)

TYPE_FORWORD_ONLY

只可向前滚动。缺省类型,不会受到其他用户对该数据库所作更改的影响。只允许向前访问一次，并且不会受到其他用户对该数据库所作更改的影响。

.
三、ResultSet 和事务处理参数

ResultSet 一般会被关闭，在事务的commit 或者 rollback被执行后.但是有时候你要保留ResultSet继续被使用怎么办？看下面参数:

HOLD_CURSORS_OVER_COMMIT: ResultSet的数据仍然可以被存取在commit 或者 rollback之后.
CLOSE_CURSORS_AT_COMMIT: ResultSet的数据被抛弃在 commits 或者 rollbacks执行后.
四、 并发性参数

ResultSet 的数据可以被更新的，为了达到这个目的要做的一件事情就是和数据库保持连接，然后就是使用一些锁机制在更新期间保护数据。并发等级参数有下面两个:

CONCUR_READ_ONLY: 不允许更新
CONCUR_UPDATABLE: 允许并发同步更新数据
五、参数的设置方法：

java.sql.Connection 在使用createStatement(), prepareStatement(), 和 prepareCall() 方法的时候设置参数:

Statement createStatement(int resultSetType, int concurrencyLevel)

Statement createStatement(int resultSetType, int concurrencyLevel,

 int resultSetHoldability)

PreparedStatement prepareStatement(String sql, int resultSetType,

 int concurrencyLevel)

PreparedStatement prepareStatement(String sql, int resultSetType,

 int concurrencyLevel, int resultSetHoldability)

CallableStatement prepareCall(String sql, int resultSetType, int concurrencyLevel)

CallableStatement prepareCall(String sql, int resultSetType,

 int concurrencyLevel, int resultSetHoldability)

六、不使用游标更新数据

Listing 7-8: Updating a ResultSet without Using Cursors

 

// updating resultsets

import java.sql.*;

class SimpleExample

{

         public static void main(String args[])

         {

                 String url = "jdbc:odbc:mysource";

                 try

                 {

                          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                          Connection myConnection =

                                  DriverManager.getConnection(url, "javauser",

                                  "hotjava");

                          Statement stmt = myConnection.createStatement(

ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

                          ResultSet rs = stmt.executeQuery("SELECT phone FROM

                          employees WHERE location = ‘Paris’");

                          while (rs.next()) {

                                  String phone = rs.getString(1);

                                  if (!phone.equals("")) {

                                           // add a telephone prefix

                                           rs.updateString("phone", "+31 " +

                                           phone);

                                           rs.updateRow();

                                  }

                          }

                          rs.close();

                          stmt.close();

                          myConnection.close();

                 }

                 catch(java.lang.Exception ex)

                 {

                          ex.printStackTrace();

                 }

         }

}

也可以删除更新数据。

It is also possible to delete rows and insert rows using the ResultSet interface. Deleting a row is performed with ResultSet.deleteRow(). The current position within the ResultSet will be set after the row just deleted. Inserting a row is performed with ResultSet.moveToInsertRow(), which actually creates a logical row in the result set. Using the updateXXX() methods on this row sets values for each column in that row. ResultSet.insertRow() is then used to physically send that row to the database.

七、使用游标更新数据

使用游标的条件：

有SELECT语句
有一个名字
有一个位置
根据游标的名字来更新和删除ResultSet中的数据
游标机制 Figure 7-1.见附件

Figure 7-1: The cursor mechanism.

怎么样使用游标

void ResultSet.setCursorName(String name);

String ResultSet.getCursorName();

boolean DatabaseMetaData.supportsPositionedDelete();

boolean DatabaseMetaData.supportsPositionedUpdate();

supportsPositionedDelete()：如果支持位置的delete语句，那么返回true

supportsPositionedUpdate()：如果支持位置的update语句，那么返回true.

例子：

Listing 7-9: A Positioned Delete

 

// cursors: positioned delete

import java.sql.*;

class SimpleExample

{

        public static void main(String args[])

        {

                String url = "jdbc:odbc:mysource";

                try

                {

                         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                         Connection myConnection =

                         DriverManager.getConnection(url, "javauser", "hotjava");

                         Statement firstStmt = myConnection.createStatement();

                         Statement secondStmt = myConnection.createStatement();

                         ResultSet rs = firstStmt.executeQuery(

                         "SELECT * FROM employees FOR UPDATE");

                         String cursor = rs.getCursorName();

                         // scan the resultset

                         while (rs.next())

                         {

                                 String phone = rs.getString("phone");

                                 // activate positioned delete

                                 // for employees outside of Belgium and France

                                 if (!phone.startsWith("+31") ||

                                 !phone.startsWith("+32"))

                                 {

                                          secondStmt.executeUpdate(

                                          "DELETE employees WHERE CURRENT OF " +

                                          cursor);

                                 }

                         }

                         rs.close();

                         firstStmt.close();

                         secondStmt.close();

                          myConnection.close();

                 }

                 catch(java.lang.Exception ex)

                 {

                          ex.printStackTrace();

                 }

         }

}

 

 
 

Listing 7-10: A Positioned Update

 

// cursors: positioned update

import java.sql.*;

class SimpleExample

{

        public static void main(String args[])

        {

                String url = "jdbc:odbc:mysource";

                try

                {

                         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                         Connection myConnection =

                         DriverManager.getConnection(url,

                         "javauser", "hotjava");

                         Statement firstStmt = myConnection.createStatement();

                         Statement secondStmt = myConnection.createStatement();

                         ResultSet rs = firstStmt.executeQuery("SELECT * FROM

                         employees FOR UPDATE");

                         String cursor = rs.getCursorName();

                         // scan the resultset

                         while (rs.next())

                         {

                                 String phone = rs.getString("phone");

                                 // activate positioned update

                                 // to convert salary to EURO currency

                                 if (phone.startsWith("+31"))

                                 {

                                          secondStmt.executeUpdate(

                                          "UPDATE employees SET salary = salary /

                                          6.666 WHERE CURRENT OF " + cursor);

                                 }

                                 else if (phone.startsWith("+32"))

                                 {

                                          secondStmt.executeUpdate(

                                          "UPDATE employees SET salary = salary /

                                          40.000 WHERE CURRENT OF " + cursor);

                                 }

                         }

                         rs.close();

                         firstStmt.close();

                         secondStmt.close();

                         myConnection.close();

                }

                catch(java.lang.Exception ex)

                {

                         ex.printStackTrace();

                }

        }

}