Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure/Oracle Cursor Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure/Oracle Cursor

    Hi All,

    Here is what I am trying to accomplish -- I have a stored procedure that takes in two inputs, returns an Oracle cursor. From the result set, I want to map each row to a custom object, and then return an array of these objects.

    This is what I've come up with so far:

    Code:
    public class MyDAOImpl implements MyDAO {
        private JdbcTemplate jdbcTemplate;
    
        public MyObject[] getObjects(String input1, String input2)
            throws ServiceLogicException {
            GetObjects sproc = new GetObjects(jdbcTemplate.getDataSource());
            Map results = sproc.execute(siteID, finalDestinationID);
    
            ResultSet objects = results.get("output");
    
            // Now what?!? :(
        }
    
        public void setDataSource(DataSource dataSource) {
            this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
    
        private class GetObjects extends StoredProcedure {
            public static final String PROC_NAME = "stored procedure name";
    
            public GetObjects(DataSource ds) {
                super(ds, PROC_NAME);
                declareParameter(new SqlParameter("input1", Types.VARCHAR));
                declareParameter(new SqlParameter("input2",
                        Types.VARCHAR));
                declareParameter(new SqlReturnResultSet("output",
                        new RowMapper() {
                        public Object mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                            MyObject myObject = new MyObject();
                            myObject.setProp1(rs.getLong(1));
                            myObject.setProp2(rs.getLong(2));
    
                            return myObject 
                        }
                    }));
                compile();
            }
    
            public Map execute(String input1, String input2){
                Map inParameters = new HashMap();
                inParameters.put("input1", input1);
                inParameters.put("input2", input2);
    
                return super.execute(inParameters);
            }
        }
    }
    Am I on the right track? Is it correct to map each row in a result set to an object, and then assume that my new result set will be a collection of objects? If so, how do I convert the new result set to an array of objects?

    Please help! Thanks!

  • #2
    The out parameter for the Oracle stored procedure should be declared as an SqlOutParameter with a type of OracleTypes.CURSOR like:
    Code:
    declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR, new RowMapper() ...
    The resulting Map will have an entry named "output" which is a List of the objects you created. You should be able to use toArray() method after casting this to a List.

    Comment

    Working...
    X