先看一段代码:
<select id="select" resultMap="HotelinfoResult" parameterClass="java.util.Map">
select
id,
HotelId as hotelId,
Selectflag as selectflag
from Hotelinfokuxun
<dynamic prepend=" WHERE ">
<isPropertyAvailable property="idList">
<isNotNull property="idList" prepend=" and id in ">
<iterate property="idList" conjunction="," close=")" open="(">
#idList[]#
</iterate>
</isNotNull>
</isPropertyAvailable>
</dynamic>
</select>
预计预计产生的SQL是:
select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE id in (?,?,?,?)
需要注意的是:iBatis对于第一个prepend=" and " 是要移除的,如果不移除,则SQL 语句就是:
select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE and id in (?,?,?,?) //显然这样的SQL是错误的,iBatis移除了字句中第一个and
显然and是不能存在的,iBatis对于动态SQL移除第一个and,但是本例中in字句还包含” and id in",也同时被移除了,所以程序产生报错:
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the Hotelinfokuxun.select-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Operand should contain 1 column(s)
当前面的<isPropertyAvilable>都没有匹配上, prepend="and id in "子句作为第一个prepend时,就会被移除,程序就报错
实际产生的SQL语句是:
select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE (?,?,?,?) //少了应该存在的" id in "
解决方法:
一:增加一个无效的1='1'
<isPropertyAvailable property="idList">
<isNotNull prepend=" and ">
1='1'
</isNotNull>
<isNotNull property="idList" prepend=" and id in ">
<iterate property="idList" conjunction="," close=")" open="(">
#idList[]#
</iterate>
</isNotNull>
</isPropertyAvailable>
产生的SQL语句:
select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE 1='1'and id in (?,?,?,?)
二:放弃使用In子句
<isPropertyAvailable property="idList">
<isNotNull property="idList" prepend=" and ">
<iterate property="idList" conjunction="OR" close=")" open="(">
id=#idList[]#
</iterate>
</isNotNull>
</isPropertyAvailable>
产生的SQL语句是:
select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE ( id=? OR id=? OR id=? OR id=?)