Excel文件旧数据导入数据库进行覆盖更新及新增

原数据表有id自增主键
①处理Excel表中数据，将其处理成数据库表中字段结构相同的数据
②代码段中的model为对比字段，查看原库是否存在，如果存在则更新，如果不存在则新增
③运行过程中，有日志展示报错，是否有空值等情况发生。

点击查看代码

**package org.example;**

**import org.apache.logging.log4j.LogManager;**
**import org.apache.logging.log4j.Logger;**
**import org.apache.poi.ss.usermodel.Cell;**
**import org.apache.poi.ss.usermodel.Row;**
**import org.apache.poi.ss.usermodel.Sheet;**
**import org.apache.poi.ss.usermodel.Workbook;**
**import org.apache.poi.xssf.usermodel.XSSFWorkbook;**

**import java.io.File;**
**import java.io.FileInputStream;**
**import java.math.BigDecimal;**
**import java.sql.*;**

**public class ExcelToDatabaseUpdater {**
**    private static final Logger logger = LogManager.getLogger(ExcelToDatabaseUpdater.class);**

**    public static void main(String[] args) {**
**        String excelFilePath = "文件路径"; **
**        String tableName = "user_device_price";**

**        // 1. 验证文件**
**        File file = new File(excelFilePath);**
**        logger.info("=== 启动程序 ===");**
**        logger.info("文件检测 - 存在: {}, 可读: {}", file.exists(), file.canRead());**

**        try (Connection conn = DriverManager.getConnection("jdbc:此处填写数据库url", "数据库账号", "数据库密码")) {**
**            logger.info("数据库连接成功");**

**            // 2. 读取Excel**
**            try (FileInputStream fis = new FileInputStream(file);**
**                 Workbook workbook = new XSSFWorkbook(fis)) {**

**                Sheet sheet = workbook.getSheetAt(0);**
**                logger.info("工作表[{}]总行数: {}", sheet.getSheetName(), sheet.getLastRowNum());**

**                // 3. 执行导入**
**                String sql = "INSERT INTO " + tableName + " (model, brand, model_name, device_type, price) VALUES (?,?,?,?,?) " +**
**                        "ON DUPLICATE KEY UPDATE brand=VALUES(brand), price=VALUES(price)";**

**                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {**
**                    conn.setAutoCommit(false);**

**                    for (int i = 1; i <= sheet.getLastRowNum(); i++) {**
**                        Row row = sheet.getRow(i);**
**                        if (row == null) continue;**

**                        try {**
**                            pstmt.setString(1, getCellValue(row.getCell(0))); // model**
**                            pstmt.setString(2, getCellValue(row.getCell(1))); // brand**
**                            pstmt.setString(3, getCellValue(row.getCell(2))); // model_name**
**                            pstmt.setString(4, getCellValue(row.getCell(3))); // device_type**
**                            pstmt.setBigDecimal(5, new BigDecimal(getCellValue(row.getCell(4)))); // price**

**                            pstmt.addBatch();**
**                            if (i % 10 == 0) {**
**                                pstmt.executeBatch();**
**                                logger.info("已提交 {} 行", i);**
**                            }**
**                        } catch (Exception e) {**
**                            logger.error("第{}行处理失败: {}", i, e.getMessage());**
**                        }**
**                    }**
**                    pstmt.executeBatch();**
**                    conn.commit();**
**                    logger.info("导入完成，共处理 {} 行", sheet.getLastRowNum());**
**                }**
**            }**
**        } catch (Exception e) {**
**            logger.error("程序异常终止", e);**
**            e.printStackTrace();**
**        }**
**    }**

**    private static String getCellValue(Cell cell) {**
**        if (cell == null) return "";**
**        switch (cell.getCellType()) {**
**            case STRING: return cell.getStringCellValue().trim();**
**            case NUMERIC: return String.valueOf(cell.getNumericCellValue());**
**            default: return "";**
**        }**
**    }**
**}**