iBATIS中替换“IN”的方法

IN的危害

1. 由于in不支持变量绑定。所以，in语句必须使用“$变量$”来描述，为sql注入埋下了隐患。
2. 同样，sql语句解析和执行计划不能复用。
3. 由于执行计划不能复用，当发生很多次IN不同内容调用的时候，会把以前的经常复用的sql语句的执行计划cache给挤出去。

如何消灭IN

1. 把in变成参数。
2. 固定参数的长度。

步骤

1. 创建函数 SplitInt.sql和SplitStr.sql  效果如下：

    
   select * from SplitInt(1,2,3,4,5,6);
   
   select * from SplitStr('a,b,c,d,e,6');
   
   ---------------------
   SplitInt.sql:
   CREATE FUNCTION [dbo].[SplitInt]
   (
    @SplitString varchar(8000)
   )
   RETURNS @SplitIntTable TABLE
   (
    [value] int
   )
   AS
   BEGIN
       DECLARE @Separator varchar(2);
       DECLARE @CurrentIndex int;
       DECLARE @NextIndex int;
       DECLARE @ReturnText int;
       SELECT @CurrentIndex=1;
       SELECT @Separator = ',';
       WHILE(@CurrentIndex<=len(@SplitString))
       BEGIN
           SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
           IF(@NextIndex=0 OR @NextIndex IS NULL)
               SELECT @NextIndex=len(@SplitString)+1;
          
           SELECT @ReturnText=cast(substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex) as int);
           INSERT INTO @SplitIntTable([value])
           VALUES(@ReturnText);
          
           SELECT @CurrentIndex=@NextIndex+1;
       END
       RETURN;
   END
   --------------------------------
   SplitStr.sql:
   CREATE  FUNCTION [dbo].[SplitStr]
   (
    @SplitString varchar(8000)
   )
   RETURNS @SplitStringsTable TABLE
   (
    [value] varchar(8000)
   )
   AS
   BEGIN
       DECLARE @Separator varchar(2);
       DECLARE @CurrentIndex int;
       DECLARE @NextIndex int;
       DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
       SELECT @CurrentIndex=1;
       SELECT @Separator = ',';
       WHILE(@CurrentIndex<=len(@SplitString))
       BEGIN
           SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
           IF(@NextIndex=0 OR @NextIndex IS NULL)
               SELECT @NextIndex=len(@SplitString)+1;
          
           SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
           INSERT INTO @SplitStringsTable([value])
           VALUES(@ReturnText);
          
           SELECT @CurrentIndex=@NextIndex+1;
       END
       RETURN;
   END
   -----------------------------

2. 组织sql

   老格式：
   select t2.* from   lily_project T2 where t2.projectname in ('亚洲一号','测试')
    新格式：
    select t2.* from SplitStr('亚洲一号,测试') T1 , lily_project T2
   where t1.value = t2.projectname

   老格式：
   select t2.* from  lily_project T2 where  t2.project_id in (67,2,3)

   新格式：

   select t2.* from SplitInt('67,2,3') T1 , lily_project T2
   where t1.value = t2.project_id

3. 配置定长参数，由于Sql Server对于不同的参数长度，会产生不过的执行路径的KEY。所以，必须要统一参数长度。这个长度可以指定为1，输入值超过1，不受影响。ibatis的inlineParameter无法设置参数长度，必须依靠ParameterMap来设置。所以，最终效果如下：
   

       <parameterMap id="testjoin" class="string">
           <parameter property="value" dbtype="varchar" size="1"/>
       </parameterMap>

       <select id="testJoinIn" parameterMap="testjoin" resultClass="project">        
           select t2.* from SplitInt(?) T1 , lily_project T2 where t1.value = t2.project_id
       </select>

   
   

 效果

         经过测试，使用上面的语句能提高20%的效率。另外，单个语句执行无提高效果。

其它

        受到启发，其它所有调用的时候。如果参数中有字符串的时候，一定要使用parameterMap形式，并且定义paramter的size