How to handle an Empty IN clause inside a SQL Sele

问题:

I have written the following SQL Statment in IBatis version 2:

<select id="mySelect" resultClass="long" >
    SELECT  COUNT(*)        
    FROM    myTable 
    WHERE   myTable.columnA IN
        <iterate property="myInClauseValues" open="(" close=")" conjunction=",">                    
                #myInClauseValues[]#                    
        </iterate>          
</select>

That statement works fine, if myInClauseValues (this is a ArrayList with Long) include at least one value. But if myInClauseValues is empty, I get an error message like this (I´m using an oracle database):

 Check the statement (query failed). 
 Cause: java.sql.SQLException: ORA-00936: Expression is missing 

回答1:

Use Ibatis dyanmic query be a correct way??
eg:

<select id="mySelect" resultClass="long" >
    SELECT  COUNT(*)        
    FROM    myTable 
   <where>
    <isNotEmpty prepend="AND" property="myInClauseValues" >
       myTable.columnA IN
       <iterate property="myInClauseValues" open="(" close=")" conjunction=",">                    
                    #myInClauseValues[]#                    
       </iterate>       
    </isNotEmpty>   
   </where>      
</select>