备案控制台
探索云世界
开发者社区 开发与运维 文章 正文

jdbc解析excel文件，批量插入数据至库中

2024-03-21 21
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： jdbc解析excel文件，批量插入数据至库中


前言

现在是：2022年5月20日09:32:38

今天遇到了个这样的需求，解析excel表中的数据，以JDBC的方式，将数据批量更新至不同的数据表中。注意，更新指的是：如果数据表中有该条记录，则更新操作，如果没有，则新增操作。

实现思路

解析Excel，直接写了个工具类，先看一下代码，在做说明：

/**
     * 解析excel表格，每行数据，分别插入到两个表中，巡查的视频表
     * 已经执行完成
     * 2022年5月12日21:22:01
     *
     * ↓执行情况如下↓
     * 一共有这么多行：88
     * **************开始执行*************
     * *************执行完毕******************集合的长度是：88
     * equipment表的添加情况88
     * disanfangvedio表的添加情况88
     *
     */
    public static void updateBaseEquipmentAndBaseDiSanFangVedio(){
        String excelPath = System.getProperty("user.dir") + "/ruoyi-admin/src/main/java/zhengshiflowstuisong519.xlsx";
        try {
            //String encoding = "GBK";
            File excel = new File(excelPath);
            //判断文件是否存在
            if (excel.isFile() && excel.exists()) {
                //.是特殊字符，需要转义！！！！！
                String[] split = excel.getName().split("\\.");
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                if ("xls".equals(split[1])) {
                    //文件流对象
                    FileInputStream fis = new FileInputStream(excel);
                    wb = new HSSFWorkbook(fis);
                } else if ("xlsx".equals(split[1])) {
                    wb = new XSSFWorkbook(excel);
                } else {
                    System.out.println("文件类型错误!");
                    return;
                }
                //开始解析
                // 读取sheet 0
                Sheet sheet = wb.getSheetAt(0);
                //第一行是列名，所以不读
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum();
                System.out.println("一共有这么多行："+lastRowIndex);
                System.out.println("**************开始执行**************");
                //将信息放在集合里面，添加的时候好处理
                List<EquipmentAndDiSanFangVedioEntity> equipmentAndDiSanFangVedioEntityListList = new ArrayList<>();
                //遍历行
                for (int rIndex = firstRowIndex; rIndex <=lastRowIndex; rIndex++) {
                    //初始化对象
                    EquipmentAndDiSanFangVedioEntity equipmentAndDiSanFangVedioEntity = new EquipmentAndDiSanFangVedioEntity();
                    BaseEquipment baseEquipment = new BaseEquipment();
                    BaseDisanfangvedio baseDisanfangvedio = new BaseDisanfangvedio();
                    //不会重复吧
                    String uuid = IdUtils.fastSimpleUUID();
                    System.out.print(rIndex+"：");
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        DataFormatter dataFormatter = new DataFormatter();
                        //regionkey
                        Cell cell_regionkey = row.getCell(0);
                        double cell_regionkey_double = Double.parseDouble(cell_regionkey.toString());
                        Long regionkey = (long)cell_regionkey_double;
                        baseEquipment.setDeptId(regionkey);
                        baseDisanfangvedio.setRegionkey(regionkey);
                        baseDisanfangvedio.setVenueId(regionkey.toString());
                        //equipment_name
                        Cell cell_equipment_name = row.getCell(1);
                        String equipment_name =dataFormatter.formatCellValue(cell_equipment_name);
                        baseEquipment.setEquipmentCode(equipment_name);
                        baseDisanfangvedio.setEquipmentName(equipment_name);
                        //province_id
                        Cell cell_province_id = row.getCell(2);
                        String province_id =dataFormatter.formatCellValue(cell_province_id);
                        baseDisanfangvedio.setProvinceId(province_id);
                        //city_id
                        Cell cell_city_id = row.getCell(3);
                        String city_id =dataFormatter.formatCellValue(cell_city_id);
                        baseDisanfangvedio.setCityId(city_id);
                        //district_id
                        Cell cell_district_id = row.getCell(4);
                        String district_id =dataFormatter.formatCellValue(cell_district_id);
                        baseDisanfangvedio.setDistrictId(district_id);
                        //venue_id
                        Cell cell_venue_id = row.getCell(5);
                        String venue_id =dataFormatter.formatCellValue(cell_venue_id);
                        //摄像机名称
                        Cell cell_openArea = row.getCell(6);
                        String openArea =dataFormatter.formatCellValue(cell_openArea);
                        baseEquipment.setEquipmentName(openArea);
                        baseEquipment.setLocation(openArea);
                        //real_time_video_address
                        Cell cell_real_time_video_address = row.getCell(7);
                        String real_time_video_address =dataFormatter.formatCellValue(cell_real_time_video_address);
                        baseDisanfangvedio.setRealTimeVideoAddress(real_time_video_address);
                        baseEquipment.setBrandId("宇视");
                        baseEquipment.setTypeId("1");
                        baseEquipment.setIpAddress(uuid);
                        baseEquipment.setStateId("0");
                        baseEquipment.setOnlineDate(new Date());
                        baseEquipment.setIsCoreArea(1);
                        baseEquipment.setIsExamine("0");
                        baseDisanfangvedio.setDeviceId(uuid);
                        baseDisanfangvedio.setCreateTime(new Date());
                        equipmentAndDiSanFangVedioEntity.setBaseEquipment(baseEquipment);
                        equipmentAndDiSanFangVedioEntity.setBaseDisanfangvedio(baseDisanfangvedio);
                        //添加到集合里面
                        equipmentAndDiSanFangVedioEntityListList.add(equipmentAndDiSanFangVedioEntity);
                    }
                }
                System.out.println("*************执行完毕******************\n集合的长度是："+equipmentAndDiSanFangVedioEntityListList.size());
                System.out.println("请稍等…………");
                System.out.println("正在插入到数据库…………");
                System.out.println("再等等…………");
                //调用更新的方法
                ZhiXingMysqlNew zhiXingMysqlNew = new ZhiXingMysqlNew();
                //暂时先注释掉，怕误调用此方法 2022年5月18日17:05:28
                zhiXingMysqlNew.updateBaseEquipmentAndBaseDiSanFangVedio(equipmentAndDiSanFangVedioEntityListList);
                System.out.println("插入完毕！！！");
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
代码说明
  • 此类专门用作解析Excel文件
  • 将每列需要用到的信息，解析之后放在集合中，更新时需要用
  • 调用更新的方法进行更新操作

更新操作

这个方法是专门用来更新操作的，实现思路：

  1. 根据指定的条件查询数据表中该记录是否存在，必须是唯一的，可以多个条件组合
  2. 如果有记录，则执行更新操作
  3. 如果没有记录，则执行添加操作
  4. 均采用JDBC批量更新的方式，addBatch、executeBatch、clearBatch
  5. 最后关闭流

代码如下：

/**
     * 更新base_equipment表和base_disanfangvedio表
     * 1.先去视频表里面查询，是否有记录
     * 2.如果有记录，则更新视频表
     * 3.如果没有记录，则两个表都插入一条记录
     * 4.判断地址是否为空，如果是空，则值给设备表中插入数据
     * 
     *
     * @param equipmentAndDiSanFangVedioEntityList
     */
    public void updateBaseEquipmentAndBaseDiSanFangVedio(List<EquipmentAndDiSanFangVedioEntity> equipmentAndDiSanFangVedioEntityList) {
        Connection connection = connection = getConnection();
        //添加
        PreparedStatement pStatementEquInsert = null;
        PreparedStatement pStatementDsfInsert = null;
        //修改
        PreparedStatement pStatementEquUpdate = null;
        PreparedStatement pStatementDsfUpdate = null;
        //查询
        PreparedStatement pStatementEquipmentSelect = null;
        ResultSet rs = null;
        try {
            //先根据场馆id和点位名称查询信息
            String sqlEquipmentSelect = "SELECT * FROM base_equipment WHERE dept_id = ? AND equipment_name =  ?";
            //新增的sql语句
            String sqlEquipmentInsert = "  INSERT INTO base_equipment \n" +
                    "( equipment_code, equipment_name,dept_id, \n" +
                    "location, brand_id, type_id, ip_address, \n" +
                    "state_id, \n" +
                    "online_date,  is_core_area,is_examine ) \n" +
                    " VALUES ( ?,?,?,?,?,?,?,?,?,?,?)";
            String sqlBaseDisanFangVedioInsert = " INSERT INTO base_disanfangvedio \n" +
                    "( regionkey, equipment_name, province_id,\n" +
                    " city_id, district_id, venue_id, real_time_video_address, \n" +
                    " device_id, create_time) \n" +
                    " VALUES (?, ?,?, ?, ?, ?, ?, ?, ?)";
            //修改的sql语句
            String sqlEquipmentUpdate = " UPDATE base_equipment SET equipment_code = ?, equipment_name = ?, " +
                    "dept_id = ?, location = ?, brand_id = ?, type_id = ?, state_id = ?, " +
                    "online_date = ?, is_core_area = ?, is_examine = ? WHERE ip_address = ?";
            String sqlBaseDisanFangVedioUpdate = " UPDATE base_disanfangvedio SET regionkey = ?, " +
                    "equipment_name = ?, province_id = ?, city_id = ?, district_id = ?, " +
                    "venue_id = ?, real_time_video_address = ?, create_time = ? " +
                    "WHERE device_id = ?";
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //添加的
            pStatementEquInsert = connection.prepareStatement(sqlEquipmentInsert);
            pStatementDsfInsert = connection.prepareStatement(sqlBaseDisanFangVedioInsert);
            //修改的
            pStatementEquUpdate = connection.prepareStatement(sqlEquipmentUpdate);
            pStatementDsfUpdate = connection.prepareStatement(sqlBaseDisanFangVedioUpdate);
            //查询的
            pStatementEquipmentSelect = connection.prepareStatement(sqlEquipmentSelect);
            //批量插入数据
            for (EquipmentAndDiSanFangVedioEntity equipmentAndDiSanFangVedioEntity : equipmentAndDiSanFangVedioEntityList) {
                //先根据场馆id和点位名称查询信息
                pStatementEquipmentSelect.setObject(1, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getDeptId());
                pStatementEquipmentSelect.setObject(2, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getEquipmentName());
                rs = pStatementEquipmentSelect.executeQuery();
                if (rs.next()) {
                    //找到了结果了，说明已经存在了，不需要插入，需要更新
                    //获取唯一标识
                    String ipAddress = rs.getString("ip_address");
                    //更新
                    //equipment表
                    pStatementEquUpdate.setString(1, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getEquipmentCode());
                    pStatementEquUpdate.setString(2, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getEquipmentName());
                    pStatementEquUpdate.setLong(3, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getDeptId());
                    pStatementEquUpdate.setString(4, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getLocation());
                    pStatementEquUpdate.setString(5, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getBrandId());
                    pStatementEquUpdate.setString(6, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getTypeId());
                    pStatementEquUpdate.setString(7, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getStateId());
                    pStatementEquUpdate.setString(8, simpleDateFormat.format(equipmentAndDiSanFangVedioEntity.getBaseEquipment().getOnlineDate()));
                    pStatementEquUpdate.setInt(9, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getIsCoreArea());
                    pStatementEquUpdate.setString(10, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getIsExamine());
                    pStatementEquUpdate.setString(11, ipAddress);
                    //disanfangvedio表
                    pStatementDsfUpdate.setLong(1, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getRegionkey());
                    pStatementDsfUpdate.setString(2, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getEquipmentName());
                    pStatementDsfUpdate.setString(3, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getProvinceId());
                    pStatementDsfUpdate.setString(4, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getCityId());
                    pStatementDsfUpdate.setString(5, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getDistrictId());
                    pStatementDsfUpdate.setString(6, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getVenueId());
                    pStatementDsfUpdate.setString(7, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getRealTimeVideoAddress());
                    pStatementDsfUpdate.setString(8, simpleDateFormat.format(equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getCreateTime()));
                    pStatementDsfUpdate.setString(9, ipAddress);
                    //批量修改
                    pStatementEquUpdate.addBatch();
                    pStatementDsfUpdate.addBatch();
                } else {
                    //没有找到结果，插入数据
                    //equipment表
                    pStatementEquInsert.setString(1, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getEquipmentCode());
                    pStatementEquInsert.setString(2, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getEquipmentName());
                    pStatementEquInsert.setLong(3, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getDeptId());
                    pStatementEquInsert.setString(4, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getLocation());
                    pStatementEquInsert.setString(5, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getBrandId());
                    pStatementEquInsert.setString(6, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getTypeId());
                    pStatementEquInsert.setString(7, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getIpAddress());
                    pStatementEquInsert.setString(8, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getStateId());
                    pStatementEquInsert.setString(9, simpleDateFormat.format(equipmentAndDiSanFangVedioEntity.getBaseEquipment().getOnlineDate()));
                    pStatementEquInsert.setInt(10, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getIsCoreArea());
                    pStatementEquInsert.setString(11, equipmentAndDiSanFangVedioEntity.getBaseEquipment().getIsExamine());
                    //批量添加
                    pStatementEquInsert.addBatch();
                    //判断视频地址是否为空
                    //  if(Strings.isNotBlank( equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getRealTimeVideoAddress())){
                    //不为空的话，需要给视频表里面维护数据
                    //disanfangvedio表
                    pStatementDsfInsert.setLong(1, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getRegionkey());
                    pStatementDsfInsert.setString(2, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getEquipmentName());
                    pStatementDsfInsert.setString(3, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getProvinceId());
                    pStatementDsfInsert.setString(4, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getCityId());
                    pStatementDsfInsert.setString(5, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getDistrictId());
                    pStatementDsfInsert.setString(6, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getVenueId());
                    pStatementDsfInsert.setString(7, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getRealTimeVideoAddress());
                    pStatementDsfInsert.setString(8, equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getDeviceId());
                    pStatementDsfInsert.setString(9, simpleDateFormat.format(equipmentAndDiSanFangVedioEntity.getBaseDisanfangvedio().getCreateTime()));
                    //批量添加
                    pStatementDsfInsert.addBatch();
                    // }
                }
            }
            //批量添加操作
            int[] pStatementEquCounts = pStatementEquInsert.executeBatch();
            int[] pStatementDsfCounts = pStatementDsfInsert.executeBatch();
            //批量修改操作
            int[] pStatementEquUpdateCounts = pStatementEquUpdate.executeBatch();
            int[] pStatementDsfUpdateCounts = pStatementDsfUpdate.executeBatch();
            System.out.println("批量添加的情况：equipment表：" + pStatementEquCounts.length+"，disanfangvedio表："+pStatementDsfCounts.length);
            System.out.println("批量修改的情况：equipment表：" + pStatementEquUpdateCounts.length+"，disanfangvedio表："+pStatementDsfUpdateCounts.length);
            //批量clear
            pStatementEquInsert.clearBatch();
            pStatementDsfInsert.clearBatch();
            pStatementEquUpdate.clearBatch();
            pStatementDsfUpdate.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResultSet(rs);
            closePreparedStatement(pStatementEquInsert);
            closePreparedStatement(pStatementDsfInsert);
            closePreparedStatement(pStatementEquUpdate);
            closePreparedStatement(pStatementDsfUpdate);
            closePreparedStatement(pStatementEquipmentSelect);
            closeConnection(connection);
        }
    }

注意事项

  1. 类似这种直接操作数据表的，一定要先把表备份一下
  2. 安全起见，excel表格中先只保留一行数据，解析导入没有问题的话，在导入整个表
  3. 一定要-先备份，先备份，先备份！！！
文章标签：
云解析DNS
云数据库 RDS MySQL 版
Java
数据库连接
安全
关键词：
云解析DNS数据
excel文件
云解析DNS文件
excel数据
jdbc excel数据
穆雄雄.
目录
相关文章
风水道人
|
25天前
|
SQL 缓存 easyexcel
面试官问10W 行级别数据的 Excel 导入如何10秒处理
面试官问10W 行级别数据的 Excel 导入如何10秒处理
风水道人
52 0
傻啦嘿哟
|
1月前
|
监控 数据处理 索引
使用Python批量实现文件夹下所有Excel文件的第二张表合并
使用Python和pandas批量合并文件夹中所有Excel文件的第二张表，通过os库遍历文件，pandas的read_excel读取表，concat函数合并数据。主要步骤包括：1) 遍历获取Excel文件，2) 读取第二张表，3) 合并所有表格，最后将结果保存为新的Excel文件。注意文件路径、表格结构一致性及异常处理。可扩展为动态指定合并表、优化性能、日志记录等功能。适合数据处理初学者提升自动化处理技能。
傻啦嘿哟
23 1
大道不孤，众行致远
|
8天前
|
数据库
开发指南009-从list导出excel文件
从数据库返回一般是对象的列表，平台底层提供了从list转为excel文件的方法
大道不孤，众行致远
7 0
江帅帅
|
8天前
|
数据挖掘 索引 Python
Python 读写 Excel 文件
Python 读写 Excel 文件
江帅帅
12 0
雪梅零落
|
14天前
|
存储 JSON JavaScript
「Python系列」Python JSON数据解析
在Python中解析JSON数据通常使用`json`模块。`json`模块提供了将JSON格式的数据转换为Python对象（如列表、字典等）以及将Python对象转换为JSON格式的数据的方法。
雪梅零落
31 0
Mylvzi
|
3月前
|
SQL Java 关系型数据库
MySQL之JDBC（二）
MySQL之JDBC（二）
Mylvzi
34 0
Mylvzi
|
3月前
|
关系型数据库 MySQL Java
MySQL之JDBC（一）
MySQL之JDBC
Mylvzi
33 0
翰文编程
|
6月前
|
Java 关系型数据库 MySQL
JSP居民信息采集系统yeclipse开发mysql数据库bs框架java编程jdbc
JSP 居民信息采集系统是一套完善的web设计系统，对理解JSP java编程开发语言有帮助，系统具有完整的源代码和数据库，开发环境为TOMCAT7.0,Myeclipse8.5开发，数据库为Mysql5.0，使用java语,言开发系统主要采用B/S模式开发。
翰文编程
36 0
yma16
|
2天前
|
SQL 关系型数据库 MySQL
Spring_jdbc数据连接池(mysql实现增、删、改、查)
Spring_jdbc数据连接池(mysql实现增、删、改、查)
yma16
8 0
.29.
|
4月前
|
Java 关系型数据库 MySQL
②⑩② 【读写分离】Sharding - JDBC 实现 MySQL读写分离[SpringBoot框架]
②⑩② 【读写分离】Sharding - JDBC 实现 MySQL读写分离[SpringBoot框架]
.29.
42 0

热门文章

最新文章

  • 1
    SpringBoot整合sharding-jdbc实现分库分表
  • 2
    Ganos H3地理网格能力解析与最佳实践
  • 3
    【Linux系统编程】深入解析Linux中read函数的错误场景
  • 4
    【C/C++ 数据库 sqlite3】SQLite C语言API返回值深入解析
  • 5
    大模型落地实战指南：从选择到训练，深度解析显卡选型、模型训练技、模型选择巧及AI未来展望---打造AI应用新篇章
  • 6
    拓扑排序解析：计算机与数学的交汇点以及C++ 实现
  • 7
    深入解析：HTTP和HTTPS的三次握手与四次挥手
  • 8
    深度解析JVM世界：JVM内存分配
  • 9
    Valgrind兼容性解析：从核心依赖到错误诊断
  • 10
    【CMake 小知识】CMake中的库目标命名和查找策略解析
  • 1
    使用AOP+反射实现Excel数据的读取
    20
  • 2
    使用Python批量合并Excel文件的所有Sheet数据
    33
  • 3
    盘点CSV文件在Excel中打开后乱码问题的两种处理方法
    162
  • 4
    使用openpyxl库从Excel文件中提取指定的数据并生成新的文件
    29
  • 5
    用Python实现Excel中的Vlookup功能
    32
  • 6
    4种方法用Python批量实现多Excel多Sheet合并
    38
  • 7
    利用python将Excel文件拆分为多个CSV
    27
  • 8
    Python中导入Excel数据：全面解析与实践
    42
  • 9
    使用SQL和Python处理Excel文件数据
    54
  • 10
    Python操作Excel常用方法汇总
    34

    • 相关课程

    更多
  • 深入解析Docker容器化技术
  • JDBC数据库开发入门
  • JDBC数据库开发进阶
  • Java面试疑难点解析 - 面试技巧及语言基础
  • Java面试疑难点解析 - Java Web开发
  • Java面试疑难点解析 - 系统架构及项目设计

    • 相关电子书

    更多
  • 神龙云服务器产品及技术深度解析
  • 弹性创造价值：基于ECS的最佳性价比实践解析
  • 又快又稳：阿里云下一代虚拟交换机解析

    • 相关实验场景

    更多
  • Excel文件转存到RDS数据库
  • 通过云拨测对指定服务器进行Ping/DNS监测

    • 推荐镜像

    更多
  • DNS
  • NTP
  • kali-security
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）