问题
com.microsoft.sqlserver.jdbc.SQLServerException: 传入的请求具有过多的参数。该服务器支持最多 2100 个参数。请减少参数的数目,然后重新发送该请求。
方案
java + sqlsever 或 sqlsever存储过程
方案1 java + sqlsever 解决方案
1. 将数据处理成XML格式
if (!StringUtil.isEmpty(dto.getFareaID())) {
String[] split = dto.getFareaID().split(",");
String str = "<root>";
for (String areaStr : split) {
str = str + "<Item>" + areaStr + "</Item>";
}
str = str + "</root>";
dto.setFareaID(str);
} else {
dto.setFareaID(null);
}
2.查询
SELECT mpb.FBatchNo,mbrn.BatchNumber,mpb.FUnitNames
FROM dbo.MY_PigBatch AS mpb
LEFT JOIN dbo.MY_BatchRecordNo AS mbrn ON mbrn.BatchNO=mpb.FBatchNo AND mbrn.CheckFlag=1
<where>
AND mpb.FSegmentID=13907
<if test="dto.fbatchNoSate != null and dto.fbatchNoSate != ''">
and mpb.FState = #{dto.fbatchNoSate}
</if>
<if test="dto.fareaID != null and dto.fareaID != ''">
and CAST(#{dto.fareaID} as xml).exist('/root/Item[text()=sql:column("mpb.FAreaID")]')=1
</if>
<if test="dto.ffieldID != null and dto.ffieldID != ''">
and CAST(#{dto.ffieldID} as xml).exist('/root/Item[text()=sql:column("mpb.FFieldID")]')=1
</if>
</where>
方案2 存储过程解决方案
ALTER PROCEDURE [存储过程名]
@AreaID XML
AS
BEGIN
SET NOCOUNT ON;
select T.c.query('.').value('(Item)[1]','int') as id INTO #tmpArea from @AreaID.nodes('/root/Item') as T(c)
select count(1) from table as a
where @AreaID IS NULL OR EXISTS(SELECT 1 FROM #tmpArea Area WHERE Area.id=a.FAreaID)
sql查询例子
select top 100 * from MY_PigBatch mpb
where CAST('<root><Item>13902</Item><Item>13903</Item></root>' as xml)
.exist('/root/Item[text()=sql:column("mpb.FAreaID")]')=1