如何处理SQL Select语句(IBatis 2)中的Empty IN子句?

问题描述：

我已经在IBatis版本2中编写了以下SQL语句:

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>

如果myInClauseValues(这是一个带有Long的ArrayList)包含至少一个值，则该语句可以正常工作.但是，如果myInClauseValues为空，则会收到这样的错误消息(我使用的是oracle数据库):

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

答

使用Ibatis动态查询是正确的方法吗?
例如:

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>

如何处理SQL Select语句(IBatis 2)中的Empty IN子句?

相关推荐