前言
现在是：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文件 - 将每列需要用到的信息，解析之后放在集合中，更新时需要用
- 调用更新的方法进行更新操作
更新操作
这个方法是专门用来更新操作的，实现思路：
- 根据指定的条件查询数据表中该记录是否存在，必须是唯一的，可以多个条件组合
- 如果有记录，则执行更新操作
- 如果没有记录，则执行添加操作
- 均采用
JDBC批量更新的方式，addBatch、executeBatch、clearBatch - 最后关闭流
代码如下：
/** * 更新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); } }
注意事项
- 类似这种直接操作数据表的，一定要先把表备份一下
- 安全起见，
excel表格中先只保留一行数据，解析导入没有问题的话，在导入整个表 - 一定要-先备份，先备份，先备份！！！
