Excel批量导入、导出

最新推荐文章于 2024-12-08 12:56:01 发布

这孩子叫逆

最新推荐文章于 2024-12-08 12:56:01 发布

阅读量1k

点赞数 24

分类专栏：工具unit java 文章标签： excel

本文链接：https://blog.youkuaiyun.com/zhzjn/article/details/143059221

版权

java 同时被 2 个专栏收录

96 篇文章

订阅专栏

工具unit

61 篇文章

订阅专栏

1.Mybatis的执行器（了解）

执行器（Executor）是一个核心组件，负责处理SQL语句的执行、结果集的处理、事务管理以及缓存机制。

执行器的主要职责包括：

SQL执行：执行器根据传入的SQL语句和参数，生成并发送到数据库执行。
结果映射：执行SQL语句后，执行器会将数据库返回的结果集映射到Java对象上，这个过程称为结果映射。
事务管理：执行器负责处理事务的开始、提交或回滚，确保数据的一致性和完整性。
缓存机制：执行器管理一级缓存（本地缓存）和二级缓存（跨SqlSession的缓存），可以有效减少数据库访问次数，提高应用性能。
批处理：某些执行器类型（如REUSE和BATCH）支持批处理操作，可以一次性执行多条SQL语句，减少网络往返次数，提高执行效率。

在进行Excel批量导入导出时需要了解执行器的批处理职责。

2.执行器（Executor）的类型

1.ExecutorType.SIMPLE：

ExecutorType.SIMPLE是默认的执行方式。

每次执行SQL语句时，都会打开一个新的PreparedStatement对象。

每条SQL语句都会立即被执行并提交到数据库。

适用于常规的单个或少量SQL操作。

注：PreparedStatement对象是 Java 中用于执行预编译 SQL 语句的接口。PreparedStatement 具有以下特点：①预编译②参数化查询（防止SQL注入）③性能优化

2. ExecutorType.BATCH：

ExecutorType.BATCH会将一批SQL语句集中在一起批量执行，减少了与数据库的交互次数，提高性能。

多条SQL语句会一起提交到数据库执行，可以提升执行效率。

可以通过 sqlSessionFactory.openSession(ExecutorType.BATCH) 方法手动触发批量执行。

适用于需要执行大量SQL操作的场景，如批量插入、更新或删除多条记录。

3.ExecutorType.REUSE

当多次调用相同的SQL语句时，会重用已编译的SQL语句和执行计划。

适用于单条SQL语句的重复执行，例如在一个循环中多次执行相同的SQL语句。

每次执行都会创建一个新的Statement对象，但会重用已编译的SQL语句和执行计划，以提高执行效率。

执行器类型总结：

ExecutorType.SIMPLE 适用于常规的单个或少量SQL操作；ExecutorType.BATCH 批量执行大量SQL操作的场景；ExecutorType.REUSE 适用于单条SQL语句的重复执行。

3.代码实现：

需要的依赖：

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.16</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.1.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

Excel数据批量导入数据库

 @PostMapping
    public String insertExcel1() throws Exception {
        // 使用Hutool读取Excel文件
        ExcelReader reader = ExcelUtil.getReader("D:\\Download\\AA\\file.xlsx");
        //将读取到的 reader 转化为 List<Map>集合
        List<Student> students = reader.readAll(Student.class);

        //StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
        StopWatch stopWatch = new StopWatch();
        //读取数据的结束时间同时也是写入数据库的开始时间
        stopWatch.start();
        //sqlSessionFactory是通过ioc容器注入的  设置其SqlSession的执行器格式
ExecutorType.SIMPLE（默认）
        //通过 sqlSessionFactory 创建一个使用批处理执行器并且不自动提交事务的 SqlSession 实例
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        ExcelMapper mapper = sqlSession.getMapper(ExcelMapper.class);
        //循环将List<Map>中的数据插入数据库
        for (Student student : students) {
            mapper.insert(student);
        }

        //批量插入
//        mapper.insertBatch(students);
        sqlSession.commit();
        stopWatch.stop();
        sqlSession.close();
        System.out.println("插入数据库最终的结果为：" + stopWatch.getTotalTimeSeconds());

        return "ok";
    }

sqlSessionFactory.openSession(...)：这是通过 SqlSessionFactory 实例来打开一个 SqlSession 的方法。SqlSessionFactory 是 MyBatis 的核心接口之一，用于创建 SqlSession 实例。
ExecutorType.BATCH：这个参数指定了 MyBatis 应该使用哪种执行器（Executor）来执行SQL语句。ExecutorType.BATCH 表示使用批处理执行器。批处理执行器会收集所有的SQL语句，在会话结束时（即调用 session.commit() 或 session.rollback() 时）一次性将它们发送到数据库执行。这对于需要执行大量插入、更新或删除操作的情况特别有用，因为它可以显著提高性能。
false：这个参数指定了是否自动提交事务。在这个例子中，传递了 false，意味着事务不会自动提交。这意味着你需要在适当的时候手动调用 session.commit() 来提交事务，或者在需要时调用 session.rollback() 来回滚事务。这提供了更细粒度的事务控制。

使用批处理执行器时，你应该注意以下几点：

确保在会话结束时正确提交或回滚事务，以避免数据不一致。
批处理执行器可能会占用更多的数据库连接资源，因此在使用时要考虑数据库的连接池配置。
由于批处理执行器会延迟SQL语句的执行，因此在执行过程中可能会遇到一些与立即执行SQL语句不同的行为（例如，某些数据库约束可能在批处理结束时才得到验证）。

数据库数据批量导出Excel表

同时大量数据导出需要计算分页参数，调用Mapper方法获取数据，然后使用EasyExcel进行数据导出。这里的关键是每次只从数据库中获取一部分数据，然后写入Excel，避免一次性加载大量数据导致内存溢出。所以使用分页查询。

在数据库查询中，`OFFSET`关键字主要用于分页查询，它指定了查询结果中应该跳过的初始记录数。`OFFSET`通常与`LIMIT`关键字一起使用，以实现从数据库中检索特定范围的记录

SELECT * FROM table_name
  OFFSET 10
  LIMIT 20;

OFFSET 10 意味着查询将跳过前10条记录。

LIMIT 20 意味着查询将返回接下来的20条记录。

OFFSET 0 LIMIT 10 ：相当于从0开始查询，返回十条结果。

 @GetMapping("/write")
    public void exportExcel() throws IOException {
        // 设置文件保存路径
        String filePath = "D:\\Download\\AA\\test1.xlsx";

        // 创建Excel写入器
        File file = new File(filePath);
        // 检查文件是否存在，如果不存在则创建
        if (!file.exists()) {
            // 创建父目录
            file.getParentFile().mkdirs();
            // 创建文件
            file.createNewFile();
        }

        // 创建ExcelWriter对象（写入器），参数为文件路径
        ExcelWriter writer = EasyExcel.write(file.getPath()).build();
        //读取到一张表中,调用EasyExcel工具类读取数据，并将结果写入到指定的Sheet中，
        // .head 会根据这个类的属性自动生成Excel的标题行。
        WriteSheet sheet = EasyExcel.writerSheet("Sheet1").head(Student.class).build();

        // 分页参数
        int pageNum = 1;
        int pageSize = 10000; // 每次读取的数量，根据实际情况调整

        // 循环读取数据，直到没有数据为止
        boolean hasMore = true;
        while (hasMore) {
            //从第几条开始查询。
            int offset = (pageNum - 1) * pageSize;
            List<Student> students = excelMapper.selectAllByPage(offset, pageSize);
            //批量导出多张表
            // WriteSheet sheet = EasyExcel.writerSheet("Sheet" + pageNum).head(Student.class).build();
            //检查查询结果是否为空，如果不为空，说明还有数据需要写入。
            if (!students.isEmpty()) {
                writer.write(students, sheet);
                pageNum++;
            } else {
                hasMore = false;
            }
        }


        // 完成写入，结束并释放ExcelWriter对象占用的所有资源
        writer.finish();
    }

多线程导入数据到数据库中

理论上使用多线程导入数据时，数据库中数据的顺序不能确定。这是由多线程环境的本质所决定的，因为在多线程环境下，线程的调度是由操作系统或JVM调度器控制的，这导致线程的执行顺序不可预测。

即使线程按照一定的顺序开始执行，由于网络延迟、数据库锁、查询优化等因素，实际数据插入到数据库中的顺序也可能与预期的不同。

就算使用自定义的有序队列，或者使用LinkedBlockingQueue和ArrayBlockingQueue都有序队列。两者是按照先进先出（First-In-First-Out, FIFO）原则工作的队列，这意味着它们在概念上都是有序的；由于多个线程可能同时从队列中取出元素，实际的处理顺序可能会因为线程调度的不确定性而略有不同。

  @PostMapping("/insertExcel2")
    public String insertExcel2() {
        // 创建固定大小的线程池
        ExecutorService executor = Executors.newFixedThreadPool(10);

        // 读取Excel文件并获取Sheet对象
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file("D:\\Download\\AA\\test1.xlsx"), "sheet1");
        // 获取Sheet对象
        Sheet sheet = reader.getSheet();

        // 获取当前时间
        long startTime = System.currentTimeMillis();

        // 获取总行数
        int totalRows = sheet.getLastRowNum() + 1; // 注意：getLastRowNum()返回的是最后一行的索引，因此需要+1

        //定义各批次的处理数据量
        int batch = 1000;
        // 定义计数器，并计算需要的总批次数。
        CountDownLatch latch = new CountDownLatch(totalRows / batch + (totalRows % batch > 0 ? 1 : 0));


        for (int i = 1; i < totalRows; i += batch) {
            final int start = i;
            final int end = Math.min(i + batch - 1, totalRows);
            executor.submit(() -> {
                List<Student> read = reader.read(0, start, end, Student.class);
                excelMapper.insertBatch(read);
                // 一次插入事件完成之后，计数器减一。
                latch.countDown();
            });
        }

        try {
            // 等待所有任务完成
            latch.await();
            long endTime = System.currentTimeMillis();
            System.out.println("最终的结果为：" + (endTime - startTime));
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            e.printStackTrace();
        } finally {
            // 关闭线程池
            executor.shutdown();
        }
        return "ok";
    }

https://meeting.tencent.com/v2/cloud-record/share?id=c17a8d67-9ec9-4479-9bd7-2fd57c38ef23&from=3