Announcement Announcement Module
Collapse
No announcement yet.
Generating header row using StoredProcedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generating header row using StoredProcedure

    Hello All,

    I'm extending the Spring StoredProcedure class to invoke an oracle sproc which returns a REF cursor. I'm currently using a SqlOutParamer with a ParameterizedRowMapper to map the REF cursor row to my java objects. In addition to the row by row mapping, I need to include a "header" row in the results and I'd like to generate that row dynamically using the resultSet metadata. It seems that the RowMapper and ResultSetExtractor are stateless and wouldn't allow me break out of the row by row processing and the RowCallbackHandler is not threadsafe/reusable and so shouldn't be used when declaring Out params with the StoredProcedure class. I'm hoping to stay with the StoredProcedure approach but it seems like the RowCallBackHandler might be my only option which means I'd have to dump the StoredProcedure approach and use something like SimpleJDBCTemplate directly. Does anyone know of a way to include this header row in the results while still staying within the confines of using the StoredProcedure class?

    Here's an example code snippet:


    Code:
    public class MyProcedure extends StoredProcedure {
    
    	private static final String RESULT_CURSOR = "O_REF_CURSOR";
    
    	public MyProcedure(DataSource dataSource) {
    
    		super(dataSource, "SOME_PKG.SOME_SPROC");
    		// Input Param
    		declareParameter(new SqlParameter("p_param_1", Types.CHAR));
    		// Output Param (REF cursor) 
    		// Currently using PRM, can't use RowCallBackHandler here due to thread safety.
    		declareParameter(new SqlOutParameter(RESULT_CURSOR, OracleTypes.CURSOR, new ParameterizedRowMapper<Row>() {
    
    			@Override
    			public Row mapRow(ResultSet rs, int rowNum) throws SQLException {
    
    				int columnCount = rs.getMetaData().getColumnCount();
    				List<String> values = new ArrayList<String>();
    
    				for (int i = 1; i <= columnCount; i++) {
    					values.add(rs.getString(i));
    				}
    				return new Row(values);
    			}
    		}));
    
    		compile();
    
    	}
    
    	public SomeResponseType execute(String param1 {
    
    		results = super.execute(param1);
    		List<Row> rows = (List<Row>) results.get(RESULT_CURSOR);
    		// Somehow I'd like the first row of the "rows" list to contain a header row built from the resultset column metadata
    		return new SomeResponseType(rows);
    
    	}
    
    }
    Any help would be greatly appreciated.
    Thanks!
    Last edited by seamuskc; May 7th, 2013, 11:15 AM.

  • #2
    So I figured out a round about way to accomplish what I was looking for (generating additional "header" row from within a class extending Spring's StoredProcedure class). Rather than process the ResultSet returned from the sproc using a RowMapper, I used a ResultSetExtractor instead like so:

    Code:
    public class MyProcedure extends StoredProcedure {
    
    	private static final String RESULT_CURSOR = "O_REF_CURSOR";
    
    	public MyProcedure(DataSource dataSource, ResultSetExtractor<List<Row>> rse) {
    
    		super(dataSource, "SOME_PKG.SOME_SPROC");
    		// Input Param
    		declareParameter(new SqlParameter("p_param_1", Types.CHAR));
    		// Output Param (REF cursor) 
    		// Use RSE here instead of RowMapper
    		declareParameter(new SqlOutParameter(RESULT_CURSOR, OracleTypes.CURSOR, rse);
    
    		compile();
    
    	}
    
    	public SomeResponseType execute(String param1) {
    
    		results = super.execute(param1);
    		List<Row> rows = (List<Row>) results.get(RESULT_CURSOR);
    		return new SomeResponseType(rows);
    
    	}
    
    }
    With the ResultSetExtractor implementation like so:

    Code:
    public class RowListResultSetExtractor<T> implements ResultSetExtractor<List<Row>> {
    
    	private RowMapper<Row> rowMapper;
    
    	public RowListResultSetExtractor(RowMapper<Row> rm) {
    		super();
    		this.rowMapper = rm;
    	}
    
    	@Override
    	public List<Row> extractData(ResultSet rs) throws SQLException, DataAccessException {
    
    		List<Row> rowList = new ArrayList<Row>();
    
    		while (rs.next()) {
    			rowList.add(rowMapper.mapRow(rs, rs.getRow()));
    		}
    
    		if (rowList.size() > 0) {
    			// Only add header row if results found (to be consistent with bad org id process)
    			rowList.add(0, extractHeaderRow(rs));
    		}
    
    		return rowList;
    	}
    
    	private Row extractHeaderRow(ResultSet rs) throws SQLException {
    
    		ResultSetMetaData metaData = rs.getMetaData();
    		int columnCount = metaData.getColumnCount();
    		Row headerRow = new Row();
    		List<String> values = headerRow.getValues();
    		for (int i = 1; i <= columnCount; i++) {
    			values.add(metaData.getColumnName(i));
    		}
    		return headerRow;
    	}
    
    }
    The downside that I see to taking this approach though is that the usage of the MyProcedure class has to be changed/misused slightly. Before, I was creating a single instance of MyProcedure at dao initialization time and then reusing that single instance anytime I needed to invoke that stored procedure. Now that I'm using the stateful/non-threadsafe ResultSetExtractor, I have to create a new instance of MyProcedure anytime I need to invoke it like so:

    Code:
    // Some DAO method
    public SomeResponseType runSproc(String someInput) {
    
    		MyProcedure myProcedure = new MyProcedure(
    				this.dataSource,
    				new RowListResultSetExtractor<List<Row>>(new TabularDataRowMapper()));
    
    		return myProcedure.execute(someInput);
    	}
    This seems somewhat wasteful and kind of defeats the purpose of using the compiled stored proc if I need to init/compile it each time it's invoked but I did some rudimentary bench marking and it doesn't seem to add a huge amount of overhead so I'm thinking it may be acceptable. Especially if this method doesn't see a huge amount of traffic. Does anybody have any feedback or opinions on if this is a gross misuse of the expected usage of the StoredProcedure class? Any feedback/replies would be appreciated.

    Comment

    Working...
    X