今天在继续研究JShopStore时候，在查看product的明细时即
执行action
<action path="/shop/viewItem" type="org.apache.struts.beanaction.BeanAction"
name="catalogBean" scope="session"
validate="false" input="/catalog/Product.jsp">
<forward name="success" path="/catalog/Item.jsp"/>
</action>报NestedSqlException错误，错误的核心部分如下：
Caused by: com.ibatis.dao.client.DaoException: Failed to execute queryForObject - id [getItem], parameterObject [EST-18]. Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ibatis/jpetstore/persistence/sqlmapdao/sql/Item.xml.
--- The error occurred while applying a parameter map.
--- Check the getItem-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]?? 'itemid' ????
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]?? 'itemid' ????
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ibatis/jpetstore/persistence/sqlmapdao/sql/Item.xml.
--- The error occurred while applying a parameter map.
--- Check the getItem-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]?? 'itemid' ???? 我跟踪、跟踪，跟踪到ItemSqlMapDao
public Item getItem(String itemId) 
{
Integer i = (Integer) queryForObject("getInventoryQuantity", itemId);
Item item = (Item) queryForObject("getItem", itemId);
item.setQuantity(i.intValue());
return item;
}找到错误语句
Item item = (Item) queryForObject("getItem", itemId);
继续跟踪到操纵的sql_map文件Item.xml
<select id="getItem" resultClass="item" parameterClass="string" cacheModel="quantityCache">
select
ITEMID,
LISTPRICE,
UNITCOST,
SUPPLIER AS supplierId,
I.PRODUCTID AS "product.productId",
NAME AS "product.name",
DESCN AS "product.description",
CATEGORY AS "product.categoryId",
STATUS,
ATTR1 AS attribute1,
ATTR2 AS attribute2,
ATTR3 AS attribute3,
ATTR4 AS attribute4,
ATTR5 AS attribute5,
QTY AS quantity
from ITEM I, INVENTORY V, PRODUCT P
where P.PRODUCTID = I.PRODUCTID
and I.ITEMID = V.ITEMID
and I.ITEMID = #value#
</select> 问题就出现在这，Item类中包含了Product类，出现NestedSqlException错误。
查阅了ibatis帮助，此处sql_map的嵌套类写法是正确的。我又将这个sql语句拷贝到MsSqlServer中执行，结果也是正确的。陷入困惑。
在网上找资料，发现了一个英文网站中指明了
--- Cause: java.sql.SQLException: [DataDirect][SQLServer JDBC Driver][SQLServer]Ambiguous column name 'itemid'.
哈哈，问题就此找到了。即itemid列指待不清，应该将ITEMID替换成I.ITEMID AS ITEMID。而之所以我直接将语句拷贝到MsSqlServer执行正确，是因为MSSqlServer自动做了处理，加上了表的别名。而这个Sql语句本身是存在问题的。
所以item.xml替换后的代码为
<select id="getItem" resultClass="item" parameterClass="string" cacheModel="quantityCache">
select
I.ITEMID AS ITEMID,
LISTPRICE,
UNITCOST,
SUPPLIER AS supplierId,
I.PRODUCTID AS "product.productId",
NAME AS "product.name",
DESCN AS "product.description",
CATEGORY AS "product.categoryId",
STATUS,
ATTR1 AS attribute1,
ATTR2 AS attribute2,
ATTR3 AS attribute3,
ATTR4 AS attribute4,
ATTR5 AS attribute5,
QTY AS quantity
from ITEM I, INVENTORY V, PRODUCT P
where P.PRODUCTID = I.PRODUCTID
and I.ITEMID = V.ITEMID
and I.ITEMID = #value#
</select> 问题解决，OK！诶，乱码也耽误了一些时间，否则早就解决了，看来乱码误事啊！
posted on 2007-01-16 13:38
涤生 阅读(3356)
评论(6) 编辑 收藏