软讯网络 > 编程语言 > Java > ibatis JshopStore5的sqlmap的xml错误，NestedSQLException错误
【标 题】：ibatis JshopStore5的sqlmap的xml错误，NestedSQLException错误
【关键字】：
ibatis,JshopStore5,sqlmap,xml,NestedSQLException
【来 源】：http://www.blogjava.net/mintqzy/archive/2007/01/16/94203.html
ibatis JshopStore5的sqlmap的xml错误，NestedSQLException错误
今天在继续研究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！诶，乱码也耽误了一些时间，否则早就解决了，看来乱码误事啊！
|