今天在繼續研究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
滌生 閱讀(3367)
評論(6) 編輯 收藏