jdbc实现批量给多个表中更新数据(解析Excel表数据插入到数据库中)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: jdbc实现批量给多个表中更新数据(解析Excel表数据插入到数据库中)


前言

现在是:2022年5月19日08:01:51

今天遇到了个这样的需求,解析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批量更新的方式,addBatchexecuteBatchclearBatch
  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. 一定要-先备份,先备份,先备份!!!
目录
相关文章
|
7天前
|
Java 数据库
SpringBoot定时将数据库表生成Excel表格
SpringBoot定时将数据库表生成Excel表格
35 0
|
7天前
|
安全 Java 数据库连接
jdbc解析excel文件,批量插入数据至库中
jdbc解析excel文件,批量插入数据至库中
21 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
46 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
49 0
|
7天前
|
Java 关系型数据库 MySQL
JDBC实现往MySQL插入百万级数据
JDBC实现往MySQL插入百万级数据
|
4天前
|
Java 数据库连接 数据库
实时计算 Flink版操作报错合集之flink jdbc写入数据时,长时间没写入后报错,是什么原因导致的
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
27 9
|
4天前
|
JSON Rust 前端开发
【sheetjs】纯前端如何实现Excel导出下载和上传解析?
本文介绍了如何使用`sheetjs`的`xlsx`库在前端实现Excel的导出和上传。项目依赖包括Vite、React、SheetJS和Arco-Design。对于导出,从后端获取JSON数据,通过`json_to_sheet`、`book_new`和`writeFile`函数生成并下载Excel文件。对于上传,使用`read`函数将上传的Excel文件解析为JSON并发送至后端。完整代码示例可在GitHub仓库[fullee/sheetjs-demo](https://github.com/fullee/sheetjs-demo)中查看。
36 10
|
5天前
|
SQL 流计算 API
实时计算 Flink版产品使用合集之ClickHouse-JDBC 写入数据时,发现写入的目标表名称与 PreparedStatement 中 SQL 的表名不一致如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 0
|
7天前
|
前端开发 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
38 0
|
7天前
|
SQL Java 数据库连接
Springboot框架整合Spring JDBC操作数据
JDBC是Java数据库连接API,用于执行SQL并访问多种关系数据库。它包括一系列Java类和接口,用于建立数据库连接、创建数据库操作对象、定义SQL语句、执行操作并处理结果集。直接使用JDBC涉及七个步骤,包括加载驱动、建立连接、创建对象、定义SQL、执行操作、处理结果和关闭资源。Spring Boot的`spring-boot-starter-jdbc`简化了这些步骤,提供了一个在Spring生态中更便捷使用JDBC的封装。集成Spring JDBC需要添加相关依赖,配置数据库连接信息,并通过JdbcTemplate进行数据库操作,如插入、更新、删除和查询。

推荐镜像

更多