在使用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