错误描述:
java.sql.SQLException: ResultSet may only be accessed in a forward direction.
错误触发点:
分页时setFirstResult(n),n为非0时提示此错误
平台:
JDK1.7 + TOMCAT7.0 + Eclipse
框架:
Spring3.1 + JPA2.0(Hibernate4.0实现) + Struts2.2
主要错误栈信息:
08:47:21,050 WARN SqlExceptionHelper:3629 - SQL Error: 0, SQLState: 24000
08:47:21,051 ERROR SqlExceptionHelper:3574 - ResultSet may only be accessed in a forward direction.
08:47:21,073 DEBUG AbstractEntityManagerImpl:3915 - Mark transaction for rollback
08:47:21,073 DEBUG AbstractTransactionImpl:3554 - rolling back
08:47:21,075 DEBUG JdbcTransaction:3554 - rolled JDBC Connection
08:47:21,075 DEBUG JdbcTransaction:3554 - re-enabling autocommit
08:47:21,076 INFO SystemExceptionInterceptor:52 - org.hibernate.exception.GenericJDBCException: ResultSet may only be accessed in a forward direction.
08:47:21,077 ERROR SystemExceptionInterceptor:53 - javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: ResultSet may only be accessed in a forward direction.
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: ResultSet may only be accessed in a forward direction.
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1347)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1280)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261)
at com.unytech.project.service.base.BaseDao.findPagingData(BaseDao.java:144)
at com.unytech.project.service.business.baseinfo.impl.AdinfobServiceImpl.getAdinfobXZ(AdinfobServiceImpl.java:28)
at com.unytech.project.service.business.baseinfo.impl.AdinfobServiceImpl$$FastClassByCGLIB$$aaec3e9e.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
错误产生条件:
1、你使用了MSSQLServer 数据库
2、你使用了jtds驱动(我用的是v1.2.5)
错误产生的主要原因:
小子,你的数据库方言写错了,看看你自己的,是不是写成了 <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
那就对了,你不错谁错啊,jtds和SQLServer自己的驱动还是有些差别的,哈哈我也搞错了,不过我找到了正确的答案,如果用SQLServer2008数据库和jtds驱动,在Hibernate中数据库方言就应该这样写:<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>,是 org.hibernate.dialect.SQLServer2008Dialect 而不是 org.hibernate.dialect.SQLServerDialect ,记住了哦!本人也是经过千辛万苦,所编网络,问遍Q群都没人知道,后来在一个外国网站的帖子上看到的,地址:https://forum.hibernate.org/viewtopic.php?p=2452163
错误不止一种情况:
你看到这里如果你的错误已经解决了,那么你可以不用继续往下看,当然如果你有闲心也可以看一看。
这个错误还可能是另一种情况导致的,也就是用JDBC而不是用连接池操作数据库时。
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()
- }
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官方网上的介绍--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 pagefor some additional information and tips.
关于jtds对MSSQLServer的结果集和游标的支持情况请看--jTDS supports the following result set types on MS SQL Server.:http://jtds.sourceforge.net/resultSets.html
在这里,我们可以发现一点小小的变化,那就是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文档。
第三种情况:
你的setFirstResult(n) n 为小于零的值,这在MS SQL Server 中是不允许的。
总结:
其实这个问题困扰了我好长时间,谷歌百度了好多网页都有解决,现在想来遇到问题时应该不错误每一个细节,其实<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>这个地方我以前改过,应该是把 org.hibernate.dialect.SQLServer2008Dialect 改成了org.hibernate.dialect.SQLServerDialect 但是当时不知道是什么原因,不知道这两者的区别才导致今天问题的产生。当然关于这个问题的更深入的原因就有待大家自己去研究了,此文只是解决应用性问题,不足之处请指正。
参考文档:
http://hi.baidu.com/hitcser/blog/item/63b2b8b13d200850092302c1.html