IBATUS - Fetching a List of List of Strings from the XML Mapping - Help Needed

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

IBATUS - Fetching a List of List of Strings from the XML Mapping - Help Needed

shiva kumar-10

Hi All,

 

This is Sivakumar. I am working with a Struts based UI Application. I have a requirement to externalize all the SQL Queries from the DAO Classes to the XML Files using IBATIS Framework. I would need your assistance in solving an issue that i am facing while modifying the Queries. I am facing an issue in a particular method within A DAO  Class where the select query returns a List of List of Records in the Normal DAO Query. However i am not able to fetch this when i am using the IBATIS resultClass="list". Please find below the Method below and let me know your suggestions, so that I could fetch a list of List from the IBATIS XML File Mapping. It would be really helpful to me.

 

    Method before using IBATIS:

 

    public List findClaims(ClaimSearchCriteriaVO searchCriteriaVO, UserProfileVO userProfileVO) throws DAOException {

 

      List displayColumns = searchCriteriaVO.getDisplayColumns();

 

   try{

 

      sql = prepareSQL(searchCriteriaVO.getDisplayColumns());
      sql.append(" FROM wrkenv01..WKTV_GP00_CLAIMS WHERE ");
      sql.append("USUS_ID = ? AND ");
      sql.append("GP00_SESSION_ID = ? AND ");
      sql.append("WEB_PAGE_IND = ? ");

 

      if (searchCriteriaVO.getClaimSortColumn() != null && !   searchCriteriaVO.getClaimSortColumn().equalsIgnoreCase(Constants.ASTRIX)) {

        sql.append("ORDER BY " + searchCriteriaVO.getClaimSortColumn());

 

        if (searchCriteriaVO.getClaimSortOrder() != null && searchCriteriaVO.getClaimSortOrder().equalsIgnoreCase("D")) {

          sql.append(" DESC ");

        } else {

          sql.append(" ASC ");
        }
      }
      pstmt = con.prepareStatement(sql.toString());
      sessionId = String.valueOf(userProfileVO.getSessionId()).trim();
      pstmt.setString(1, userProfileVO.getUserId());
      pstmt.setString(2, sessionId);
      pstmt.setString(3, "S"); // WEB_PAGE_IND 'S' for search
      rs = pstmt.executeQuery();
      searchResultList = new ArrayList();

      while (rs.next()) {

        rowList = new ArrayList();
        rowList.add(DaoUtil.trim(rs.getString("DISPLAY_COLOR_IND")));
        rowList.add(DaoUtil.trim(rs.getString("CLCL_ID")));
        rowList.add(DaoUtil.trim(rs.getString("PRPR_ID")));

        if (displayColumns != null) {

          for (int displayColumnsIter = 0; displayColumnsIter < displayColumns.length; displayColumnsIter++) {

            rowList.add(DaoUtil.trim(rs.getString(displayColumns[displayColumnsIter])));
          }
        }
        searchResultList.add(rowList);
      }

    } catch (SQLException ex) {

      m_logger.error("SQLException in findClaims() :" + ex.getMessage());
      throw new DAOException(ex.getMessage());

    } finally {

      AppDBUtil.closeQueryObjects(rs, pstmt, con);
    }
    return searchResultList;
  }

 

 

Method after using IBATIS: DAO.java file

 

public List findClaims(ClaimSearchCriteriaVO searchCriteriaVO, UserProfileVO userProfileVO) throws DAOException {

 

    String[] displayColumns = null;

    String claimSortColumn = null;

    String claimSortOrder = null;

    List searchResultList = null;

   

    try {

 

      displayColumns = searchCriteriaVO.getDisplayColumns();

      claimSortColumn = searchCriteriaVO.getClaimSortColumn();

      claimSortOrder = DaoUtil.trim(searchCriteriaVO.getClaimSortOrder());

     

      Map findClaimsMap = new HashMap();

      findClaimsMap.put("userID", userProfileVO.getUserId());

      String sessionId = String.valueOf(userProfileVO.getSessionId()).trim();

      findClaimsMap.put("sessionID", sessionId);

      findClaimsMap.put("webPageInd", "S");

     

      if (displayColumns != null) {

       

        findClaimsMap.put("displayColumns", displayColumns);

      }

      if (claimSortColumn != null && !claimSortColumn.equalsIgnoreCase(Constants.ASTRIX)) {

       

        System.out.println("claimSortcolumn"+claimSortColumn);

        findClaimsMap.put("claimSortColumn", claimSortColumn);

       

        if (claimSortOrder != null) {

         

          findClaimsMap.put("claimSortOrder", claimSortOrder);

        }

      }

      SqlMapClient sqlMapQueryBuilder = AppUtil.getSQLMapQueryBuilder(m_logger, sqlMapConfigFile);

      searchResultList = sqlMapQueryBuilder.queryForList("Claim.findClaims", findClaimsMap);

     

      for (int i = 0; i < searchResultList.size(); i++){

       

        List rowsList = (List) searchResultList.get(i);

        System.out.println("rowsList "+rowsList.size()); // This should contain all the Column Values fetched from the Table. But this prints 0

                                                    

      }

     

    } catch (SQLException ex) {

 

      m_logger.error("SQLException in findClaims() :" + ex.getMessage());

      throw new DAOException(ex.getMessage());

    }

    return searchResultList;

  }

 

using IBATIS: Query.xml file

 

<select id="findClaims" resultClass="list" >

                 SELECT DISPLAY_COLOR_IND, CLCL_ID, PRPR_ID

                  <isPropertyAvailable property="displayColumns">

                              <iterate prepend ="," property="displayColumns" conjunction=",">

                                      #displayColumns[]#

                                      </iterate>

                          </isPropertyAvailable> as claimsList

                         FROM wrkenv01..WKTV_GP00_CLAIMS WHERE

             USUS_ID = #userID# AND

             GP00_SESSION_ID = #sessionID# AND

         WEB_PAGE_IND = #webPageInd#

         <isPropertyAvailable property="claimSortColumn" >

                                    ORDER BY $claimSortColumn$

                                    <isPropertyAvailable property="claimSortOrder" >

                                                <isEqual property="claimSortOrder" compareValue="D">

                                                             DESC

                                                </isEqual>

                                                <isNotEqual property="claimSortOrder" compareValue="D">

                                                             ASC

                                                </isNotEqual>  

                                    </isPropertyAvailable>

                         </isPropertyAvailable>

</select>

     

Could you please provide your expertise on this?

 

Thanks,

 

Shiva




The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.