A.J.

At jive there are many times we want to use scrollable result sets in clearspace. We use this functionality for things like pagination. Unfortunately Spring’s JDBC template support lacks this currently. However Spring is flexible enough to allow us to extend it fairly easily.

The first thing we want to do is to make our own template object, later we will add our custom methods to it:
// implement SimpleJdbcOperations to provide all the standard
// method available in SimpleJdbcOperations
public class JiveJdbcTemplate implements SimpleJdbcOperations {

	// For most operations we will delegate to the spring jdbc template
	private SimpleJdbcTemplate template;

	public JiveJdbcTemplate(SimpleJdbcTemplate template) {
		this.template = template;
	}

	// delegate SimpleJdbcOperation methods below....

}

Next we will need to make a custom PreparedStatementCreator that will created a scrollable
prepared statement:

public static class ScrollablePreparedStatementCreator implements PreparedStatementCreator {

        private String sql;
        private Object[] args;

        public ScrollablePreparedStatementCreator(String sql, Object... args) {
            this.args = args;
            this.sql = sql;
        }

        public PreparedStatement createPreparedStatement(Connection connection)
                throws SQLException
        {
            PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            if (this.args != null) {
                for (int i = 0; i < this.args.length; i++) {
                    Object arg = this.args[i];
                    if (arg instanceof SqlParameterValue) {
                        SqlParameterValue paramValue = (SqlParameterValue) arg;
                        StatementCreatorUtils
                                .setParameterValue(ps, i + 1, paramValue, paramValue.getValue());
                    }
                    else {
                        StatementCreatorUtils
                                .setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, arg);
                    }
                }
            }

            return ps;
        }
    }

Next we will need to create a ResultSetExtract that is able to take advantage of the ResultSet#FetchSize and scroll features.

public static class ScrollableResultSetExtractor implements ResultSetExtractor {

        private int startIndex;
        private int numResults;
        private RowMapper mapper;

        public ScrollableResultSetExtractor(int startIndex, int numResults, RowMapper mapper) {
            this.startIndex = startIndex;
            this.numResults = numResults;
            this.mapper = mapper;
        }

        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {

            // Set the fetch size and search results for the result set
 		   rs.setFetchSize(numResults);
            rs.setFetchDirection(ResultSet.FETCH_FORWARD);
            rs.absolute(startIndex);

            ArrayList<Object> list = new ArrayList<Object>();
            for (int i = 0; i < numResults; i++) {
                if (rs.next()) {
                    Object o = mapper.mapRow(rs, i);
                    list.add(o);
                }
                else {
                    break;
                }
            }

            return list;
        }
    }

Finally we put it all together and add it all together by adding the following method to our custom template
created in the first step:

/**
 * Setups a srollable result set and only returns the speciied amount of results.
 *
 * @param sql The sql statement
 * @param startIndex the starting index of the result set
 * @param numResults the number of results to return.
 * @param mapper The row mapper to use.
 * @param args Arguments for the prepared statement.
 * @return The scrolled results.
 */
@SuppressWarnings({"UnnecessaryLocalVariable", "unchecked"})
public <T> List<T> queryScrollable(String sql, int startIndex, int numResults,
        ParameterizedRowMapper<T> mapper, Object... args)
{
    JdbcOperations jdbcOperations = template.getJdbcOperations();

    ScrollablePreparedStatementCreator creator = new ScrollablePreparedStatementCreator(sql,
            args);

    ScrollableResultSetExtractor extractor = new ScrollableResultSetExtractor(startIndex,
            numResults, mapper);

    List<T> list = (List<T>) jdbcOperations.query(creator, extractor);
    return list;
}