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. 一定要-先备份，先备份，先备份！！！

​