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/
