Stored Procedure help

[too_many_details]

Hi,

I think I'm almost there but the last few steps ahve got me stumped!!!

I am writing an inner class that returns a Map of key/value pairs. Here's my code so far:

public Map getBranches() {
        logger.info("Entering method QueryDaoImpl.getBranches");

        Map branches = new HashMap();
        branches.put("1", "London");
        branches.put("2", "Madrid");
        branches.put("3", "Barcelona");
        branches.put("4", "New York");
        logger.info("Exiting method QueryDaoImpl.getBranches");

        return branches;
    }

    private class GetBranchesSP extends StoredProcedure {
        private static final String SQL = "PKG_PBTV_UILOOKUP.F_PBTVBRANCHESGET";

        public GetBranchesSP(DataSource ds) {
            setDataSource(ds);
            setSql(SQL);
            setFunction(true);

            declareParameter(new SqlOutParameter("cursor", OracleTypes.CURSOR,new RowMapper(){

                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    logger.info("Entering method .mapRow");
                    // TODO implement method
                    logger.info("Exiting method .mapRow");
                    return null;
                }
                
            }));
            
            compile();
        }

        public Map execute() {
            Map results = execute(new HashMap());

            return results;
        }
    }

the first method getBranches() doesnt call the inner class yet and instead constructs the map manually. Its this map I want to create in the inner class. I've seen a lot of examples of using RowMapper but they all write to System.out. How can I return a map like I want to?

The cursor will hold a variable number of rows and each row has 2 strings (id and value - for populating a html drop down).

Thanks in advance,

Rakesh

[dejanp]

In your execute method you should have something like:

List cursor = results.get("cursor");

where cursor is then the list of objects you generated in your mapRow() method. You can iterate that list and transform it into the map you need.

[too_many_details]

Hi,

well i experimented a bit and came up with this approach and it works:

    public Map getBranches() {
        logger.info("Entering method QueryDaoImpl.getBranches");

        GetBranchesSP sp = new GetBranchesSP(dataSource);

        logger.info("Exiting method QueryDaoImpl.getBranches");

        return sp.execute();
    }

    private class GetBranchesSP extends StoredProcedure {
        private static final String SQL = "PKG_PBTV_UILOOKUP.F_PBTVBRANCHESGET";

        public GetBranchesSP(DataSource ds) {
            setDataSource(ds);
            setSql(SQL);
            setFunction(true);

            declareParameter(new SqlOutParameter("cursor", OracleTypes.CURSOR,
                    new ResultSetExtractor() {
                    public Object extractData(ResultSet rs)
                        throws SQLException, DataAccessException {
                        logger.info("Entering method .extractData");

                        Map resultsMap = new HashMap();

                        while (rs.next()) {
                            resultsMap.put(rs.getString(1), rs.getString(2));
                        }

                        logger.info("Exiting method .extractData");

                        return resultsMap;
                    }
                }));

            compile();
        }

        public Map execute() {
            Map results = execute(new HashMap());

            return (Map) results.get("cursor");
        }
    }

I guess there was nothing I wanted to do per-row so I changed to the ResultSetExtractor and created my Map.

Is this the most common/optimal way to do this?

Thanks

Rakesh

[trisberg]

I would move the creation of the GetBranchesSP class to a place where it's just initialized once rather than creating a new one each time the getBranches is called. You might only call this method once in your code though so my point might be moot.

[wpoitras]

I would move the creation of the GetBranchesSP class to a place where it's just initialized once rather than creating a new one each time the getBranches is called. You might only call this method once in your code though so my point might be moot.

Using the JdbcDaoSupport class as a superclass for your object works well. You initialize the stored procedure in the initDao method:

private GetBranchesSP getBranchesSP;
protected void initDao() {
  getBranchesSP = new GetBranchesSP(getJdbcTemplate());
}

I prefer initializing my stored procedures with a JdbcTemplate because JdbcDaoSupport allows you to set a DataSource and exception translator for the JdbcTemplate it creates internally.

[vaddi.sudhakar]

Hi

I have similar requirement where I need to pass 2 IN parameters and it returns Ref-Cursor as OUT parameter.

How do we modify the above code to meet my req.

If I use JDBCTemplate, how do I get data source? Is it from app-context file or other way? Please respond.

Thanks in advance !

Thanks
Sudhakar