不能使用 resultSet.setFetchDirection(ResultSet.TYPE_SCROLL_SENSITIVE) 和 spring jdbc DaoSupport with Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2068951/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can not use resultSet.setFetchDirection(ResultSet.TYPE_SCROLL_SENSITIVE) with spring jdbc DaoSupport with Oracle
提问by m0z4rt
I want to use scrollable resultset, so when I use two lines of code:
我想使用可滚动的结果集，所以当我使用两行代码时：
rs.setFetchDirection(ResultSet.TYPE_SCROLL_SENSITIVE);
rs.absolute(12);
in my DAOimpl, I get exception, plz help to solve them, thank in advance.
在我的 DAOimpl 中，我遇到了异常，请帮助解决它们，提前致谢。
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Component;
@Component
public class MyDAOimpl extends JdbcDaoSupport implements
MyDAO {
public List<User> getList(final String where) throws Exception {
return (List) getJdbcTemplate().execute(
"{call PKG_USER.getUser(?,?)}",
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException {
cs.setString(1, where);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(6);
rs.setFetchDirection(ResultSet.TYPE_SCROLL_SENSITIVE);
rs.absolute(12);
List<User> list = new ArrayList<User>();
while (rs.next()) {
User user = new User(
rs.getString(1),
rs.getString(2),
rs.getString(3));
list.add(user);
}
return list;
}
});
}
}
this is exception
这是例外
java.sql.SQLException: Invalid argument(s) in call: setFetchDirection
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
oracle.jdbc.driver.BaseResultSet.setFetchDirection(BaseResultSet.java:128)
//////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////// ////////////////////////////////////////
where I change like the following, I didn't get any result, normally, my procedure return 100 users:
在我更改如下的地方，我没有得到任何结果，通常，我的程序返回 100 个用户：
return (List) getJdbcTemplate().execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(
Connection connection) throws SQLException {
return connection.prepareCall(
"{call PKG_USER.getUser(?,?)}",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.TYPE_SCROLL_INSENSITIVE);
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.setString(1, where);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(6);
//////not run////
rs.absolute(12);
////////////////
List<User> list = new ArrayList<User>();
while (rs.next())
{
List<User> list = new ArrayList<User>();
while (rs.next()) {
User user = new User(
rs.getString(1),
rs.getString(2),
rs.getString(3));
list.add(user);
}
return list;
}
});
回答by Pascal Thivent
First, ResultSet.TYPE_SCROLL_SENSITIVEis a constant indicating a result set typeand is certainly not a valid argument for setFetchDirectionwhich expects a fecth direction. Quoting the parameter section of the javadoc of ResultSet#setFetchDirection(int direction):
首先，ResultSet.TYPE_SCROLL_SENSITIVE是一个常数，表示结果集类型，当然不是一个setFetchDirection需要fecth direction的有效参数。引用 javadoc 的参数部分ResultSet#setFetchDirection(int direction)：
direction- anintspecifying the suggested fetch direction; one ofResultSet.FETCH_FORWARD,ResultSet.FETCH_REVERSE, orResultSet.FETCH_UNKNOWN
direction-int指定建议的获取方向；中的一个ResultSet.FETCH_FORWARD，ResultSet.FETCH_REVERSE或ResultSet.FETCH_UNKNOWN
Hence the exception and the message "Invalid argument(s) in call: setFetchDirection".
因此出现异常和消息“调用中的无效参数：setFetchDirection”。
And BTW, according to Oracle's "JDBC Developer's Guide and Reference" (all versions are available from http://tahiti.oracle.com/) in Processing a Scrollable Result Set:
顺便说一句，根据 Oracle在处理可滚动结果集中的“ JDBC 开发人员指南和参考”（所有版本均可从http://tahiti.oracle.com/ 获得）：
Presetting the Fetch Direction
The JDBC 2.0 standard allows the ability to pre-specify the direction, known as the fetch direction, for use in processing a result set. This allows the JDBC driver to optimize its processing. The following result set methods are specified:
void setFetchDirection(int direction) throws SQLException*int getFetchDirection() throws SQLExceptionThe Oracle JDBC drivers support only the forward preset value, which you can specify by inputting the
ResultSet.FETCH_FORWARDstatic constant value.The values
ResultSet.FETCH_REVERSEandResultSet.FETCH_UNKNOWNare not supported. Attempting to specify them causes a SQL warning, and the settings are ignored.
预置取方向
JDBC 2.0 标准允许预先指定方向（称为提取方向）以用于处理结果集。这允许 JDBC 驱动程序优化其处理。指定了以下结果集方法：
void setFetchDirection(int direction) throws SQLException*int getFetchDirection() throws SQLExceptionOracle JDBC 驱动程序仅支持前向预设值，您可以通过输入
ResultSet.FETCH_FORWARD静态常量值来指定该 值。值
ResultSet.FETCH_REVERSE和ResultSet.FETCH_UNKNOWN不受支持。尝试指定它们会导致 SQL 警告，并且这些设置将被忽略。
This is also mentioned in the readmeof the Oracle Database 11g Release 2 JDBC Drivers(the ultimate version at the time of writing this):
Oracle Database 11g 第 2 版 JDBC 驱动程序（撰写本文时的最终版本）的自述文件中也提到了这一点：
The scrollable result set implementation has the following limitation:
- setFetchDirection() on ScrollableResultSet does not do anything.
可滚动结果集实现具有以下限制：
- ScrollableResultSet 上的 setFetchDirection() 不做任何事情。
But all this was a kind of side note, using setFetchDiretionis simply not the way to get a scrollable result set.
但所有这些都是一种旁注，使用setFetchDiretion根本不是获得可滚动结果集的方法。
To create a scrollable result set with Spring's JdbcTemplate, you should actually use the method execute(CallableStatementCreator csc, CallableStatementCallback action)with a custom CallableStatementCreatorimplementation. In this implementation, use the method Connection.prepareCall(String sql, int resultSetType, int resultSetConcurrency)to create a CallableStatementthat will produce ResultSetobjects with the given type and concurrency. Finally, call rs.absolute().
要使用 Spring 的 来创建可滚动的结果集JdbcTemplate，您实际上应该使用execute(CallableStatementCreator csc, CallableStatementCallback action)带有自定义CallableStatementCreator实现的方法。在这个实现中，使用 方法Connection.prepareCall(String sql, int resultSetType, int resultSetConcurrency)创建一个CallableStatement将产生ResultSet具有给定类型和并发性的对象。最后，调用rs.absolute()。
UPDATE:There is a problem in the connection.prepareCall()call, the third parameter should be a concurrency type (either ResultSet.CONCUR_READ_ONLYor ResultSet.CONCUR_UPDATABLE). Try this:
更新：有一个在一个问题connection.prepareCall()呼叫，第三个参数应该是一个并发类型（ResultSet.CONCUR_READ_ONLY或ResultSet.CONCUR_UPDATABLE）。尝试这个：
return connection.prepareCall(
"{call PKG_USER.getUser(?,?)}",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
