大数据查询时Mysql报错Max_Allowed_Packet超出最大连接数，除了调整mysql的最大连接数，java代码方式解决版本

大数据查询时Mysql报错Max_Allowed_Packet超出最大连接数，除了调整mysql的最大连接数，java代码方式解决版本

1.方式一：
1.1常量mysql连接数

public enum MysqlConfiguration{
    /*
      大于mysql的查询最大连接数 分页查询6277450 > 4194304
      目前最大的是4194304
     目前最大的是4000000
     */
    Max_Allowed_Packet(4000000,"最大允许查询数")
    ;
    private final int value;
    private final String name;


    MysqlConfiguration(int value, String name) {
        this.value = value;
        this.name = name;
    }
}

1.2 代码编写

        //大于mysql的查询最大连接数 分页查询6277450 > 4194304
        if (fieldIds.size() > MysqlConfiguration.Max_Allowed_Packet.getValue()){
            int pagesize = MysqlConfiguration.Max_Allowed_Packet.getValue();
            int pageCount = (int) Math.ceil(fieldIds.size()* 1.0 / MysqlConfiguration.Max_Allowed_Packet.getValue());

            for (int i = 0; i < pageCount; i++) {
                List<Long> subsetList=new ArrayList<>(pagesize);
                if (i == pageCount-1) {
                    subsetList = fieldIds.subList(pagesize*i,fieldIds.size());
                }else {
                    subsetList = fieldIds.subList(pagesize*i, pagesize*(i+1));
                }
                List<ResourceArchivesField> list = this.list(
                        new LambdaQueryWrapper<实体类>()
                                .in(实体类::getId, subsetList)
                                .eq(实体类::getDataValid, true)
                                .select(实体类::getId, 实体类::getFieldType, 实体类::getNecessary, 实体类::getCode));

                allList.addAll(list);
            }

2.方式二：抽出公共方法
2.1 抽出公共工具类

import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;

/**
 * @ClassName BigDataQueryUtils 
 * @description: 分页查询，ids数据量过大工具类
 */
@Component
public class BigDataQueryUtils {

    /**
     *
     * @param ids 查询的id集合
     * @param queryFunction
     * @param pageSize 分页大小
     * @param <T>
     * @return
     */
    public static <T> List<T> queryWithPagination(List<Long> ids, Function<List<Long>, List<T>> queryFunction, int pageSize) {
        List<T> allList = new ArrayList<>();
        int pageCount = (int) Math.ceil(ids.size() * 1.0 / pageSize);

        for (int i = 0; i < pageCount; i++) {
            List<Long> subsetList = new ArrayList<>(pageSize);
            if (i == pageCount - 1) {
                subsetList = ids.subList(pageSize * i, ids.size());
            } else {
                subsetList = ids.subList(pageSize * i, pageSize * (i + 1));
            }

            List<T> list = queryFunction.apply(subsetList);
            allList.addAll(list);
        }

        return allList;
    }
}

2.2 代码编写优化,简洁干净

        allList = BigDataQueryUtils.queryWithPagination(fieldIds, subsetList ->
                this.list(new LambdaQueryWrapper<实体类>()
                        .in(实体类::getId, subsetList)
                        .eq(实体类::getDataValid, true)
                        .select(实体类::getId, 实体类::getFieldType, 实体类::getNecessary, 实体类::getCode)),
                MysqlConfiguration.Max_Allowed_Packet.getValue());

2.3 工具类也可以优化java8写法和普通优化写法

public <T> List<T> queryWithPagination(List<Long> ids, Function<List<Long>, List<T>> queryFunction, int pageSize) {
    List<T> allList = new ArrayList<>();
    int pageCount = (int) Math.ceil(ids.size() * 1.0 / pageSize);

    for (int i = 0; i < pageCount; i++) {
        final List<Long> subsetList = ids.subList(pageSize * i, Math.min(pageSize * (i + 1), ids.size()));
        List<T> list = queryFunction.apply(subsetList);
        allList.addAll(list);
    }

    return allList;
}

java8写法

public <T> List<T> queryWithPagination(List<Long> ids, Function<List<Long>, List<T>> queryFunction, int pageSize) {
    int pageCount = (int) Math.ceil(ids.size() * 1.0 / pageSize);
    return IntStream.range(0, pageCount)
            .mapToObj(i -> ids.subList(pageSize * i, Math.min(pageSize * (i + 1), ids.size())))
            .map(queryFunction)
            .flatMap(Collection::stream)
            .collect(Collectors.toList());
}