SimpleJdbcCall ignoring JdbcTemplate fetch size

Question

We are calling the pl/sql stored procedure through Spring SimpleJdbcCall, the fetchsize set on the JdbcTemplate is being ignored by SimpleJdbcCall. The rowmapper resultset fetch size is set to 10 even though we have set the jdbctemplate fetchsize to 200. Any idea why this happens and how to fix it?

Have printed the fetchsize of resultset in the rowmapper in the below code snippet - Once it is 200 and other time it is 10 even though I use the same JdbcTemplate on both occassion.

This direct execution through jdbctemplate returns fetchsize of 200 in the row mapper

    jdbcTemplate = new JdbcTemplate(ds);
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    jdbcTemplate.setFetchSize(200);

    List temp = jdbcTemplate.query("select 1 from dual", new ParameterizedRowMapper() {
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            System.out.println("Direct template : " + resultSet.getFetchSize());
            return new String(resultSet.getString(1));
        }
    });

This execution through SimpleJdbcCall is always returning fetchsize of 10 in the rowmapper

jdbcCall = new SimpleJdbcCall(jdbcTemplate).withSchemaName(schemaName)
                .withCatalogName(catalogName).withProcedureName(functionName);
jdbcCall.returningResultSet((String) outItValues.next(), new        ParameterizedRowMapper<Map<String, Object>>() {
                public Map<String, Object> mapRow(ResultSet rs, int row) throws SQLException {
                   System.out.println("Through simplejdbccall " + rs.getFetchSize());
                    return extractRS(rs, row);
                }
            });
outputList = (List<Map<String, Object>>) jdbcCall.executeObject(List.class, inParam);

Answer 1

I'm not sure how much the getFetchSize() method on ResultSet can be trusted. JdbcTemplate calls setFetchSize() on the Statement, and assumes the JDBC driver will do the right thing with it, including propagating it to all ResultSet objects. It seems that the Oracle JDBC driver isn't, in this case.

The reason you get different behaviour from the two approaches is that executing a simple SELECT through JdbcTemplate is simple JDBC, whereas the ResultSet you get back from SimpleJdbcCall is obtained as an OUT parameter to the call. The latter is more complex, and it seems the information is being lost.

As a workaround, have you tried calling ResultSet.setFetchSize() yourself? It might not work, but it's worth a go. You could also submit a issue to http://jira.springsource.org/ to see if they think that Spring's JDBC layer could address it transparently.

Answer 2

Not a direct answer, but a helpful thing I think. When you create objects of spring classes by hand most people tend to forget calling the afterPropertiesSet() method (which is called by Spring to perform any initialisation).

I checked the jdbcTemplate.afterPropertiesSet() it only seems to set the exception translator and the data source.

Answer 3

If you want to limit the number of rows returning with Oracle, you should use a subselect query and specify the start and stop rownumber like this:

SELECT * FROM (SELECT ROWNUM as id, demo.* FROM DEMO_TABLE demo)
WHERE id >= startRowNumber AND id <= stopRowNumber; 

If you don't want any Oracle specific code, you should consider JPA instead of JDBC:

Query selectQuery = entityManager.createQuery(queryString);
selectQuery.setMaxResults(maxNumberOfElements);
selectQuery.setFirstResult(startRowNumber);
List demoList = entityManager.getResultList(queryString);