Excel文件导入导出，SpringBoot整合EasyExcel批量导入导出，采用的JDBC+EasyExcel（附带整个Demo）

时间：2024-03-21 12:02:13

目录

0.为什么mybatis的foreach比JDBC的addBatch慢

1.引入依赖

2.Controller层

3.Service层

4.Utils工具类

5.自定义监听器

6.实体类

7Mapper层

不用Mybatis的原因就是因为在大量数据插入的时候jdbc性能比mybatis好

1. 首先分批读取Excel中的数据这一点EasyExcel有自己的解决方案 2.其次就是DB里插入，怎么去插入这20w条数据当然不能一条一条循环，应该批量插入20w条数据 3.使用JDBC+事务的批量操作将数据插入到数据库

整个Demo连接，打开下载即可，包含数据库表

整个Demo连接，打开下载即可，包含数据库表

整个Demo连接，打开下载即可，包含数据库表整个Demo连接，打开下载即可，包含数据库表

整个Demo连接，打开下载即可，包含数据库表

0.为什么mybatis的foreach比JDBC的addBatch慢

ORM 框架开销：MyBatis 的 foreach 操作涉及到将对象数据转换为 SQL 语句的过程，在这个过程中需要进行对象到 SQL 的映射、动态 SQL 的解析等操作，这些额外的操作会增加开销。
数据库连接管理：MyBatis 在执行 foreach 操作时，会频繁地获取和释放数据库连接，而数据库连接的获取和释放是一个相对耗时的操作，特别在百万级数据的情况下，这种开销可能会积累导致性能下降。
SQL 语句生成：MyBatis 的 foreach 操作在执行过程中会生成大量的 SQL 语句，这可能会导致数据库的缓存失效、重新编译查询计划等，从而影响性能。
批量插入优化：JDBC 的 addBatch 可以直接利用底层数据库的批量插入功能，而 MyBatis 的 foreach 操作在某些数据库上可能不能充分利用数据库的批量插入优化。

1.引入依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.2</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.42</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.18</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-tomcat</artifactId>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

2.Controller层

@Slf4j
@RestController
@RequestMapping("excel")
public class ExcelController {

    @Autowired
    private UserService userService;

    //导出
    @GetMapping("/exportExcel")
    public String exportExcel(HttpServletRequest request, HttpServletResponse response){
        String file="D:";
        long startTime = System.currentTimeMillis();
        log.debug("-------------开始插入-------------------");
        userService.exportInspectionPlan(request,response);
        return "ok";
    }


    //导入
    @PostMapping("/importExcel")
    public void importExcel(MultipartFile multipartFile) throws IOException {
        if (multipartFile.isEmpty()) {
            return;
        }
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
        EasyExcel.read(multipartFile.getInputStream(), ExportPlanInformationVo.class,
            new PageReadListener<ExportPlanInformationVo>(dataList -> {
                            for (ExportPlanInformationVo user : dataList) {
                                //将导入的数据用mybatisPlus一个个添加进数据库
                                System.out.println(user);
                            }
        })).sheet("现场巡视计划报表").doRead();
    }
    /**
     * 1. 首先分批读取Excel中的数据
     * 这一点EasyExcel有自己的解决方案
     *
     * 2.其次就是DB里插入，怎么去插入这20w条数据
     * 当然不能一条一条循环，应该批量插入20w条数据
     * 同样不能选择Mybatis的批量插入，因为效率低
     *
     * 3.使用JDBC+事务的批量操作将数据插入到数据库
     * */
    //批量导入
    @PostMapping("/batchImportExcel")
    public void batchImportExcel(MultipartFile file) throws IOException {
        if (BeanUtil.isEmpty(file)){
            log.debug("传入的文件不能为空！");
            return ;
        }
        if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith("xls") && !file.getOriginalFilename().endsWith("xlsx")){
            log.debug("请上传Excel文件！");
            return ;
        }
        CommonExportListenerDto commonExportListenerDto = new CommonExportListenerDto();
        EasyExcel.read(file.getInputStream(),commonExportListenerDto).doReadAll();
    }
}

3.Service层

@Service
@Slf4j
public class UserService {
//
    @Resource
    private IndMapper indMapper;
    //文件导出
    public void exportInspectionPlan(HttpServletRequest request, HttpServletResponse response) {
        //以上需要根据自己的业务去做数据处理   这里就不做展示
        try {
            //给文件命名
            String fileName = "ExcelTest";
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            // 设置防止中文名乱码
            fileName = URLEncoder.encode(fileName, "utf-8");
            // 文件下载方式(附件下载还是在当前浏览器打开)
            response.setHeader("Content-disposition", "attachment;filename=" +
                    fileName + ".xlsx");
            //向excel表格写入数据
            EasyExcel.write(response.getOutputStream(), ExportPlanInformationVo.class)
                    .sheet("下方导航")
                    .doWrite(getAll());
/*
            //下载到指定路径
            String fileUrl = "D://ExcelTest.xlsx";
            //向excel表格写入数据
            EasyExcel.write(fileUrl, ExportPlanInformationVo.class)
                    .sheet("下方导航")
                    .doWrite(getAll());
*/
        } catch (Exception e) {
            log.error("出现错误 {}", e);
        }
    }

    public List<ExportPlanInformationVo> getAll(){
        //根据业务逻辑获取数据
//        List<ExportPlanInformationVo> all = indMapper.getAll();
        return  indMapper.getAll();
    }





}

4.Utils工具类

import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

public class JDBCUtil {

    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false";
    private static String username = "root";
    private static String password = "196713";
    private static String driverName = "com.mysql.jdbc.Driver";

    /**
     * 获取连接对象
     *
     * @return 连接对象
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            // 1. 注册驱动
            Class.forName(driverName);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * 释放资源
     *
     * @param connection 连接对象
     * @param statement  预编译执行对象
     * @param resultSet  结果集
     */
    public static void releaseResources(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        // 释放资源
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void insertBatch(List<Map<Object, Object>> dataList, String sql) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = getConnection();
            //如果需要开启事务此处需要将自动提交关闭
            // conn.setAutoCommit(false);
            //预编译sql
            pstm = (PreparedStatement) conn.prepareStatement(sql);
            for (Map<Object, Object> map : dataList) {
                //此处类型判断不完整后续可以借鉴jdk自行封装拦截器
                for (int i = 1; i <= map.size(); i++) {
                    Object o = map.get(i-1);
                    if (BeanUtil.isEmpty(o)) {
                        pstm.setString(i, null);
                        continue;
                    }
                    if (o instanceof String) {
                        pstm.setString(i, o.toString());
                        continue;
                    }
                    if (o instanceof Integer) {
                        pstm.setInt(i, Integer.parseInt(o.toString()));
                        continue;
                    }
                    if (o instanceof LocalDateTime) {
                        pstm.setDate(i, new Date(System.currentTimeMillis()));
                        continue;
                    }
                    if (o instanceof Boolean) {
                        pstm.setBoolean(i, Boolean.parseBoolean(o.toString()));
                    }
                }
                //添加到同一个批处理中
                pstm.addBatch();
            }
            //执行批处理
            pstm.executeBatch();
            //如果需要开启事务此处需要手动提交事务
            //conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

5.自定义监听器

@Data
@Slf4j
public class CommonExportListenerDto extends AnalysisEventListener<Map<Object, Object>> {

    /**
     * 表头数据（存储所有的表头数据）
     */
    private List<Map<Integer, String>> headList = new ArrayList<>();

    /*
     * 数据体
     */
    private List<Map<Object, Object>> dataList = new ArrayList<>();

    /**
     * 存储全部表头数据
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        headList.add(headMap);
    }

    /**
     * 每一条数据解析都会来调用
     * @param data
     * @param context
     */
    @Override
    public void invoke(Map<Object, Object> data, AnalysisContext context) {
        dataList.add(data);
        if (dataList.size() >= 3) {
            saveData();
            // 存储完成清理 list
            dataList = ListUtils.newArrayListWithExpectedSize(2000);
        }
    }

    /**
     * 所有数据解析完成之后的操作
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", dataList.size());
        //批量导入
        JDBCUtil.insertBatch(dataList,"INSERT INTO ind (a,b,c,d,e) VALUES(?,?,?,?,?);");
        log.info("存储数据库成功！");
    }
}

6.实体类

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 11)
public class ExportPlanInformationVo  implements Serializable {

// 1. 如果不想某个字段在excel中出现  可以加  @ExcelIgnore注解
    @ExcelProperty(value = "a")
    private String a;


//    @Dict(code = "inspectionType", fieldName = "inspectionTypeName")
    @ExcelProperty(value = "b")
    private String b;

    @ExcelProperty(value = "c")
    private String c;
    @ExcelProperty(value = "d")
    private String d;
    @ExcelProperty(value = "c")
    private String e;


}

7Mapper层

@Mapper
public interface IndMapper {

    @Select("select * from ind")
    List<ExportPlanInformationVo> getAll();

}

