Announcement Announcement Module
Collapse
No announcement yet.
mapping or handing cursor inside (as a collumn) of a cursor Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mapping or handing cursor inside (as a collumn) of a cursor

    Hiall,
    I am using a stored procedure to get data from Oracle, am using RowMapper Implementators to map with the outparameter of the stored procedures.
    Now we have a senario where we need a cursor as a collumn of the another cursor..... so can any one suggest me how to handle (in the spring way) or is there any best praticses in this....

    Currently we are getting the internal cursor by a resultSet.getObject method
    from that we are processing ......


    Current sample code

    public class FetchCourseSearchResultsSP extends StoredProcedure
    public FetchCourseSearchResultsSP(DataSource ds)
    {
    setDataSource(ds);
    setSql(HCConstants.FETCH_COURSE_SEARCH_RESULTS);
    declareParameter(new SqlParameter("p_user_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_affiliate_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_region_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_aus_study_mode_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_aus_qual_level_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_search_text", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("p_county_state_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_category_code", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("p_category_display_level", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_search_on_what", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("p_institution_id", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_page_no", OracleTypes.NUMBER));
    declareParameter(new SqlParameter("p_order_by_what", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("p_qual_option_desc", OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter("p_total_inst_cnt", OracleTypes.NUMBER));

    declareParameter(new SqlOutParameter("pc_course_results", OracleTypes.CURSOR, new CourseListRowMapperImpl()));


    declareParameter(new SqlOutParameter("p_error_msg",
    OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter("p_ret_code", OracleTypes.NUMBER));
    compile();
    }

    public Map execute(List params, HttpServletRequest request)
    {
    Map outMap = null;
    try
    {
    // To translate keyword to english
    String keyword = (String)params.get(1);
    String countryDomain = HCIUtil.getCountryDomain(request);
    ResourceBundle defaultbundle = ResourceBundle.getBundle("com.hcint.resources.Appl icationResources_language");
    if(countryDomain != null && (defaultbundle.getString("nonenglish.domain").inde xOf(countryDomain)!=-1)) {
    if(keyword != null && !keyword.equalsIgnoreCase("null")) {
    String language = HCIUtil.getTranslateLang(defaultbundle,request);
    keyword = Translate.translate(keyword, language, Language.ENGLISH);
    }
    }
    Map inMap = new HashMap();
    inMap.put("p_user_id", params.get(13)); // user id.
    inMap.put("p_affiliate_id", params.get(14)); // affiliate id.
    inMap.put("p_user_agent", params.get(16)); // user id.
    inMap.put("p_client_id", params.get(15)); // affiliate id.
    inMap.put("p_region_id", params.get(4)); // country id.
    inMap.put("p_study_mode_id", params.get(9)); // study mode id.
    inMap.put("p_int_qual_rel_id", params.get(8)); // qualification level id(Includes child qualification Id).
    inMap.put("p_qual_display_level", params.get(18)); // Qualification display level.
    inMap.put("p_qual_parent_rel_id", params.get(17)); // Qualification parent id(NULL if we display only parents).
    inMap.put("p_keyword", keyword); // keyword entered by the user.
    inMap.put("p_county_state_id", params.get(7)); // county / state id.
    inMap.put("p_category_code", params.get(6)); // category code.
    inMap.put("p_category_display_level", params.get(11)); // category display level.
    inMap.put("p_keyword_search_on_what", String.valueOf(params.get(3)).toUpperCase()); // where to search, either course title or course title and summary.
    inMap.put("p_institution_id", null); // institution id. (For future use).
    inMap.put("p_page_no", params.get(12)); // for pagination. by default 1.
    inMap.put("p_order_by_what", params.get(10));
    inMap.put("p_request_url", params.get(21));
    inMap.put("p_referred_url", params.get(20));


    outMap = execute(inMap);
    } catch (Exception e)
    {
    e.printStackTrace();
    }
    return outMap;
    }





    private class CourseListRowMapperImpl implements RowMapper
    {
    StringBuffer bf = new StringBuffer(10);
    public Object mapRow(ResultSet rs, int rownum)
    {
    CourseBrowseVO coursebrowsevo = new CourseBrowseVO();
    try
    {
    coursebrowsevo.setCourseCount(rs.getString("course _cnt"));
    coursebrowsevo.setCollegeId(rs.getString("institut ion_id"));
    coursebrowsevo.setCollegeName(rs.getString("instit ution_name"));
    coursebrowsevo.setReviewRating(rs.getString("revie w_rating"));
    coursebrowsevo.setReviewCount(rs.getString("review _cnt"));



    ArrayList options = new ArrayList();

    ResultSet advertResultSet = (ResultSet)rs.getObject("profile_rec");
    try
    {
    //Iterating the cursor
    while (advertResultSet.next())
    {
    //Instantiating the option value type
    CourseBrowseVO coursebrowsevo1 = new CourseBrowseVO();
    coursebrowsevo1.setKeyId(advertResultSet.getString ("key_id"));
    coursebrowsevo1.setSection(advertResultSet.getStri ng("section"));
    coursebrowsevo1.setMediaPath(advertResultSet.getSt ring("media_path"));
    coursebrowsevo1.setMediaTypeId(advertResultSet.get String("media_type_id"));
    coursebrowsevo1.setWhatProfile(advertResultSet.get String("what_profile"));
    coursebrowsevo1.setAdvertName(advertResultSet.getS tring("advert_name"));
    coursebrowsevo1.setInstProfileFlag(advertResultSet .getString("inst_profile_exists"));
    coursebrowsevo1.setSubjProfileCount(advertResultSe t.getString("subj_profile_count"));

    if (advertResultSet.getString("sys_var_value") != null)
    {

    if (advertResultSet.getString("media_type_id") != null && advertResultSet.getString("media_type_id").equalsI gnoreCase("1"))
    {
    bf.delete(0, bf.length());
    bf.append(advertResultSet.getString("sys_var_value ") + advertResultSet.getString("media_path"));
    if (bf != null && bf.lastIndexOf(".") != -1)
    {
    bf.insert(bf.lastIndexOf("."), "t");
    coursebrowsevo1.setMediaPath(advertResultSet.getSt ring("sys_var_value") + advertResultSet.getString("media_path"));
    coursebrowsevo1.setThumbNails(bf.toString()); //seting thumbnail path

    }

    }
    }


    options.add(coursebrowsevo1);
    }
    coursebrowsevo.setAdvertList(options);
    } catch (Exception e)
    {
    throw new SQLException(e.getMessage());
    } finally
    {
    try
    {
    advertResultSet.close();
    } catch (Exception e)
    {
    throw new SQLException(e.getMessage());
    }
    }


    } catch (Exception e)
    {
    e.printStackTrace();
    }
    return coursebrowsevo;
    }
    }
Working...
X