public class LoadStoredProcedure extends StoredProcedure {
private Map inParam;
public MktLoadStoredProcedure(String storedProcedureName) {
DataSource ds = (DataSource)SpringContextUtils.getInstance().getBean("dataSource");
setDataSource(ds);
setSql(storedProcedureName);
}
public RowMapper RouteRowMapper = new RowMapper(){
public Object mapRow(ResultSet rs,int rowNum) throws SQLException{
BidRouteInfo route = new BidRouteInfo();
route.setBidHeaderId(rs.getString("BID_HEADER_ID"));
route.setBidRouteInfoId(rs.getString("BID_ROUTE_INFO_ID"));
route.setCreatedByUser(rs.getString("CREATED_BY_USER"));
route.setCreatedDtmLoc(rs.getDate("CREATED_DTM_LOC"));
route.setCreatedOffice(rs.getString("CREATED_OFFICE"));
route.setCreatedTimeZone(rs.getString("CREATED_TIME_ZONE"));
route.setDestCode(rs.getString("DEST_CODE"));
route.setDestName(rs.getString("DEST_NAME"));
route.setPodCode(rs.getString("POD_CODE"));
route.setPodDestMode(rs.getString("POD_DEST_MODE"));
route.setPodName(rs.getString("POD_NAME"));
route.setPolCode(rs.getString("POL_CODE"));
route.setPolName(rs.getString("POL_NAME"));
route.setPorCode(rs.getString("POR_CODE"));
route.setPorName(rs.getString("POR_NAME"));
route.setPorPolMode(rs.getString("POR_POL_MODE"));
route.setPorState(rs.getString("POR_STATE"));
route.setPrincipalGroupCode(rs.getString("PRINCIPAL_GROUP_CODE"));
route.setRecordVersion(rs.getLong("RECORD_VERSION"));
route.setSeqNo(rs.getLong("SEQ_NO"));
route.setServiceType(rs.getString("SERVICE_TYPE"));
route.setStateCode(rs.getString("STATE_CODE"));
route.setUpdatedByUser(rs.getString("UPDATED_BY_USER"));
route.setUpdatedDtmLoc(rs.getDate("UPDATED_DTM_LOC"));
route.setUpdatedOffice(rs.getString("UPDATED_OFFICE"));
route.setUpdatedTimeZone(rs.getString("UPDATED_TIME_ZONE"));
route.setRowStatus(BaseObject.ROWSTATUS_UNCHANGED);
return route;
}
};
public RowMapper FreightRowMapper = new RowMapper(){
public Object mapRow(ResultSet rs,int rowNum) throws SQLException{
BidFreightDetail freight = new BidFreightDetail();
freight.setBidRouteInfoId(rs.getString("BID_ROUTE_INFO_ID"));
freight.setBgColor(rs.getString("BG_COLOR"));
freight.setBidFreightDetailId(rs.getString("BID_FREIGHT_DETAIL_ID"));
freight.setChargeCode(rs.getString("CHARGE_CODE"));
freight.setContainerType(rs.getString("CONTAINER_TYPE"));
freight.setCreatedByUser(rs.getString("CREATED_BY_USER"));
freight.setCreatedDtmLoc(rs.getDate("CREATED_DTM_LOC"));
freight.setCreatedOffice(rs.getString("CREATED_OFFICE"));
freight.setCreatedTimeZone(rs.getString("CREATED_TIME_ZONE"));
freight.setPrincipalGroupCode(rs.getString("PRINCIPAL_GROUP_CODE"));
freight.setRate(rs.getBigDecimal("RATE"));
freight.setRecordVersion(rs.getLong("RECORD_VERSION"));
freight.setUpdatedByUser(rs.getString("UPDATED_BY_USER"));
freight.setUpdatedDtmLoc(rs.getDate("UPDATED_DTM_LOC"));
freight.setUpdatedOffice(rs.getString("UPDATED_OFFICE"));
freight.setUpdatedTimeZone(rs.getString("UPDATED_TIME_ZONE"));
freight.setRowStatus(BaseObject.ROWSTATUS_UNCHANGED);
return freight;
}
};
//spring版本从1.2更新到2.0-rc3
// private RowMapperResultReader callback = new RowMapperResultReader(rowMapper){
// public void processRow(ResultSet rs) throws SQLException{
// int count = rs.getMetaData().getColumnCount();
// String[] header = new String[count];
// for(int i=0;i<count;i++){
// header[i] = rs.getMetaData().getColumnName(i+1);
// }
// do{
// HashMap<String,String> row = new HashMap<String,String>();
// for(int i=0;i<count;i++){
// row.put(header[i],rs.getString(i+1));
// }
// rsList.add(row);
// }while(rs.next());
// }
// };
// private RowMapperResultSetExtractor callback = new RowMapperResultSetExtractor(RouteRowMapper){
// public List extractData(ResultSet rs) throws SQLException{
// int count = rs.getMetaData().getColumnCount();
// String[] header = new String[count];
// for(int i=0;i<count;i++){
// header[i] = rs.getMetaData().getColumnName(i+1);
// }
// while(rs.next()){
// HashMap<String,String> row = new HashMap<String,String>();
// for(int i=0;i<count;i++){
// row.put(header[i],rs.getString(i+1));
// }
// rsList.add(row);
// }
// return rsList;
// }
// };
public Map execute(){
compile();
if(inParam == null){
return execute();
}
return execute(this.inParam);
}
public void setOutParameter(String column,int type,RowMapper rowMapper){
declareParameter(new SqlOutParameter(column,type,rowMapper));
}
public void setParameter(String column,int type){
declareParameter(new SqlParameter(column,type));
}
public void setInParam(Map inParam){
this.inParam = inParam;
}
}
调用：
LoadStoredProcedure sp = new LoadStoredProcedure("SP_MKT_LOAD_TEST");
sp.setParameter("V_BID_HEADER_ID", Types.VARCHAR);
sp.setOutParameter("ROUTECURSOR", OracleTypes.CURSOR,sp.RouteRowMapper);
sp.setOutParameter("FRIGHTCURSOR", OracleTypes.CURSOR,sp.FreightRowMapper);
Map<String,String> mapValue = new HashMap<String,String>(1);
mapValue.put("V_BID_HEADER_ID", bidHeaderId);
sp.setInParam(mapValue);
Map map = sp.execute();
List freightDetails = (List)map.get("FRIGHTCURSOR");
List routeInfos = (List)map.get("ROUTECURSOR");
具体参考：
http://www.springframework.org/docs/reference/jdbc.html