can not use resultSet.setFetchDirection(ResultSet.TYPE_SCROLL_SENSITIVE) with spring jdbc DaoSupport with Oracle.

Question

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;
            }
        });

Answer 1

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 - an int specifying the suggested fetch direction; one of ResultSet.FETCH_FORWARD, ResultSet.FETCH_REVERSE, or ResultSet.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 and ResultSet.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);