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.
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:
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;
}
});
First, ResultSet.TYPE_SCROLL_SENSITIVE
is a constant indicating a result set type and is certainly not a valid argument for setFetchDirection
which expects a fecth direction. Quoting the parameter section of the javadoc of ResultSet#setFetchDirection(int direction)
:
direction
- anint
specifying the suggested fetch direction; one ofResultSet.FETCH_FORWARD
,ResultSet.FETCH_REVERSE
, orResultSet.FETCH_UNKNOWN
Hence the exception and the message "Invalid argument(s) in call: 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:
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 SQLException
The Oracle JDBC drivers support only the forward preset value, which you can specify by inputting the
ResultSet.FETCH_FORWARD
static constant value.The values
ResultSet.FETCH_REVERSE
andResultSet.FETCH_UNKNOWN
are not supported. Attempting to specify them causes a SQL warning, and the settings are ignored.
This is also mentioned in the readme of the Oracle Database 11g Release 2 JDBC Drivers (the ultimate version at the time of writing this):
The scrollable result set implementation has the following limitation:
- setFetchDirection() on ScrollableResultSet does not do anything.
But all this was a kind of side note, using setFetchDiretion
is simply not the way to get a scrollable result set.
To create a scrollable result set with Spring's JdbcTemplate
, you should actually use the method execute(CallableStatementCreator csc, CallableStatementCallback action)
with a custom CallableStatementCreator
implementation. In this implementation, use the method Connection.prepareCall(String sql, int resultSetType, int resultSetConcurrency)
to create a CallableStatement
that will produce ResultSet
objects with the given type and concurrency. Finally, call rs.absolute()
.
UPDATE: There is a problem in the connection.prepareCall()
call, the third parameter should be a concurrency type (either ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
). Try this:
return connection.prepareCall(
"{call PKG_USER.getUser(?,?)}",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);