Spring StoredProcedure Object Return List of Object

Question

I using mysql stored procedure which uses to retrieve list of object. Is this possible ?

I'm following this article

Question:

How to retrieve the list of object like in select statement using result set ?
How to map the result set to list of object ?

CREATE DEFINER=root@localhost PROCEDURE generateLCRReport(IN countryCodeParam INT, OUT countryCode INT, OUT dialCode INT, OUT custPrefix VARCHAR(50), OUT vendorPrefix VARCHAR(50), OUT custPrice FLOAT, OUT vendorCost FLOAT, OUT profit FLOAT) LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT 'generateLCRReport' BEGIN select c.country_code as countryCode, c.dial_code as dialCode, c.customer_prefix as custPrefix, c.vendor_prefix as vendorPrefix, max(cust_rate.rate) as custPrice, min(ven_rate.rate) as vendorCost, round(max(cust_rate.rate) - min(ven_rate.rate), 3) as profit from cdr c

inner join (select a.id, r.rate, re.country_code, re.dial_code, ap.prefix from rate r inner join region re on r.region_id = re.id inner join account_prefix ap on r.account_prefix_id = ap.id inner join account a on a.id = ap.account_id where ap.prefix_type = 0 ) as cust_rate

on c.country_code = cust_rate.country_code and c.dial_code = cust_rate.dial_code and c.customer_prefix = cust_rate.prefix and c.customer_id = cust_rate.id

inner join (select a.id, r.rate, re.country_code, re.dial_code, ap.prefix from rate r inner join region re on r.region_id = re.id inner join account_prefix ap on r.account_prefix_id = ap.id inner join account a on a.id = ap.account_id where ap.prefix_type = 1 ) as ven_rate

on c.country_code = ven_rate.country_code and c.dial_code = ven_rate.dial_code and c.vendor_prefix = ven_rate.prefix and c.vendor_id = ven_rate.id where c.country_code = countryCodeParam group by c.country_code and c.dial_code order by c.country_code asc limit 5000;

END

public class LCRReportSP extends StoredProcedure {
```
/**
 * 
 */
@Autowired
public LCRReportSP(JdbcTemplate jdbcTemplate, String storedProcName, RowMapper<CostReport> mapper) {
    super(jdbcTemplate, storedProcName);

    SqlReturnResultSet rs = new SqlReturnResultSet("", mapper);
    SqlOutParameter outParam = new SqlOutParameter("countryCode", Types.INTEGER);
    SqlOutParameter outParam1 = new SqlOutParameter("dialCode", Types.INTEGER);
    SqlOutParameter outParam2 = new SqlOutParameter("custPrefix", Types.VARCHAR);
    SqlOutParameter outParam3 = new SqlOutParameter("vendorPrefix", Types.VARCHAR);
    SqlOutParameter outParam4 = new SqlOutParameter("custPrice", Types.FLOAT);
    SqlOutParameter outParam5 = new SqlOutParameter("vendorCost", Types.FLOAT);
    SqlOutParameter outParam6 = new SqlOutParameter("profit", Types.FLOAT);

    this.declareParameter(rs);
    this.declareParameter(outParam);
    this.declareParameter(outParam1);
    this.declareParameter(outParam2);
    this.declareParameter(outParam3);
    this.declareParameter(outParam4);
    this.declareParameter(outParam5);
    this.declareParameter(outParam6);

    this.setFunction(false);
    this.compile();
}

/**
 * @param countryCode
 * @return
 */
public Map<String, ?> generateLCRReport(int countryCode) {

    Map<String, Object> inParam = new HashMap<String, Object>();

    inParam.put("countryCodeParam", new Integer(countryCode));

    return this.execute(inParam);
}
```
}

Please help.

Thanks.

nicholas nicholas · Accepted Answer · 2013-03-13T04:05:05

0

votes

I'm using RowMapper and declared parameter SqlReturnResultSet.

Spring StoredProcedure Object Return List of Object

1 Answers