mybatis+sqlserver批量update操作数据库报错：The server supports a maximum of 2100 parameters

sqlserver中，update操作报错，会判定长度是否超过2100，超过就会报错，我这边更新超过100就报错了（我还需要研究一下为什么提交100条数据就报错了，并没有超过2100条啊）。

针对批量操作中出错，解决办法，如下：

 

---

 

1.xxxServiceImpl.java

 

 

 

 2.xxxMapper.java

 

 

3. xxxMapper.xml

    
    <update id="updatePoInfo"
        parameterType="java.util.List">
             <foreach collection="updatePoList" item="p" index="index" open="" close="" separator=";">
        update production_order
        <set>
        plant = #{p.plant},
        order_type = #{p.orderType},
        batch_no =#{p.batchNo},
        customer_code = #{p.customerCode},
        customer_name
        =#{p.customerName},
        material_no = #{p.materialNo},
        mode_no =#{p.moldNo},
        cust_material_no =
        #{p.custMaterialNo},
        order_qty =#{p.orderQty},
        uom =
        #{p.uom},
        packing_category =#{p.packingCategory},
        country = #{p.country},
        mpt
        =#{p.mpt},
        mpq = #{p.mpq},
        mpt_material_no =#{p.mptMaterialNo},
        length =
        #{p.length},
        width =#{p.width},
        high = #{p.high},
        apt =#{p.apt},
        apq = #{p.apq},
        assortment_id
        =#{p.assortmentID},
        assortment_description =
        #{p.assortmentDescription},
        total_carton_qty =#{p.totalCartonQty},
        rel_date
        =#{p.relDate},
        locco =
        #{p.locco},
        airplane_type =#{p.airplaneType},
        zscxs =
        #{p.zscxs}
</set>
        where
        production_order = #{p.productionOrderNo}
      </foreach>
    </update>

 

 

4.运行后报错：

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
; uncategorized SQLException; SQL state [S0001]; error code [8003]; The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy89.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy3002.updatePoInfo(Unknown Source)
...................

 

 

 

 

修改后：

只用修改  xxxServiceImpl.java 就行：

 

 

代码：

logger.info("生产订单经过箱数字段为null检验过后，最终需要更新的po数据,共计:" + updatePoList.size() + "条");
                try {
                    if (!updatePoList.isEmpty() || updatePoList.size() > 0) {
                        //this.productionOrderMapper.updatePoInfo(updatePoList);// 更新箱数为空的po
                        
                        // 由于数据库对于插入字段的限制为2100，在这里对批量插入的数据进行分批处理
                        int numms = updatePoList.size();
                        int sqlResult = 0;
                        List<PoTableVO> clientsnew = new ArrayList<>();
                        for (int j = 1; j <= updatePoList.size(); j++) {
                            clientsnew.add(updatePoList.get(j - 1));
                            if (j % 60 == 0) { // 每次插入数据库的条数，建议值控制在60以内。
                                sqlResult = productionOrderMapper.updatePoInfo(clientsnew);
                                if (sqlResult > 0) {
                                    logger.info("箱数为null的po更新成功");
                                }
                                clientsnew.clear();// 清空clientsnew。
                            } else if (j == numms) {// 只插入最后一条。
                                sqlResult = productionOrderMapper.updatePoInfo(clientsnew);
                                if (sqlResult > 0) {
                                    logger.info("箱数为null的po更新成功");
                                }
                            }
                        }
                    }
                } catch (Exception e3) {
                    logger.error("箱数为null的po更新失败" + e3.getMessage());
                    e3.printStackTrace();
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();// 回滚
                    return ResultRsp.ofFail(CodeEnum.FAIL_GETDATA);
                }

 

 

 

 

 

 

 

.