JDBC链接数据库查询1000万数据并导出execl表格使用多线程和深度分页

可以连接本地和服务器，本地更快一些，和电脑的性能也有关系

导入依赖

     <!-- easyExcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
        </dependency>

编写自己的实体类

示例代码

@SpringBootTest
class TextApplicationTests {

    // 线程池大小
    private static final int THREAD_POOL_SIZE = 16;
    // 批量处理数据大小
    private static final int BATCH_SIZE = 625000;
    // 全局表格索引，使用AtomicInteger保证线程安全递增
    private static final AtomicInteger globalSheetIndex = new AtomicInteger(0);
    // 同步锁
    private static final Object lock = new Object();

    public static void main(String[] args) {
        // 数据库连接信息
        String jdbcUrl = "jdbc:mysql://服务器或者本地:3306/你的数据库";
        String username = "";
        String password = "";
        // 导出文件路径
        String outputPath = "D:\\sss.xlsx";

        // 记录开始时间
        long startTime = System.currentTimeMillis();

        // 创建一个 ExcelWriter 对象
        ExcelWriter excelWriter = EasyExcel.write(outputPath).excelType(ExcelTypeEnum.XLSX).build();

        // 异步方式从数据库导出数据到 Excel 文件
        exportData(jdbcUrl, username, password, excelWriter);

        // 关闭 ExcelWriter 对象
        excelWriter.finish();

        // 记录结束时间
        long endTime = System.currentTimeMillis();
        // 计算执行时间
        long duration = endTime - startTime;
        // 打印导出耗时
        System.out.println("导出耗时：" + duration + " 毫秒");
    }


    // 导出数据方法
    private static void exportData(String jdbcUrl, String username, String password, ExcelWriter excelWriter) {
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            // 获取总行数
            int rowCount = getTotalRowCount(connection);
            int pageSize = BATCH_SIZE;

            try {
                CountDownLatch latch = new CountDownLatch(THREAD_POOL_SIZE);
                ExecutorService executorService = Executors.newFixedThreadPool(THREAD_POOL_SIZE);

                // 分批次处理数据
                for (int i = 0; i < rowCount; i += pageSize) {
                    int endIndex = Math.min(i + pageSize, rowCount);
                    List<People> subList = fetchData(connection, i, endIndex);


                    executorService.submit(() -> {
                        synchronized (lock) {

                        WriteSheet writeSheet = EasyExcel.writerSheet(globalSheetIndex.getAndIncrement(), "sheet" + globalSheetIndex).build();
                        writeToExcel(excelWriter, subList, writeSheet);
                        }
                        latch.countDown();
                    });
                }

                // 关闭线程池
                try {
                    latch.await(); // 等待所有线程完成
                    executorService.shutdown();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

                // 关闭 ExcelWriter 对象
                excelWriter.finish();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 获取总行数方法
    private static int getTotalRowCount(Connection connection) throws SQLException {
        // 创建统计语句对象，准备查询people表中的记录数
        try (PreparedStatement countStatement = connection.prepareStatement("SELECT COUNT(*) FROM people");
             // 执行统计语句并获取查询结果集
             ResultSet countResultSet = countStatement.executeQuery()) {
            // 从结果集中获取计数信息
            countResultSet.next();
            // 返回记录数
            return countResultSet.getInt(1);
        }
    }


    // 获取分批次数据方法
    private static List<People> fetchData(Connection connection, int startIndex, int endIndex) {
        List<People> dataList = new ArrayList<>();
        try (PreparedStatement dataStatement = connection.prepareStatement("SELECT * FROM people LIMIT ?, ?")) {
            // 设置参数
            dataStatement.setInt(1, startIndex);
            dataStatement.setInt(2, endIndex - startIndex);
            ResultSet resultSet = dataStatement.executeQuery();

            // 遍历结果集
            while (resultSet.next()) {
                People people = new People();
                // 获取数据并设置给people对象的属性
                people.setId(resultSet.getInt("id"));
                people.setName(resultSet.getString("name"));
                people.setAge(resultSet.getInt("age"));
                people.setSex(resultSet.getInt("sex"));
                people.setPhone(resultSet.getString("phone"));
                people.setDescribe(resultSet.getString("describe"));
                people.setStatus(resultSet.getString("status"));
                people.setDeleted(resultSet.getInt("deleted"));
                people.setUpdateTime(resultSet.getString("update_time"));
                people.setUpdater(resultSet.getString("updater"));
                dataList.add(people);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }


    // 写入 Excel 方法
    private static void writeToExcel(ExcelWriter excelWriter, List<People> dataList, WriteSheet writeSheet) {

        try {
            // 累积一定数量的数据再写入
            int batchSize = 1000000;
            for (int i = 0; i < dataList.size(); i += batchSize) {
                int endIndex = Math.min(i + batchSize, dataList.size());
                List<People> subList = dataList.subList(i, endIndex);
                excelWriter.write(subList, writeSheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }




}