Spring Batch的JdbcBatchItemWriter无法写入MariaDB数据库求助

问题现象 #

• 系统可自动创建数据库表，说明数据库连接正常
• 使用内存数据库时能成功写入数据，切换到MariaDB后数据无法写入
• 日志无报错信息，但Spring Batch任务未启动执行

相关配置代码 #

DataSourceConfig.java #

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSource appDataSource() {
        DataSourceBuilder builder = DataSourceBuilder.create();
        builder.driverClassName("org.mariadb.jdbc.Driver");
        builder.url("jdbc:mariadb://localhost:3306/people");
        builder.username("user");
        builder.password("pwd");
        return builder.build();
    }
}

BatchConfiguration.java #

@Configuration
public class BatchConfiguration {

    // tag::readerwriterprocessor[]
    @Autowired
    private DataSource dataSource;

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public FlatFileItemReader<Person> reader() {
        return new FlatFileItemReaderBuilder<Person>()
            .name("personItemReader")
            .resource(new ClassPathResource("sample-data.csv"))
            .delimited()
            .names(new String[]{"firstName", "lastName"})
            .fieldSetMapper(new BeanWrapperFieldSetMapper<Person>() {{
                setTargetType(Person.class);
            }})
            .build();
    }

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

    @Bean
    public JdbcBatchItemWriter<People> writer() {
        JdbcBatchItemWriter<People> writer = new JdbcBatchItemWriter<>();
        writer.setDataSource(dataSource);
        writer.setSql("INSERT INTO people (first_name, last_name) VALUES (?, ?)");
        writer.setItemPreparedStatementSetter((item, ps) -> {
            ps.setString(1, item.getFirstName());
            ps.setString(2, item.getLastName());

            // Log the item being processed
            System.out.println("Processing item: " + item);
        });
        return writer;
    }


    // end::readerwriterprocessor[]

    // tag::jobstep[]
    @Bean
    public Job importUserJob(JobRepository jobRepository,
            JobCompletionNotificationListener listener, Step step1) {
        return new JobBuilder("importUserJob", jobRepository)
            .incrementer(new RunIdIncrementer())
            .listener(listener)
            .flow(step1)
            .end()
            .build();
    }

    @Bean
    public Step step1(JobRepository jobRepository,
                      JpaTransactionManager transactionManager, JdbcBatchItemWriter<People> writer) {
        return new StepBuilder("step1", jobRepository)
            .<Person, People> chunk(10, transactionManager)
            .reader(reader())
            .processor(processor())
            .writer(writer)
            .build();
    }
    // end::jobstep[]
}

BatchProcessingApplication.java #

@SpringBootApplication
@EnableBatchProcessing(dataSourceRef ="appDataSource")
public class BatchProcessingApplication {

    public static void main(String[] args) throws Exception {
        SpringApplication.run(BatchProcessingApplication.class, args);
    }
}

Pom文件 #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>batch-processing-complete</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>batch-processing-complete</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

排查与解决建议 #

1. 开启Batch任务自动执行 #

Spring Boot默认不会自动启动Batch任务，需添加配置：
在application.properties中加入：

spring.batch.job.enabled=true

或在主类中手动触发任务：

@SpringBootApplication
@EnableBatchProcessing(dataSourceRef ="appDataSource")
public class BatchProcessingApplication {

    public static void main(String[] args) throws Exception {
        SpringApplication.run(BatchProcessingApplication.class, args);
    }

    @Bean
    public CommandLineRunner runJob(JobLauncher jobLauncher, Job importUserJob) {
        return args -> {
            JobParameters jobParameters = new JobParametersBuilder()
                    .addLong("time", System.currentTimeMillis())
                    .toJobParameters();
            jobLauncher.run(importUserJob, jobParameters);
        };
    }
}

2. 调整事务管理器匹配度 #

当前使用JpaTransactionManager，但JdbcBatchItemWriter基于JDBC，建议改用DataSourceTransactionManager：

@Bean
public DataSourceTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource);
}

同时修改Step定义中的事务管理器引用。

3. 校验数据类型与映射 #

确认Person、People类的字段类型与数据库表字段一致，尤其是firstName、lastName是否为String类型。

4. 开启详细日志排查 #

在application.properties中添加日志配置，查看Batch执行细节：

logging.level.org.springframework.batch=DEBUG
logging.level.org.springframework.jdbc=DEBUG

5. 验证数据库权限 #

确认数据库用户user拥有people表的INSERT权限，可手动执行插入语句测试。

6. 规范JdbcBatchItemWriter构建方式 #

使用JdbcBatchItemWriterBuilder构建，避免遗漏初始化步骤，同时简化参数设置：

@Bean
public JdbcBatchItemWriter<People> writer() {
    return new JdbcBatchItemWriterBuilder<People>()
            .dataSource(dataSource)
            .sql("INSERT INTO people (first_name, last_name) VALUES (:firstName, :lastName)")
            .beanMapped()
            .build();
}

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