File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes EJB and other Java EE Technologies and the fly likes RowMapper & Stored Proc @ DB2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "RowMapper & Stored Proc @ DB2" Watch "RowMapper & Stored Proc @ DB2" New topic

RowMapper & Stored Proc @ DB2

suresh saro

Joined: May 22, 2006
Posts: 4
Am trying to execute the below program:

Am getting the resultset as blank (i.e) []

Thanks in Advance!!

public class AgentInfoDAOImpl extends BaseJdbcDAO implements AgentInfoDAO {
private static final Log log = LogFactory.getLog(AgentInfoDAOImpl.class);
private static final String RETURN_SUCCESS = "000";
private String dbSchema;
private AgencyProfileDTO agProfile;
private ProducerDetailsDTO prodDetails;
private Map input = new HashMap();
private String licenseStatus;

* Uses the Inner Class StoredProcAGSIM028, to create the Database connection and execute the stored procedure.
* @see O#getProducerList( chCriteriaDTO)
public ProducerDetailsDTO getProducerList(
AgInfoSearchCriteriaDTO agSCriteria) throws ResourceException {
StoredProcAGSIM028 storedProc = new StoredProcAGSIM028(getJdbcTemplate());

prodDetails = new ProducerDetailsDTO();
buildInput(agSCriteria, "AGSIM028");
return prodDetails;

private void buildInput(AgInfoSearchCriteriaDTO agSCriteria, String ticket) {
if (ticket.equals("AGSIM028")) {
input.put("I_AGCY", "00000869");
input.put("I_PRODUCER", "");
input.put("C_STATUS", "AC");

private DataAccessException buildDataAccessException(final Map inParams,
final org.springframework.dao.DataAccessException exception,
final String storedProcedureName) {
String msg = "Failed to execute stored procedure " +
storedProcedureName + " because of database reason " +

DataAccessException dataException = new DataAccessException(msg,
ErrorCode.DEFAULT, exception);
dataException.addDataItem("inParams", inParams);
log.error(msg, exception);

return dataException;

* @return Returns the dbSchema.
public String getDbSchema() {
return dbSchema;

* @param aDBSchema The dbSchema to set.
public void setDbSchema(final String aDBSchema) {
dbSchema = aDBSchema;
private class StoredProcAGSIM028 extends StoredProcedure {
public static final String SQL = "AGSIM028";

* Constructor to define the stored proc parameters (INPUT & OUTPUT).
* @param jdbcTemplate
public StoredProcAGSIM028(final JdbcTemplate jdbcTemplate) {
super(jdbcTemplate, getDbSchema() + "." + SQL);
declareParameter(new SqlReturnResultSet("T_FIRST_NAME", new DataMapper()));
declareParameter(new SqlParameter("I_AGCY", Types.CHAR));
declareParameter(new SqlParameter("I_PRODUCER", Types.CHAR));
declareParameter(new SqlParameter("C_STATUS", Types.CHAR));
declareParameter(new SqlOutParameter("AG2_RC", Types.CHAR));
declareParameter(new SqlOutParameter("AG2_RC_CAUSE", Types.CHAR));
declareParameter(new SqlOutParameter("AG2_SQLCA", Types.CHAR));
declareParameter(new SqlOutParameter("AG2_SQLCODE",
declareParameter(new SqlOutParameter("AG2_SQLCERM", Types.CHAR));

* Executes the stored procs.
* @param inParams - a Map containing the input parameters to the stored procedure
* @throws DataAccessException when execution of stored procs fails
public void executeStoredProc(final Map inParams)
throws DataAccessException {
Map out = null;

try {
out = execute(inParams);
List productList = (List) out.get("T_FIRST_NAME");
List l = (List)out.get("T_FIRST_NAME");
} catch (org.springframework.dao.DataAccessException e) {
throw buildDataAccessException(null, e, SQL);
stopTimer("Execute Stored Proc...." + SQL);


* DataMapper is an Inner class which implements the RowMapper.
private class DataMapper implements RowMapper {
* Method to map the ResultSet to the Object.
* @param ResultSet
* @param Number of Rows
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
return rs.getString("T_FIRST_NAME");
I agree. Here's the link:
subject: RowMapper & Stored Proc @ DB2
It's not a secret anymore!