Spring Boot从Excel导入PostgreSQL多表及价格关联问题求助

核心问题 #

现有含vehicle、vehicle id、brand等字段的Excel文件，需导入PostgreSQL的三张关联表：

• sub-category（name、category_id）
• service（service_name、sub_category_id）
• service price（service_id、model_id、variant_id）

其中service price需关联已有5万+数据的model表的model_id和variant_id，当前已用POI实现Excel读取与实体类映射，但批量关联插入时效率低下或出错。

分步解决方案 #

1. 预加载车型映射缓存，避免高频DB查询 #

由于model表数据量较大，禁止逐行查询DB获取model_id和variant_id，需一次性加载所有车型的唯一标识与对应ID的映射到内存缓存：

• 唯一标识可采用brand+model_name+variant_name的组合字符串（确保唯一）；
• 用HashMap存储映射关系，键为组合标识，值为包含model_id和variant_id的DTO。

2. 批量处理实体，减少DB交互开销 #

不要逐行插入数据，而是将解析后的实体按批次收集（建议每1000条为一批），达到阈值时执行批量插入：

• 先批量插入sub-category，通过PostgreSQL的RETURNING子句批量获取插入后的id，关联到对应的service实体；
• 再批量插入service，同样获取id关联到service price；
• 最后批量插入service price。

3. 关联service price的车型ID #

解析Excel每行的brand、model、variant字段，拼接成缓存键，直接从内存缓存中获取model_id和variant_id：

• 若缓存中无匹配项，记录异常行号与数据，后续人工处理，避免中断整个导入流程。

4. 数据库层面优化 #

• 为model表的brand、model_name、variant_name字段建立联合唯一索引，加快缓存加载时的查询速度；
• 导入过程关闭自动提交，用事务包裹所有批量操作，确保数据一致性；
• 使用JDBC的batchUpdate方法或PostgreSQL的COPY命令，提升插入效率。

代码优化示例（基于现有POI代码修改） #

预加载车型缓存方法 #

// 导入前执行，加载车型映射缓存
private static Map<String, ModelVariantDTO> loadModelVariantCache(Connection conn) throws SQLException {
    Map<String, ModelVariantDTO> cache = new HashMap<>(50000); // 初始容量适配model表数据量
    String sql = "SELECT brand, model_name, variant_name, model_id, variant_id FROM model";
    try (PreparedStatement stmt = conn.prepareStatement(sql);
         ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            // 用竖线分隔避免字段值包含空格导致冲突
            String key = String.join("|",
                    rs.getString("brand").trim(),
                    rs.getString("model_name").trim(),
                    rs.getString("variant_name").trim());
            ModelVariantDTO dto = new ModelVariantDTO();
            dto.setModelId(rs.getInt("model_id"));
            dto.setVariantId(rs.getInt("variant_id"));
            cache.put(key, dto);
        }
    }
    return cache;
}

批量导入主方法 #

public static void importExcelToDb(InputStream is, Connection conn) {
    Map<String, ModelVariantDTO> modelCache;
    try {
        modelCache = loadModelVariantCache(conn);
    } catch (SQLException e) {
        System.err.println("加载车型缓存失败：" + e.getMessage());
        return;
    }

    // 批量容器，初始容量设为1000减少扩容开销
    List<ServiceSubCategory> subCategoryBatch = new ArrayList<>(1000);
    List<Services> serviceBatch = new ArrayList<>(1000);
    List<ServicePrice> servicePriceBatch = new ArrayList<>(1000);

    try (XSSFWorkbook workbook = new XSSFWorkbook(is)) {
        XSSFSheet sheet = workbook.getSheet("data");
        Iterator<Row> rowIterator = sheet.iterator();
        int rowNum = 0;

        // 开启事务
        conn.setAutoCommit(false);

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            rowNum++;
            // 跳过表头行
            if (rowNum == 1) continue;

            Iterator<Cell> cellIterator = row.iterator();
            int colIdx = 0;

            ServiceSubCategory subCategory = new ServiceSubCategory();
            Services service = new Services();
            ServicePrice servicePrice = new ServicePrice();

            // 解析Excel单元格到实体（根据实际列索引调整）
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (colIdx) {
                    case 0:
                        subCategory.setName(cell.getStringCellValue().trim());
                        break;
                    case 1:
                        subCategory.setCategoryId((int) cell.getNumericCellValue());
                        break;
                    case 2:
                        service.setServiceName(cell.getStringCellValue().trim());
                        break;
                    case 3:
                        servicePrice.setBrand(cell.getStringCellValue().trim());
                        break;
                    case 4:
                        servicePrice.setModelName(cell.getStringCellValue().trim());
                        break;
                    case 5:
                        servicePrice.setVariantName(cell.getStringCellValue().trim());
                        break;
                    case 6:
                        servicePrice.setPrice(cell.getNumericCellValue());
                        break;
                    // 补充其他字段的解析逻辑
                }
                colIdx++;
            }

            // 加入批量容器
            subCategoryBatch.add(subCategory);
            serviceBatch.add(service);

            // 关联车型ID
            String cacheKey = String.join("|",
                    servicePrice.getBrand(),
                    servicePrice.getModelName(),
                    servicePrice.getVariantName());
            ModelVariantDTO dto = modelCache.get(cacheKey);
            if (dto != null) {
                servicePrice.setModelId(dto.getModelId());
                servicePrice.setVariantId(dto.getVariantId());
                servicePriceBatch.add(servicePrice);
            } else {
                System.err.println("未找到车型映射：行号" + rowNum + "，数据：" + cacheKey);
            }

            // 批量插入触发逻辑
            if (subCategoryBatch.size() >= 1000) {
                List<Integer> subCatIds = batchInsertSubCategories(conn, subCategoryBatch);
                // 将插入后的ID关联到service
                for (int i = 0; i < serviceBatch.size(); i++) {
                    serviceBatch.get(i).setSubCategoryId(subCatIds.get(i));
                }
                subCategoryBatch.clear();
            }
            if (serviceBatch.size() >= 1000) {
                List<Integer> serviceIds = batchInsertServices(conn, serviceBatch);
                // 将插入后的ID关联到servicePrice
                for (int i = 0; i < servicePriceBatch.size(); i++) {
                    servicePriceBatch.get(i).setServiceId(serviceIds.get(i));
                }
                serviceBatch.clear();
            }
            if (servicePriceBatch.size() >= 1000) {
                batchInsertServicePrices(conn, servicePriceBatch);
                servicePriceBatch.clear();
            }
        }

        // 处理剩余未批量的数据
        if (!subCategoryBatch.isEmpty()) {
            List<Integer> subCatIds = batchInsertSubCategories(conn, subCategoryBatch);
            for (int i = 0; i < serviceBatch.size(); i++) {
                serviceBatch.get(i).setSubCategoryId(subCatIds.get(i));
            }
        }
        if (!serviceBatch.isEmpty()) {
            List<Integer> serviceIds = batchInsertServices(conn, serviceBatch);
            for (int i = 0; i < servicePriceBatch.size(); i++) {
                servicePriceBatch.get(i).setServiceId(serviceIds.get(i));
            }
        }
        if (!servicePriceBatch.isEmpty()) {
            batchInsertServicePrices(conn, servicePriceBatch);
        }

        // 提交事务
        conn.commit();
        System.out.println("导入完成，共处理" + (rowNum - 1) + "行数据");
    } catch (Exception e) {
        System.err.println("导入失败：" + e.getMessage());
        try {
            conn.rollback(); // 异常回滚
        } catch (SQLException ex) {
            System.err.println("事务回滚失败：" + ex.getMessage());
        }
    } finally {
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("恢复自动提交失败：" + e.getMessage());
        }
    }
}

批量插入工具方法 #

// 批量插入sub-category，返回插入的ID列表
private static List<Integer> batchInsertSubCategories(Connection conn, List<ServiceSubCategory> list) throws SQLException {
    String sql = "INSERT INTO sub_category (name, category_id) VALUES (?, ?) RETURNING id";
    List<Integer> ids = new ArrayList<>(list.size());
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (ServiceSubCategory sc : list) {
            stmt.setString(1, sc.getName());
            stmt.setInt(2, sc.getCategoryId());
            stmt.addBatch();
        }
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                ids.add(rs.getInt(1));
            }
        }
    }
    return ids;
}

// 批量插入service，返回插入的ID列表
private static List<Integer> batchInsertServices(Connection conn, List<Services> list) throws SQLException {
    String sql = "INSERT INTO service (service_name, sub_category_id) VALUES (?, ?) RETURNING id";
    List<Integer> ids = new ArrayList<>(list.size());
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (Services s : list) {
            stmt.setString(1, s.getServiceName());
            stmt.setInt(2, s.getSubCategoryId());
            stmt.addBatch();
        }
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                ids.add(rs.getInt(1));
            }
        }
    }
    return ids;
}

// 批量插入service price
private static void batchInsertServicePrices(Connection conn, List<ServicePrice> list) throws SQLException {
    String sql = "INSERT INTO service_price (service_id, model_id, variant_id, price) VALUES (?, ?, ?, ?)";
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (ServicePrice sp : list) {
            stmt.setInt(1, sp.getServiceId());
            stmt.setInt(2, sp.getModelId());
            stmt.setInt(3, sp.getVariantId());
            stmt.setDouble(4, sp.getPrice());
            stmt.addBatch();
        }
        stmt.executeBatch();
    }
}

// 车型映射DTO
class ModelVariantDTO {
    private int modelId;
    private int variantId;

    public int getModelId() { return modelId; }
    public void setModelId(int modelId) { this.modelId = modelId; }
    public int getVariantId() { return variantId; }
    public void setVariantId(int variantId) { this.variantId = variantId; }
}

关键注意事项 #

• 内存控制：若Excel数据量极大（百万级），需调整批量阈值或分段读取Excel，避免OOM；
• 缓存更新：若model表数据频繁更新，需在导入前重新加载缓存，或使用Redis等分布式缓存实时同步；
• 异常处理：未找到车型映射的数据需单独记录，避免中断导入流程，后续人工补全后再导入；
• 索引维护：确保model表的联合索引存在，否则缓存加载速度会大幅下降。

内容的提问来源于stack exchange，提问作者VsCoderVs