Spring Batch新手求助：无DTO实现动态Excel批量导入SQL数据库

Absolutely! Spring Batch is flexible enough to handle dynamic Excel file structures without requiring you to create a dedicated DTO for every file. Here's how you can approach this:

1. Build a Custom Dynamic Excel ItemReader #

Spring Batch's out-of-the-box Excel readers (like PoiItemReader) are tied to fixed structures and DTOs. Instead, create a custom ItemReader using Apache POI to read Excel files dynamically:

• First, read the header row to capture column names (these become your map keys).
• For each subsequent row, map cell values to a Map<String, Object> where keys are header names and values are the corresponding cell data.

Here's a simplified code snippet to illustrate:

public class DynamicExcelItemReader implements ItemReader<Map<String, Object>> {
    private final String filePath;
    private Sheet sheet;
    private int currentRow = 1; // Skip header row initially
    private List<String> headers;

    public DynamicExcelItemReader(String filePath) {
        this.filePath = filePath;
        initializeSheet();
    }

    private void initializeSheet() {
        // Use Apache POI to open the Excel file and get the first sheet
        try (Workbook workbook = WorkbookFactory.create(new File(filePath))) {
            sheet = workbook.getSheetAt(0);
            // Read header row
            Row headerRow = sheet.getRow(0);
            headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue().trim());
            }
        } catch (IOException e) {
            throw new RuntimeException("Failed to initialize Excel reader", e);
        }
    }

    @Override
    public Map<String, Object> read() throws Exception {
        if (currentRow >= sheet.getPhysicalNumberOfRows()) {
            return null; // End of file
        }

        Row row = sheet.getRow(currentRow);
        Map<String, Object> rowData = new HashMap<>();

        for (int i = 0; i < headers.size(); i++) {
            Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            Object value;
            // Handle different cell types (numbers, dates, strings, etc.)
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        value = cell.getDateCellValue();
                    } else {
                        value = cell.getNumericCellValue();
                    }
                    break;
                case STRING:
                    value = cell.getStringCellValue().trim();
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                default:
                    value = null;
            }
            rowData.put(headers.get(i), value);
        }

        currentRow++;
        return rowData;
    }
}

2. Process and Write Dynamic Data #

ItemProcessor (Optional but Useful) #

Add an ItemProcessor<Map<String, Object>, Map<String, Object>> to handle validation, data conversion, or column mapping (e.g., if Excel headers don't match database column names):

public class DynamicExcelProcessor implements ItemProcessor<Map<String, Object>, Map<String, Object>> {
    private final Map<String, String> columnMapping; // Excel header -> DB column

    public DynamicExcelProcessor(Map<String, String> columnMapping) {
        this.columnMapping = columnMapping;
    }

    @Override
    public Map<String, Object> process(Map<String, Object> item) throws Exception {
        Map<String, Object> processedItem = new HashMap<>();
        for (Map.Entry<String, Object> entry : item.entrySet()) {
            String dbColumn = columnMapping.getOrDefault(entry.getKey(), entry.getKey());
            // Add validation/conversion logic here (e.g., parse strings to dates)
            processedItem.put(dbColumn, entry.getValue());
        }
        return processedItem;
    }
}

Dynamic ItemWriter #

Use NamedParameterJdbcTemplate to write map data to the database without hardcoding SQL. Generate the INSERT statement dynamically based on the map keys:

public class DynamicJdbcItemWriter implements ItemWriter<Map<String, Object>> {
    private final JdbcTemplate jdbcTemplate;
    private final String tableName;
    private String insertSql;

    public DynamicJdbcItemWriter(JdbcTemplate jdbcTemplate, String tableName) {
        this.jdbcTemplate = jdbcTemplate;
        this.tableName = tableName;
    }

    @Override
    public void write(List<? extends Map<String, Object>> items) throws Exception {
        if (items.isEmpty()) {
            return;
        }
        // Generate INSERT SQL once using the first item's keys
        if (insertSql == null) {
            Map<String, Object> firstItem = items.get(0);
            String columns = String.join(", ", firstItem.keySet());
            String placeholders = firstItem.keySet().stream()
                    .map(key -> ":" + key)
                    .collect(Collectors.joining(", "));
            insertSql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, placeholders);
        }

        // Batch write using NamedParameterJdbcTemplate
        NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        namedJdbcTemplate.batchUpdate(insertSql, items.toArray(new Map[0]));
    }
}

3. Configure the Batch Job #

Wire everything together in your job configuration, using job parameters to pass dynamic values like file path, target table, and column mappings:

@Configuration
public class DynamicExcelImportJobConfig {

    @Bean
    public Job dynamicExcelImportJob(JobRepository jobRepository, Step importStep) {
        return new JobBuilder("dynamicExcelImportJob", jobRepository)
                .start(importStep)
                .build();
    }

    @Bean
    public Step importStep(JobRepository jobRepository, PlatformTransactionManager transactionManager,
                           DynamicExcelItemReader reader, DynamicExcelProcessor processor, DynamicJdbcItemWriter writer) {
        return new StepBuilder("importStep", jobRepository)
                .<Map<String, Object>, Map<String, Object>>chunk(100, transactionManager)
                .reader(reader)
                .processor(processor)
                .writer(writer)
                .build();
    }

    // Define reader, processor, writer with job parameters
    @Bean
    @StepScope
    public DynamicExcelItemReader dynamicExcelItemReader(@Value("#{jobParameters['filePath']}") String filePath) {
        return new DynamicExcelItemReader(filePath);
    }

    @Bean
    @StepScope
    public DynamicExcelProcessor dynamicExcelProcessor(@Value("#{jobParameters['columnMapping']}") String columnMappingJson) {
        // Parse JSON string to Map (use Jackson for this)
        ObjectMapper objectMapper = new ObjectMapper();
        Map<String, String> columnMapping = objectMapper.readValue(columnMappingJson, new TypeReference<>() {});
        return new DynamicExcelProcessor(columnMapping);
    }

    @Bean
    @StepScope
    public DynamicJdbcItemWriter dynamicJdbcItemWriter(JdbcTemplate jdbcTemplate, @Value("#{jobParameters['tableName']}") String tableName) {
        return new DynamicJdbcItemWriter(jdbcTemplate, tableName);
    }
}

Key Considerations #

• Cell Type Handling: Ensure you handle all Excel cell types (numeric, date, boolean, etc.) correctly to avoid data loss.
• Validation: Add checks for missing required columns, invalid data formats, or mismatched data types in your processor.
• Error Handling: Use Spring Batch's skip/retry mechanisms to handle corrupted rows or database errors gracefully.
• Performance: For large files, use Apache POI's SXSSFWorkbook to stream data instead of loading the entire workbook into memory.

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