iBATIS中替换“IN”的方法

来源：互联网发布：防出轨软件编辑：程序博客网时间：2024/06/04 01:05

IN的危害

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

如何消灭IN

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

步骤

创建函数 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
-----------------------------
组织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
配置定长参数，由于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