JDBCTemplate query method for StoredProcs?

[dholmes97]

I noticed all the query methods on JdbcTemplate take PreparedStatementCreators or Strings (for SQL). Why aren't there any query methods that take a CallableStatementCreator and a RowMapper, for example. We use stored procedures exclusively, and nearly all of them return a ResultSet with no other outparameters. Am I missing the easy way to do this in Spring?

Thanks for any help.

- Dan

(The decision to use stored procedures in this manner is beyond my ability to control, so suggestions to use PreparedStatements, etc, are non-starters for me.)

[wpoitras]

Given a JdbcTemplate you can use executeQuery(CallableStatementCreator csc, CallableStatementCallback action). Either as a utility method or a method on a JdbcTemplate subclass. The follow is an example of a utility method.

public List SimpleProc(JdbcTemplate jdbcTemplate, CallableStatementCreator csc, final RowMapper rm)
{
    return (List)jdbcTemplate.executeQuery(csc, new CallableStatementCallback() {
      public Object doInCallableStatement(CallableStatement cs) throws SQLException {
          ResultSet rs = cs.executeQuery();
          int rowNum = 0;
          List result = new ArrayList();
          while (rs.next()) {
            rowNum++;
            result.add(rm.mapRow(rs, rowNum));
          }
          return result;
      }
    });
}

[dholmes97]

Thanks! It looks obvious now that you spell it out, but I wasn't grasping the use of the CallableStatementCallback interface.

[azzoti]

I haven't a Sybase server to try this out on at the moment, but I think this might work...

If a sybase (MsSQL?) stored procedure just returns a single result set via a select will the following simpler call work?

List rows = jdbcTemplate.queryForList("exec somedb.db.procname ?, ?", , new Object[] {new Integer(123), "Spain"});

[azzoti]

That should have read
List rows = jdbcTemplate.queryForList("exec somedb.dbo.procname ?, ?", , new Object[] {new Integer(123), "Spain"});