The incoming request has too many parameters. The server supports a maximum of 2100 parameters
excel 批量导入时 mybatis 动态拼接参数不能超出2100个,超出就抛出这个异常,所以我们需要分段插入,分段根据当时的场景需要每段设置多少条
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
/**
* excel 分批导入
*/
@Component
public class SqlBatchConfig {
private final Logger log = LoggerFactory.getLogger(SqlBatchConfig.class);
@Autowired
private SqlSessionFactory sqlSessionFactory;
/**
*
* @param packageName Dao方法名
* @param list 传入list集合
*/
public void save(String packageName, List list) {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
try {
//批量插入的数据分批处理
int batchCount = 500; //每批commit的个数
int batchIndex = (int) Math.ceil(list.size() / batchCount);// 每批下标
List tempList = new ArrayList<>(batchCount);
int start ,stop ;
for (int i = 0; i < batchIndex ; i++) {
tempList.clear();
start = i * batchCount;
stop = Math.min(i * batchCount + batchCount - 1, list.size() - 1);
log.info("条数区间:" + start + " - " + stop);
for (int j = start; j <= stop; j++) {
tempList.add(list.get(j));
}
sqlSession.insert(packageName, tempList);
sqlSession.commit();
sqlSession.clearCache();
log.info("已经插入" + (stop + 1) + " 条");
}
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
}