ResultSet may only be accessed in a forward direction

iwebcode

浏览: 2007669 次
性别:
来自: 杭州

最近访客更多访客>>

u012363178

yefeng_laixi

无为我是谁

firehack

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (2435)

社区版块

存档分类

2013-02 ( 13)
2013-01 ( 57)
2012-12 ( 62)
更多存档...

我遇到的这个问题前提是将SQLServer常用的JDBC换成JTDS造成的

=============下面是转载===========
欲流远之，必固其源泉，虽然现在有了Hibernate等工具，或许我们没太多必要关注JDBC的底层操作，但是工具会不断更新或者过时或者被淘汰，所以Java基础还是最重要的，有了深厚的JDBC功底，相信再学其它ORM都轻车熟路，不费吹灰之力。

I get java.sql.SQLException: "ResultSet may only be accessed in a forward direction" or "ResultSet is read only" when using a scrollable/updateable ResultSet.

There are three possible causes to this (if we exclude not creating the ResultSet with the appropriate type and concurrency in the first place):

The executed query must be a single SELECT statement or a call to a procedure that consists of a single SELECT statement (even a SET or PRINT will cause the resulting ResultSet to be forward only read only). This is a SQL Server limitation and there's not much jTDS can do about it.
The scroll insensitive/updateable combination is not supported by SQL Server, so such a ResultSet is automatically downgraded to scroll insensitive/read-only by the server. Use the scroll sensitive/updateable combination and it should work.
The other possible cause is that the cursor is keyset-based and either the table you are selecting from does not have a unique primary key or that primary key is not included in your SELECT. See the SQL Server Documentation on cursor types for more information.

In both cases if you call Statement.getWarnings() right after calling executeQuery() you'll get a warning about the ResultSet being downgraded. Also, please take a look at our ResultSet support page for some additional information and tips.

JDK5支持用rs.updateRow()直接更新当前行，而我们习惯的用法是

PreparedStatement pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
pstmt.set.....
ResultSet rs=pstmt.executeQuery();
//更新操作
if(rs.next()){
rs.updateString("fieldName","value");
...
rs.updateRow()
}
当然，这在Microsoft的JDBC驱动里面是没有问题的，可是当你用jtds的时候，这种情况就发生变化了，且让我们来参考一下jTDS官方网上的介绍——jTDS supports the following result set types on MS SQL Server（http://jtds.sourceforge.net/resultSets.html）.

JDBC Type SQL Server Cursor Type Server Load Description

TYPE_FORWARD_ONLY	Firehose cursor (direct select) when read-only	Light	Fast, but driver will have to read all data. Not recommended when using multiple result sets. Forward only.
Fast forward-only (static) cursor when read-only and `useCursors=true`	Heavy	Slower than firehose cursors (multiple fetch requests), driver doesn't have to read all data. Forward only.
Forward-only dynamic cursor when updatable	Heavy	Others' updates, deletes and inserts visible. Forward only.
TYPE_SCROLL_INSENSITIVE	Static cursor	Heavy	Only works with read-only concurrency (updatable is downgraded). SQL Server generates a temporary table, so changes made by others are not visible. Scrollable.
TYPE_SCROLL_SENSITIVE	Keyset cursor	Medium	Others' updates or deletes visible, but not others' inserts. Scrollable.
TYPE_SCROLL_SENSITIVE+1	Dynamic cursor	Heavy	Others' updates, deletes and inserts visible. Scrollable.

jTDS supports the following result set concurrencies on MS SQL Server.

JDBC Concurrency SQL Server Concurrency Row Locks Description

CONCUR_READ_ONLY	Read only	No	Read-only.
CONCUR_UPDATABLE	Optimistic concurrency, updatable	No	Row integrity checked using timestamp comparison or, when not available, value comparison (except text and image fields).
CONCUR_UPDATABLE+1	Pessimistic concurrency, updatable	Yes	Row integrity is ensured by locking rows.
CONCUR_UPDATABLE+2	Optimistic concurrency, updatable	No	Row integrity checked using value comparison (except text and image fields).

在这里，我们可以发现一点小小的变化，那就是jTDS的TYPE_SCROLL_INSENSITIVE只支持只读操作（Only works with read-only concurrency (updatable is downgraded)），TYPE_SCROLL_SENSITIVE支持Update操作，但不支持另外的Insert（说明：此Insert指的是新增一条空记录，并在当前记录中填值的情况），而TYPE_SCROLL_SENSITIVE+1就跟MS SQL Server JDBC驱动中的TYPE_SCROLL_INSENSITIVE功能类似了。

基于此，所以我们要将原来的语句：
pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
改成如下形式：
pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
这样，你就可以应用查询，并在结果集rs的当前行直接进行更新操作了，关于ResultSet的更新用法请参照JDK文档