How to use RowSetDynaClass with Spring RowMapper to retrieve all rows of a ResultSet?

Question

I have a stored procedure in my Oracle DB which returns a CURSOR and works fine. I want to retrieve the cursor's data in Java without any Object persistence. My only goal is to display it to the screen. Since I will have dozens of stored procedures and I don't want to create a new RowMapper corresponding to each of them so I tried to use RowSetDynaClass.

I subclassed Spring StoredProcedure object like this:

public abstract class AbstractReport extends StoredProcedure {

    public static final String KEY_REPORT_RESULT = "profiles";

    public AbstractReport(DataSource dataSource, String sprocName) {
        super(dataSource, sprocName);
        declareParameter(new SqlOutParameter(
                KEY_REPORT_RESULT,
                oracle.jdbc.OracleTypes.CURSOR,
                new RowMapper() {
                    public RowSetDynaClass mapRow(ResultSet argResults, int argRowNum ) throws SQLException {
                        return new RowSetDynaClass(argResults);
                    }
                })
        );
//      declareParameter(new SqlOutParameter(
//              "test",
//              oracle.jdbc.OracleTypes.CURSOR,
//              new RowMapper() {
//                  public String[] mapRow(ResultSet argResults, int argRowNum ) throws SQLException {
//                      return new String[]{argResults.getString(1), argResults.getString(2), argResults.getString(3)};
//                  }
//              })
//        );
        compile();
    }

    @SuppressWarnings("unchecked")
    public Map<String, Object> computeReport(Map<String, Object> params){
        return super.execute(params);
    }
...

}

This is called like this:

public List<String[]> computeReport(Map<String, Object> params, ReportEnum report) throws EptServiceException {
        List<String[]> result;

        try {
            logger.debug("Computing report " + report);
            AbstractReport procedure = getReport(report);

            Map<String, Object> rawResult = procedure.computeReport(params);
            logger.info("rawResult:" + rawResult);
            result = generateReportOutput(rawResult);
        } catch (EptServiceException e) {
            throw e;
        } catch (Exception e) {
            logger.error("Error while computing report:" + report, e);
            throw new EptServiceException(EsbPortalError.ERROR_PORTAL_UNKNOWN, e);
        }

        return result;
    }

All of this works almost perfectly well except I'm missing a row from the result... When I execute the stored procedure in SQL I have 3 results. If I use a simple RowMapper like the one in comment above I have 3 results.

But if I use RowSetDynaClass I get one object with 2 rows, instead of the 3 expected. I know it doesn't make much sense (at least it doesn't for me) but there must be something stupid I'm missing.

What am I doing wrong?

Anth0 · Accepted Answer · 2013-04-10 14:22:13Z

I finally figured it out (mostly). I couldn't find a way to make it work using RowMapper interface. Thus, I used the ResultSetExtractor object as following:

declareParameter(new SqlOutParameter(
            KEY_REPORT_RESULT,
            oracle.jdbc.OracleTypes.CURSOR,
            new ResultSetExtractor() {

                @Override
                public Object extractData(ResultSet rs) throws SQLException,
                        DataAccessException {
                    return new RowSetDynaClass(rs);
                }
            })
);

This output me the correct three results I was expecting. As for why RowMapper does not work but this one does I don't know.

Kumar's Blog

Sunday, November 17, 2013

How to use RowSetDynaClass with Spring RowMapper to retrieve all rows of a ResultSet?

1 Answer

2 comments: