Spring Batch 5.0.1双数据源配置异常：Reader误用H2数据源报错

问题背景 #

基于Spring Boot搭建Spring Batch 5.0.1项目，需配置两个数据源：

• MySQL：供Spring Batch Job Reader的JPA仓库使用
• H2：存储Spring Batch元数据（5.x版本无法禁用元数据）

运行应用时Reader报错，提示H2数据库中找不到目标表，确认Reader错误使用了主数据源（H2）而非配置的MySQL数据源。

配置文件（application.properties） #

spring.reader.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.reader.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.reader.datasource.username=root
spring.reader.datasource.password=

spring.datasource.url=jdbc:h2:mem:test;SCHEMA=BATCH
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

数据源配置类 #

ReaderDataSourceConfiguration（MySQL数据源） #

@Configuration
@Component
@EnableJpaRepositories(
        entityManagerFactoryRef = "readerEntityManagerFactory",
        transactionManagerRef = "readerTransactionManager")
public class ReaderDataSourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.reader.datasource")
    public DataSourceProperties readerDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.reader.datasource.configuration")
    public DataSource readerDataSource() {
        return readerDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean(name = "readerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean readerEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(readerDataSource())
                .packages("com.batch.job.reader") // 仅指定Reader实体类包
                .build();
    }

    @Bean(name = "readerTransactionManager")
    public PlatformTransactionManager readerTransactionManager(
            final @Qualifier("readerEntityManagerFactory") LocalContainerEntityManagerFactoryBean readerEntityManagerFactory) {
        return new JpaTransactionManager(readerEntityManagerFactory.getObject());
    }

}

BatchMetaDataSourceConfiguration（H2元数据数据源） #

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(//basePackages = "com.javatodev.api.repository.user", 不确定此处配置故注释
        entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef= "transactionManager")
public class BatchMetaDataSourceConfiguration {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties batchDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.configuration")
    public DataSource dataSource() {
        return batchDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    public DataSourceInitializer h2DatabasePopulator() {
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript(
                new ClassPathResource("org/springframework/batch/core/schema-h2.sql"));
        populator.setContinueOnError(true);
        populator.setIgnoreFailedDrops(true);

        DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDatabasePopulator(populator);
        initializer.setDataSource(dataSource());

        return initializer;
    }

    @Bean(name = "entityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(dataSource())
                .packages("com.rbc.services.accountmapper.loader")
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager transactionManager(
            final @Qualifier("entityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }
}

Spring Batch配置类（SpringBatchConfig） #

@Configuration
@EnableBatchProcessing
public class SpringBatchConfig extends DefaultBatchConfiguration {

    @Autowired
    private AProcessor processor;
    @Autowired
    private AWriter writer;
    @Autowired
    private JpaPagingItemReader<OriginEntity> reader;
    
    @Autowired
    private LoaderJobExecutionListener jobExecutionListener;

    @Autowired
    private ItemProcessorExecutionListener itemProcessorExecutionListener;
    @Autowired
    private ItemReaderExecutionListener itemReaderExecutionListener;

    @Value("${chunkSize}")
    private Integer chunkSize;

    @Bean
    public Step step1(JobRepository jobRepository,
                      PlatformTransactionManager readerTransactionManager) {
        return new StepBuilder("accountMapperDataLoaderStep", jobRepository)
                .allowStartIfComplete(true)
                .<OriginEntity, DestinationEntity>chunk(chunkSize, readerTransactionManager)
                .reader(reader)
                .processor(processor)
                .writer(writer)
                .faultTolerant()
                .retryLimit(1)
                .retry(ArithmeticException.class)
                .listener(itemReaderExecutionListener)
                .listener(itemProcessorExecutionListener)
                .build();
    }

    @Bean
    public Job runJob(JobRepository jobRepository,
                      PlatformTransactionManager readerTransactionManager) {
        return new JobBuilder("accountMapperDataLoaderJob", jobRepository)
                .incrementer(new RunIdIncrementer())
                .start(step1(jobRepository, readerTransactionManager))
                .listener(jobExecutionListener)
                .build();
    }
}

Reader配置类（AReader） #

@Configuration
public class AReader {

    @Value("${chunkSize}")
    private Integer chunkSize;

    @Autowired
    LocalContainerEntityManagerFactoryBean readerEntityManagerFactory;

    @Bean(destroyMethod="")
    public JpaPagingItemReader<OriginAccount> reader() {
        return new JpaPagingItemReaderBuilder<OriginAccount>()
                .name("Account")
                .entityManagerFactory(readerEntityManagerFactory.getObject())
                .queryString("query")
                .pageSize(chunkSize)
                .build();
    }

}

报错信息 #

batch.ItemReaderExecutionListener : Exception occurred while reading.
jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.SQLGrammarException` to JPA `PersistenceException` : could not prepare statement
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "READER_TABLE" not found; SQL statement:

问题排查与解决方案 #

1. 修复Reader中EntityManagerFactory的注入问题 #

当前AReader直接注入LocalContainerEntityManagerFactoryBean并调用getObject()存在初始化风险，且未指定@Qualifier导致注入了Primary的H2数据源对应的EntityManagerFactory。修改如下：

@Configuration
public class AReader {

    @Value("${chunkSize}")
    private Integer chunkSize;

    @Autowired
    @Qualifier("readerEntityManagerFactory")
    private EntityManagerFactory readerEntityManagerFactory; // 改为EntityManagerFactory类型

    @Bean(destroyMethod="")
    public JpaPagingItemReader<OriginAccount> reader() {
        return new JpaPagingItemReaderBuilder<OriginAccount>()
                .name("Account")
                .entityManagerFactory(readerEntityManagerFactory)
                .queryString("SELECT o FROM OriginAccount o") // 确保JPQL语句正确
                .pageSize(chunkSize)
                .build();
    }

}

2. 完善ReaderDataSourceConfiguration的@EnableJpaRepositories配置 #

添加basePackages指定该数据源对应的Repository包路径，确保Spring正确识别范围：

@Configuration
@EnableJpaRepositories(
        basePackages = "com.batch.job.reader.repository", // 替换为你的Reader相关Repository包路径
        entityManagerFactoryRef = "readerEntityManagerFactory",
        transactionManagerRef = "readerTransactionManager")
public class ReaderDataSourceConfiguration {
    // 原代码不变
}

3. 清理BatchMetaDataSourceConfiguration的冗余配置 #

H2数据源仅用于Spring Batch元数据存储，无需配置@EnableJpaRepositories，移除该注解避免干扰：

@Configuration
@EnableTransactionManagement
// 移除@EnableJpaRepositories注解
public class BatchMetaDataSourceConfiguration {
    // 原代码不变
}

4. 验证EntityManagerFactory的数据源绑定 #

在ReaderDataSourceConfiguration中添加日志打印数据源URL，确认绑定的是MySQL：

@Bean(name = "readerEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean readerEntityManagerFactory(
        EntityManagerFactoryBuilder builder) {
    DataSource dataSource = readerDataSource();
    // 打印数据源URL验证
    System.out.println("Reader DataSource URL: " + ((HikariDataSource)dataSource).getJdbcUrl());
    return builder
            .dataSource(dataSource)
            .packages("com.batch.job.reader")
            .persistenceUnit("readerPU") // 显式指定持久化单元，避免冲突
            .build();
}

5. 确认步骤事务管理器配置 #

当前SpringBatchConfig中步骤已指定readerTransactionManager，确保Reader读取数据时使用MySQL对应的事务管理器，这部分配置正确无需修改。

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