数据入库条数限制解决

数据入库条数限制解决

postgresql对于sql语句的参数数量是有限制的，最大为32767。及行数*列数最大值为32767

通过subList()方法将集合中的数据进行分批插入

package com.xj.utils;

import com.xj.entity.commen.InsertBatch;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program:Java_trs
 * @description:批量插入数据
 * @author:尚龙龙
 * @create:2021-03-11-09:56
 **/
public class InsertBatchUtil {

    public <T> List<InsertBatch> subList(List<T> list) {
        Map map = new HashMap();
        List<InsertBatch> listArray = new ArrayList();
        int columnNum = list.get(0).getClass().getDeclaredFields().length;
        int numberBatch = columnNum * list.size(); //上传参数总数据量
        int result = 0;
        double number = numberBatch / 32767; //数据量拆分
        if (numberBatch%32767 == 0){
            number = number;
        }else{
            number = number+1;
        }
        int n = ((Double)Math.ceil(number)).intValue(); //向上取整
        for(int i = 0; i < n; i++){
            int end = list.size()/n;
            if (i == n-1){
                end = list.size() - end * i;
            }
            InsertBatch insertBatch = new InsertBatch();
            insertBatch.setBeginIndex(end*(i));
            insertBatch.setEndIndex(end*(i+1));
            listArray.add(insertBatch);
        }
        return listArray;
    }
}

Service层
调用进行分批插入操作

    @Override
    public int insert(List<WkKbusiness> wkKbusiness){
        int result = 0;
        InsertBatchUtil insertBatchUtil = new InsertBatchUtil();
        List<InsertBatch> list = insertBatchUtil.subList(wkKbusiness);
        for (InsertBatch insertBatch:list){
            result = wkKbusinessMapper.insert(wkKbusiness.subList(insertBatch.getBeginIndex(), insertBatch.getEndIndex())); //插入数据库
        }
        return result;
    }