使用Spring Batch读取不同结构Excel文件至对应数据库表

核心思路 #

针对异构Excel文件，核心是为每种结构单独定义读取-处理-写入的独立作业流程，利用Spring Batch的作业调度机制实现多作业的隔离执行或串联执行。

具体实现步骤 #

1. 依赖准备 #

确保项目引入Spring Batch核心依赖与Excel解析依赖（以Apache POI为例），Maven pom.xml配置如下：

<!-- Spring Batch核心 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<!-- Apache POI解析Excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

2. 定义实体类 #

分别对应两张数据库表的结构：

// 对应t1表的实体
public class T1Entity {
    private String segment;
    private String country;
    private String product;
    // 生成getter、setter与构造方法
}

// 对应t2表的实体
public class T2Entity {
    private String application;
    private String environment;
    private String database;
    // 生成getter、setter与构造方法
}

3. 自定义异构Excel读取器 #

为每种Excel结构编写专用ItemReader，通过Apache POI解析不同列：

读取t1对应Excel的Reader

@Component
public class T1ExcelReader implements ItemReader<T1Entity> {
    private final String filePath;
    private int currentRow = 1; // 跳过表头行
    private XSSFSheet sheet;

    public T1ExcelReader(@Value("${excel.t1.path}") String filePath) throws IOException {
        this.filePath = filePath;
        FileInputStream fis = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        this.sheet = workbook.getSheetAt(0);
    }

    @Override
    public T1Entity read() throws Exception {
        if (currentRow <= sheet.getLastRowNum()) {
            XSSFRow row = sheet.getRow(currentRow++);
            T1Entity entity = new T1Entity();
            entity.setSegment(row.getCell(0).getStringCellValue());
            entity.setCountry(row.getCell(1).getStringCellValue());
            entity.setProduct(row.getCell(2).getStringCellValue());
            return entity;
        }
        return null; // 返回null表示读取结束
    }
}

读取t2对应Excel的Reader

@Component
public class T2ExcelReader implements ItemReader<T2Entity> {
    private final String filePath;
    private int currentRow = 1;
    private XSSFSheet sheet;

    public T2ExcelReader(@Value("${excel.t2.path}") String filePath) throws IOException {
        this.filePath = filePath;
        FileInputStream fis = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        this.sheet = workbook.getSheetAt(0);
    }

    @Override
    public T2Entity read() throws Exception {
        if (currentRow <= sheet.getLastRowNum()) {
            XSSFRow row = sheet.getRow(currentRow++);
            T2Entity entity = new T2Entity();
            entity.setApplication(row.getCell(0).getStringCellValue());
            entity.setEnvironment(row.getCell(1).getStringCellValue());
            entity.setDatabase(row.getCell(2).getStringCellValue());
            return entity;
        }
        return null;
    }
}

4. 编写数据库写入器 #

使用Spring Batch的JdbcBatchItemWriter分别实现对应表的批量写入：

t1表的Writer配置

@Configuration
public class T1WriterConfig {
    @Bean
    public JdbcBatchItemWriter<T1Entity> t1ItemWriter(DataSource dataSource) {
        return new JdbcBatchItemWriterBuilder<T1Entity>()
                .dataSource(dataSource)
                .sql("INSERT INTO t1 (segment, country, product) VALUES (:segment, :country, :product)")
                .beanMapped()
                .build();
    }
}

t2表的Writer配置

@Configuration
public class T2WriterConfig {
    @Bean
    public JdbcBatchItemWriter<T2Entity> t2ItemWriter(DataSource dataSource) {
        return new JdbcBatchItemWriterBuilder<T2Entity>()
                .dataSource(dataSource)
                .sql("INSERT INTO t2 (application, environment, database) VALUES (:application, :environment, :database)")
                .beanMapped()
                .build();
    }
}

5. 配置独立作业流程 #

为每种Excel结构配置单独的Job与Step，确保流程完全隔离：

t1对应的作业配置

@Configuration
public class T1BatchConfig {
    @Bean
    public Step t1Step(T1ExcelReader t1Reader, JdbcBatchItemWriter<T1Entity> t1Writer) {
        return new StepBuilder("t1Step")
                .<T1Entity, T1Entity>chunk(100) // 每100条数据提交一次事务
                .reader(t1Reader)
                .writer(t1Writer)
                .build();
    }

    @Bean
    public Job t1Job(JobBuilderFactory jobBuilderFactory, Step t1Step) {
        return jobBuilderFactory.get("t1Job")
                .start(t1Step)
                .build();
    }
}

t2对应的作业配置

@Configuration
public class T2BatchConfig {
    @Bean
    public Step t2Step(T2ExcelReader t2Reader, JdbcBatchItemWriter<T2Entity> t2Writer) {
        return new StepBuilder("t2Step")
                .<T2Entity, T2Entity>chunk(100)
                .reader(t2Reader)
                .writer(t2Writer)
                .build();
    }

    @Bean
    public Job t2Job(JobBuilderFactory jobBuilderFactory, Step t2Step) {
        return jobBuilderFactory.get("t2Job")
                .start(t2Step)
                .build();
    }
}

6. 触发作业执行 #

可以通过接口调用或启动时自动执行，示例控制器如下：

@RestController
@RequestMapping("/batch")
public class BatchController {
    private final JobLauncher jobLauncher;
    private final Job t1Job;
    private final Job t2Job;

    public BatchController(JobLauncher jobLauncher, Job t1Job, Job t2Job) {
        this.jobLauncher = jobLauncher;
        this.t1Job = t1Job;
        this.t2Job = t2Job;
    }

    @GetMapping("/run/t1")
    public String runT1Job() throws Exception {
        JobParameters params = new JobParametersBuilder()
                .addString("JobID", String.valueOf(System.currentTimeMillis()))
                .toJobParameters();
        jobLauncher.run(t1Job, params);
        return "T1 Job executed successfully";
    }

    @GetMapping("/run/t2")
    public String runT2Job() throws Exception {
        JobParameters params = new JobParametersBuilder()
                .addString("JobID", String.valueOf(System.currentTimeMillis()))
                .toJobParameters();
        jobLauncher.run(t2Job, params);
        return "T2 Job executed successfully";
    }
}

扩展优化点 #

• 动态字段映射：若Excel列名不固定，可读取表头动态匹配实体字段，避免硬编码列索引
• 异常处理：在Reader中处理空行、单元格类型转换异常，为Step配置skipPolicy跳过错误数据
• 定时调度：结合Spring Task实现作业定时自动执行
• 资源管理：在Reader中添加流关闭逻辑，避免文件句柄泄漏

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