Ibatis prepend iterate label, a summary of the in statements

First look at a piece of code:

<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> 

The expected SQL statement is:

select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE id in (?,?,?,?) 

Note that ibatis is to be removed for the first prepend = "and". If it is not removed, the SQL statement is:

Select ID, Region ID as Region ID, selectflag as selectflag from Region infok where and ID in (?,?,?,?) // Obviously, this SQL statement is incorrect. ibatis removes the first and

Obviously, and cannot exist. ibatis removes the first and for dynamic SQL statements, but the in clause also contains "and ID in" and is also removed, so the program generates an error:

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)

The current <ispropertyavilable> does not match. When the prepend = "and ID in" clause is the first prepend, It is removed and the program reports an error.

The actual SQL statement is:

Select ID, Region ID as Region ID, selectflag as selectflag from Region infok where (?,?,?,?) // The "ID in" that should exist is missing"

Solution:

1. Add an invalid 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 statement:

select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE 1='1'and id in (?,?,?,?) 

Ii. Discard the in Clause

<isPropertyAvailable property="idList"><isNotNull property="idList" prepend=" and "><iterate property="idList" conjunction="OR" close=")" open="(">id=#idList[]#</iterate></isNotNull> </isPropertyAvailable>

The generated SQL statement is:

select id, HotelId as hotelId,Selectflag as selectflag from Hotelinfok WHERE ( id=? OR id=? OR id=? OR id=?)