You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by shiva kumar <sh...@yahoo.co.in> on 2009/12/18 08:25:21 UTC
IBATUS - Fetching a List of List of Strings from the XML Mapping - Help Needed
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. http://in.yahoo.com/
Re: IBATUS - Fetching a List of List of Strings from the XML Mapping
- Help Needed
Posted by Nathan Maves <na...@gmail.com>.
First off welcome to ibatis!
Second please don't use this email list for help topics like this. You
should use the user-java@ibatis.apache.org for normal question like this.
This allows others in the community to help. I have included it in the
reply to move this to the correct email list.
Your issue is that you are not setting your result class properly. Ibatis
will return a list of your resultClass because you are calling
queryForList(). You need to set the resultClass to the actual class that
your query returns. It appears from the name of the select that you are
looking for claims. If you have a Claim object I would suggest using it as
the result class. If not a simple java.util.HashMap would work. I would
suggest going the first route and creating a POJO to hold each result and
then harness the true mapping power of ibatis.
Nathan
On Fri, Dec 18, 2009 at 12:25 AM, shiva kumar <sh...@yahoo.co.in>wrote:
> 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 IBAT*I*S 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<http://in.rd.yahoo.com/tagline_yyi_1/*http://in.yahoo.com/>
> .
>