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

 muxiongxiong   2022-05-19 08:41   82 人阅读  0 条评论

大家好,我是雄雄,欢迎关注微信公众号:雄雄的小课堂

前言

现在是: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. 一定要-先备份,先备份,先备份!!!
本文地址:http://www.xn--ypzn79ca.com/?id=1391
版权声明:本文为原创文章,版权归 muxiongxiong 所有,欢迎分享本文,转载请保留出处!

 发表评论


表情

还没有留言,还不快点抢沙发?