IBatis SqlParameter参数数量超过2100报错

在使用IBatis框架时，绝大多数情况下都不会出现传入参数超过限制（2100个）的情况

但是当SQL中含有 IN 的场合，有时候参数就会超过限制

 

如以下SQL文Where条件中项目 alcCode 的参数： 

<select id="selectStoreSalesByDate" parameterClass ="Hashtable" resultClass="Hashtable">
    SELECT a.alcCode
    ,d.positionID
    ,[salesDate]
    ,c.[productID]
    ,REPLACE(c.category ,'*','') AS category
    ,c.shortName
    ,SUM([quantity_OUT_A]) AS [quantity_OUT_A]
    ,SUM([sales_OUT_A]) AS [sales_OUT_A]
    ,SUM([quantity_OUT_T]) AS [quantity_OUT_T]
    ,SUM([sales_OUT_T]) AS [sales_OUT_T]

    FROM [storeSales] a,
    sellinRelation b,
    product c,
    dummyman d

    WHERE
    a.alcCode =b.alcCode
    AND a.productID =c.productID
    AND b.staffID =d.staffID
    <![CDATA[ AND convert(INT,a.salesDate) >= #salesDateStart#]]>
    <![CDATA[ AND convert(INT,a.salesDate) <= #salesDateEnd#]]>
    AND a.alcCode IN
    <iterate property="alcCodeList" open="(" close=")" conjunction=",">
      #alcCodeList[]#
    </iterate>
    AND d.positionLevel = #positionLevel#
      
      GROUP BY
      a.alcCode
      ,d.positionID
      ,[salesDate]
      ,c.[productID]
      ,c.category
      ,c.shortName

      ORDER BY
      a.alcCode ASC,
      d.positionID ASC,
      c.productID ASC,
      a.salesDate ASC

 

此时，我们就需要对传入的参数进行拆分

本例中，需要传入的参数有4个，分别是字符串类型的salesDateStart、salesDateEnd、positionLevel 和ArrayList类型的 alcCodeList

主要拆分工作就集中在变量 alcCodeList身上。

拆分代码如下：

            '由于IBatis对导入sql的参数有个数限制（不能超过2100），
            '为了程序计算方便，将导入的门店编码List进行每1000条数据分割
            If alcCodeList.Count >= 1000 Then
                dataCount = alcCodeList.Count \ 1000

                '重新设定临时objcet的位数
                ReDim obj(dataCount)

                For i = 0 To dataCount Step 1
                    '初始化
                    tmpList = New ArrayList

                    If i < dataCount Then

                        For j = i * 1000 To (i + 1) * 1000 - 1 Step 1
                            tmpList.Add(alcCodeList.Item(j))
                        Next j

                        '最后一次分割
                    ElseIf i = dataCount Then
                        For j = i * 1000 To alcCodeList.Count - 1 Step 1
                            tmpList.Add(alcCodeList.Item(j))
                        Next j
                    End If

                    '将分割后的List追加到临时obj中
                    obj(i) = tmpList
                Next i

                '初始化
                tmpList = New ArrayList

                '对分割后的门店编码List进行循环查询
                For i = 0 To dataCount Step 1
                    '初始化
                    hashTable.Clear()

                    hashTable.Add("salesDateStart", reportYear - 1 & "01")
                    hashTable.Add("salesDateEnd", reportYYYYMM)
                    hashTable.Add("alcCodeList", obj(i))
                    hashTable.Add("positionLevel", Constant.DUMMYMAN_POSITION_SR)

                    '取得指定年度内所有SR负责的门店的Sellout Actual和Target信息
                    tmpList = readDataFromDB.selectStoreSalesByDate(hashTable)

                    '将取得的结果追加到selloutList中
                    For j = 0 To tmpList.Count - 1 Step 1
                        selloutList.Add(tmpList.Item(j))
                    Next j
                Next i


                '门店编码List个数小于IBatis的参数限制，直接利用
            Else
                hashTable.Add("salesDateStart", reportYear - 1 & "01")
                hashTable.Add("salesDateEnd", reportYYYYMM)
                hashTable.Add("alcCodeList", alcCodeList)
                hashTable.Add("positionLevel", Constant.DUMMYMAN_POSITION_SR)

                '取得指定年度内所有SR负责的门店的Sellout Actual和Target信息
                selloutList = readDataFromDB.selectStoreSalesByDate(hashTable)
            End If