如何通过Spring Boot+Spring Batch将XML多POJO数据写入MySQL多表

算力阿峰

2026-7-22

问题描述

将XML数据转换为POJO类后得到了多个类，但使用Spring Batch时只能读取XML中的一个类数据，尝试访问其他类时会报错。希望将每个POJO类对应的XML数据写入MySQL数据库的不同表中。

原实现代码

BatchConfig.java

@Configuration 
@EnableBatchProcessing
public class BatchConfig {
    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private DataSource dataSource;

    @Bean
    public DataItenProcessor processor(){
        return new DataItenProcessor();
    }

    @Bean
    public StaxEventItemReader<Call_Info> reader(){
        StaxEventItemReader<Call_Info> reader = new StaxEventItemReader<Call_Info>();
        reader.setResource(new ClassPathResource("data.xml"));
        reader.setFragmentRootElementName("data");
        
        Map<String,String> aliasesMap =new HashMap<String,String>();
        aliasesMap.put("data", "com.example.demo.model.Call_Info");
        XStreamMarshaller marshaller = new XStreamMarshaller();
        marshaller.setAliases(aliasesMap);
        
        reader.setUnmarshaller(marshaller);
        return reader;
    }

    @Bean 
    public JdbcBatchItemWriter<Call_Info> writer(){
        JdbcBatchItemWriter<Call_Info> writer = new JdbcBatchItemWriter<Call_Info>(); 
        writer.setDataSource(dataSource); 
        writer.setSql("INSERT INTO TBL_IVR_CALLACTIVITY(Cli,Dnis,Dnis_Type,SessionID,CallStarttime,CallEndtime,Language,Menu_Path,Last_Menu,Announce_Path,Agent_Vdnno,CI_Reserve1,CI_Reserve2,CI_Reserve3,CI_Reserve4,CI_Reserve5,CI_Reserve6,CI_Reserve7,CI_Reserve8,CI_Reserve12) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 

        writer.setItemPreparedStatementSetter(new DataPreparedStatementSetter()); 
        return writer;
    }

    @Bean
    public Step step1(){
        return stepBuilderFactory.get("step1").<Call_Info,Call_Info>chunk(100).reader(reader()).processor(processor()).writer(writer()).build();
    }

    @Bean
    public Job exportPerosnJob(){
        return jobBuilderFactory.get("importData").incrementer(new RunIdIncrementer()).flow(step1()).end().build();
    }
}

DataPreparedStatementSetter.java

public class DataPreparedStatementSetter implements ItemPreparedStatementSetter<Call_Info> {
    @Override
    public void setValues(Call_Info data, PreparedStatement ps) throws SQLException {
        ps.setString(1, data.getCli());
        ps.setString(2, data.getDnis());
        ps.setString(3, data.getDnis_Type());
        ps.setString(4, data.getSessionID());
        ps.setString(6, data.getCallStarttime());
        ps.setString(7, data.getCallEndtime());
        ps.setString(8, data.getLanguage());
        ps.setString(9, data.getMenu_Path());
        ps.setString(10, data.getAnnounce_Path());
        ps.setString(11, data.getAgent_Vdnno());
        ps.setString(12, data.getCI_Reserve1());
        ps.setString(13, data.getCI_Reserve2());
        ps.setString(14, data.getCI_Reserve3());
        ps.setString(15, data.getCI_Reserve4());
        ps.setString(16, data.getCI_Reserve5());
        ps.setString(17, data.getCI_Reserve6());
        ps.setString(18, data.getCI_Reserve8());
        ps.setString(19, data.getCI_Reserve12());
    }
}

DataItenProcessor.java

public class DataItenProcessor implements ItemProcessor<Call_Info, Call_Info>{
    @Override
    public Call_Info process(Call_Info data) throws Exception {
        return data;
    }
}

目前只能访问模型类中的Call_Info类，请问如何访问其他类并将其数据写入数据库的不同表中？

修改后的代码尝试

BatchConfig.java（修改部分）

@Bean
public JdbcBatchItemWriter<IvrData> writer(){
    JdbcBatchItemWriter<IvrData> writer = new JdbcBatchItemWriter<IvrData>();
    writer.setDataSource(dataSource);
    writer.setSql("INSERT INTO TBL_IVR_CALLACTIVITY(Cli,Dnis,Dnis_Type,SessionID,CallStarttime,CallEndtime,Language,Menu_Path,Last_Menu,Announce_Path,Agent_Vdnno,CI_Reserve1,CI_Reserve2,CI_Reserve3,CI_Reserve4,CI_Reserve5,CI_Reserve6,CI_Reserve7,CI_Reserve8,CI_Reserve12) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    writer.setSql("INSERT INTO TBL_IVR_ANNOUNCE_DETAILS(menuId,menuStartTime,menuEndTime,  menuOption,menuOptionDesc,menuReserve1)VALUES(?,?,?,?,?,?)");
    writer.setSql("INSERT INTO TBL_IVR_HOSTDETAILS(announceId, announceStartTime, announceEndTime)VALUES(?,?,?)");
    writer.setSql("INSERT INTO TBL_IVR_MENUDETAILS(hostType,hostMethod,hostStartTime, hostEndTime,hostInparams,hostOutparams)VALUES(?,?,?,?,?,?,?)");
    writer.setItemPreparedStatementSetter(new DataPreparedStatementSetter());
    return writer;
}

DataPreparedStatementSetter.java（修改后）

public abstract  class DataPreparedStatementSetter implements ItemPreparedStatementSetter<IvrData> {

    public void setValues(IvrData.CallInfo data, PreparedStatement ps) throws SQLException {
        ps.setString(1,data.getAgentVdnno());
        ps.setString(2, data.getAnnouncePath());
        ps.setString(3,data.getCallEndTime());
        ps.setString(4,data.getCallStartTime());
        ps.setString(5,data.getCiReserve1());
        ps.setString(6,data.getCiReserve2());
        ps.setString(7,data.getCiReserve3());
        ps.setString(8,data.getCiReserve4());
        ps.setString(9,data.getCiReserve5());
        ps.setString(10,data.getCiReserve6());
        ps.setString(11,data.getCiReserve7());
        ps.setString(12,data.getCiReserve8());
        ps.setString(13,data.getCiReserve12());
    }

    public void setValues(IvrData.Announce data, PreparedStatement ps)throws SQLException{
        ps.setString(1,data.getAnnounceEndTime());
        ps.setString(1,data.getAnnounceId());
        ps.setString(1,data.getAnnounceStartTime());
    }

    public void setValues(IvrData.Host data,PreparedStatement ps)throws SQLException{
        ps.setString(1,data.getHostEndTime());
        ps.setString(1,data.getHostInparams());
        ps.setString(1,data.getHostMethod());
        ps.setString(1,data.getHostOutparams());
        ps.setString(1,data.getHostType());
        ps.setString(1,data.getHostStartTime());
    }

    public void setValues(IvrData.Menu data, PreparedStatement ps) throws SQLException{
        ps.setString(1,data.getMenuEndTime());
        ps.setString(1,data.getMenuId());
        ps.setString(1,data.getMenuOptionDesc());
        ps.setString(1,data.getMenuReserve1());
        ps.setString(1,data.getMenuStartTime());
        ps.setString(1,data.getMenuOption());
        ps.setString(1,data.getMenuEndTime());
    }
}

遇到的错误（翻译后）

org.springframework.beans.factory.BeanCreationException: 创建名为 'writer' 的 bean 时出错: 初始化 bean 失败；嵌套异常是 org.springframework.beans.ConversionNotSupportedException: 无法将类型为 'com.example.demo.model.IvrData' 的值转换为所需类型 'com.example.demo.model.Call_Info'；嵌套异常是 java.lang.IllegalStateException: 无法将 com.example.demo.model.IvrData 转换为 com.example.demo.model.Call_Info
同时存在SQL语法错误：每个JdbcBatchItemWriter只能设置一条SQL语句，多次调用setSql()会覆盖之前的配置，导致SQL不完整；另外DataPreparedStatementSetter中的方法签名不符合ItemPreparedStatementSetter的接口要求，接口只定义了setValues(T item, PreparedStatement ps)方法，重载的多个setValues方法不会被Spring Batch调用。

解决方案

1. 修正XML读取逻辑，支持多类型POJO

如果XML包含多种类型的根节点，配置StaxEventItemReader支持多类型映射：

@Bean
public StaxEventItemReader<Object> reader(){
    StaxEventItemReader<Object> reader = new StaxEventItemReader<>();
    reader.setResource(new ClassPathResource("data.xml"));
    
    // 配置所有需要解析的根元素和对应的POJO类
    Map<String, Class<?>> aliasesMap = new HashMap<>();
    aliasesMap.put("callInfo", IvrData.CallInfo.class);
    aliasesMap.put("announce", IvrData.Announce.class);
    aliasesMap.put("host", IvrData.Host.class);
    aliasesMap.put("menu", IvrData.Menu.class);
    
    XStreamMarshaller marshaller = new XStreamMarshaller();
    marshaller.setAliases(aliasesMap);
    // 内部类需添加到支持类列表
    marshaller.setSupportedClasses(IvrData.CallInfo.class, IvrData.Announce.class, IvrData.Host.class, IvrData.Menu.class);
    
    reader.setUnmarshaller(marshaller);
    return reader;
}

2. 使用`ClassifierCompositeItemWriter`实现多类型分表写入

Spring Batch的ClassifierCompositeItemWriter可根据Item类型路由到对应Writer：

@Bean
public ClassifierCompositeItemWriter<Object> compositeWriter() {
    ClassifierCompositeItemWriter<Object> compositeWriter = new ClassifierCompositeItemWriter<>();
    compositeWriter.setClassifier(new Classifier<Object, ItemWriter<? super Object>>() {
        @Override
        public ItemWriter<? super Object> classify(Object item) {
            if (item instanceof IvrData.CallInfo) {
                return callInfoWriter();
            } else if (item instanceof IvrData.Announce) {
                return announceWriter();
            } else if (item instanceof IvrData.Host) {
                return hostWriter();
            } else if (item instanceof IvrData.Menu) {
                return menuWriter();
            }
            throw new IllegalArgumentException("未知的Item类型: " + item.getClass());
        }
    });
    return compositeWriter;
}

// 每个表对应独立的JdbcBatchItemWriter
@Bean
public JdbcBatchItemWriter<IvrData.CallInfo> callInfoWriter() {
    JdbcBatchItemWriter<IvrData.CallInfo> writer = new JdbcBatchItemWriter<>();
    writer.setDataSource(dataSource);
    writer.setSql("INSERT INTO TBL_IVR_CALLACTIVITY(Cli,Dnis,Dnis_Type,SessionID,CallStarttime,CallEndtime,Language,Menu_Path,Last_Menu,Announce_Path,Agent_Vdnno,CI_Reserve1,CI_Reserve2,CI_Reserve3,CI_Reserve4,CI_Reserve5,CI_Reserve6,CI_Reserve7,CI_Reserve8,CI_Reserve12) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    writer.setItemPreparedStatementSetter(new CallInfoPreparedStatementSetter());
    return writer;
}

@Bean
public JdbcBatchItemWriter<IvrData.Announce> announceWriter() {
    JdbcBatchItemWriter<IvrData.Announce> writer = new JdbcBatchItemWriter<>();
    writer.setDataSource(dataSource);
    writer.setSql("INSERT INTO TBL_IVR_ANNOUNCE_DETAILS(menuId,menuStartTime,menuEndTime,menuOption,menuOptionDesc,menuReserve1) VALUES(?,?,?,?,?,?)");
    writer.setItemPreparedStatementSetter(new AnnouncePreparedStatementSetter());
    return writer;
}

// 同理实现hostWriter和menuWriter

3. 实现对应类型的PreparedStatementSetter

每个类型单独实现接口，避免重载混淆：

public class CallInfoPreparedStatementSetter implements ItemPreparedStatementSetter<IvrData.CallInfo> {
    @Override
    public void setValues(IvrData.CallInfo data, PreparedStatement ps) throws SQLException {
        ps.setString(1, data.getCli());
        ps.setString(2, data.getDnis());
        // 补充所有字段的设置，注意索引顺序
        ps.setString(6, data.getCallStarttime());
        ps.setString(7, data.getCallEndtime());
        // ...其他字段
    }
}

public class AnnouncePreparedStatementSetter implements ItemPreparedStatementSetter<IvrData.Announce> {
    @Override
    public void setValues(IvrData.Announce data, PreparedStatement ps) throws SQLException {
        ps.setString(1, data.getMenuId());
        ps.setString(2, data.getMenuStartTime());
        ps.setString(3, data.getMenuEndTime());
        ps.setString(4, data.getMenuOption());
        ps.setString(5, data.getMenuOptionDesc());
        ps.setString(6, data.getMenuReserve1());
    }
}

4. 修正Step配置

替换Writer为复合Writer，处理器改为支持Object类型：

@Bean
public ItemProcessor<Object, Object> processor(){
    return item -> item; // 无需处理直接返回，如需处理可根据类型判断
}

@Bean
public Step step1(){
    return stepBuilderFactory.get("step1")
            .<Object, Object>chunk(100)
            .reader(reader())
            .processor(processor())
            .writer(compositeWriter())
            .build();
}

关键注意点

每个JdbcBatchItemWriter只能对应一条SQL，多次调用setSql()会覆盖配置，必须为每个表创建独立Writer。
ItemPreparedStatementSetter必须实现接口定义的setValues(T item, PreparedStatement ps)方法，重载方法不会被调用，需为每个类型单独实现。
XML读取时，确保XStream能正确识别根节点与POJO的映射，内部类需添加到supportedClasses。

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

如何通过Spring Boot+Spring Batch将XML多POJO数据写入MySQL多表

原实现代码 #

BatchConfig.java #

DataPreparedStatementSetter.java #

DataItenProcessor.java #

修改后的代码尝试 #

BatchConfig.java（修改部分） #

DataPreparedStatementSetter.java（修改后） #

遇到的错误（翻译后） #

1. 修正XML读取逻辑，支持多类型POJO #

2. 使用ClassifierCompositeItemWriter实现多类型分表写入 #

3. 实现对应类型的PreparedStatementSetter #

4. 修正Step配置 #

关键注意点 #