I'm learning how to work with Spring Batch for processing data.
Do i need to create tables with my prefix manually based on schema in Spring Batch core? Or am i missing something?
Here some sources from my project:
BatchConfiguration
@Configuration
@EnableScheduling
@FieldDefaults(level = PRIVATE, makeFinal = true)
@RequiredArgsConstructor
public class BatchConfiguration {
BatchProperties properties;
OrderService orderService;
@Bean
public Job updateOrderStatusJob(JobRepository jobRepository, PlatformTransactionManager platformTransactionManager) {
Step updateOrderStatusStep = new StepBuilder("updateOrderStatusStep", jobRepository)
.tasklet(orderStatusUpdateTasklet(), platformTransactionManager)
.build();
return new JobBuilder(properties.getJob().getName(), jobRepository)
.start(updateOrderStatusStep)
.listener(orderStatusUpdateJobListener())
.build();
}
@Bean
public Tasklet orderStatusUpdateTasklet() {
return new OrderStatusUpdateTasklet(orderService);
}
@Bean
public JobExecutionListener orderStatusUpdateJobListener() {
return new OrderStatusUpdateJobListener();
}
@Bean
public OrderStatusUpdateJobScheduler orderStatusUpdateJobScheduler(JobLauncher jobLauncher, Job job) {
return new OrderStatusUpdateJobScheduler(jobLauncher, job);
}
}
OrderStatusUpdateTasklet
@FieldDefaults(level = PRIVATE, makeFinal = true)
@RequiredArgsConstructor
public class OrderStatusUpdateTasklet implements Tasklet {
OrderService orderService;
@Override
public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception {
LocalDateTime tenMinutesAgo = LocalDateTime.now().minusMinutes(10);
List<Order> orders = orderService.getAllOrders();
List<Order> updatedOrders = new ArrayList<>();
for (Order order : orders) {
if (order.getCreatedAt().isBefore(tenMinutesAgo) && order.getIsActive()) {
order.setIsActive(false);
updatedOrders.add(order);
}
}
orderService.saveOrders(updatedOrders);
return FINISHED;
}
}
OrderStatusUpdateJobScheduler
@FieldDefaults(level = PRIVATE, makeFinal = true)
@Slf4j
@RequiredArgsConstructor
public class OrderStatusUpdateJobScheduler {
private static final int TEN_SECONDS = 10000;
JobLauncher jobLauncher;
Job updateOrderStatusJob;
@Scheduled(fixedDelay = TEN_SECONDS)
public void launchStatusUpdateJob() {
try {
jobLauncher.run(updateOrderStatusJob, new JobParameters());
} catch (Exception e) {
log.error("Error launching order status update job", e);
}
}
}
application.yml
server:
port: ${BT_PORT:8080}
spring:
name: spring-boot-batch-test
config:
import:
- classpath:application-db.yml
- classpath:application-batch.yml
application-batch.yml
spring:
batch:
job:
name: update-order-status
jdbc:
initialize-schema: always
table-prefix: MY_CUSTOM_BATCH_
application-db.yml
spring:
datasource:
url: ${DB_URL:jdbc:postgresql://localhost:5432/spring_batch_test}
username: ${DB_USERNAME:spring_batch_test}
password: ${DB_PASSWORD}
driver-class-name: ${DB_DRIVER:org.postgresql.Driver}
hikari:
pool-name: spring-batch-hikari-db-pool
connection-timeout: 30000
maximum-pool-size: 10
jpa:
hibernate:
ddl-auto: ${DB_HIBERNATE_DDL_AUTO:update}
dialect: ${DB_HIBERNATE_DIALECT:org.hibernate.dialect.PostgreSQLDialect}
my pom file:
<?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 http://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.3.5</version>
</parent>
<artifactId>spring-boot-batch-test</artifactId>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>3.3.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>3.3.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-batch</artifactId>
<version>3.3.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.36</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.4</version>
<scope>runtime</scope>
</dependency>
</dependencies>
</project>
I'm trying to create batch job using tables prefixed with "MY_CUSTOM_BATCH_". But my application failed to start.
On application startup I'm getting error bad sql grammar:
2025-02-13T12:07:59.928+06:00 ERROR 32268 --- [ scheduling-1] .n.m.b.e.j.OrderStatusUpdateJobScheduler : Error launching order status update job
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME
FROM MY_CUSTOM_BATCH_JOB_INSTANCE
WHERE JOB_NAME = ?
and JOB_KEY = ?]
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116) ~[spring-jdbc-6.1.14.jar:6.1.14]
...
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[na:na]
at java.base/java.lang.Thread.run(Thread.java:1583) ~[na:na]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "my_custom_batch_job_instance" does not exist
- Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.4.jar:42.7.4]
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732) ~[spring-jdbc-6.1.14.jar:6.1.14]
...
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.1.14.jar:6.1.14]
... 33 common frames omitted
2025-02-13T12:07:59.956+06:00 ERROR 32268 --- [ main] o.s.boot.SpringApplication : Application run failed
Currently my application creates tables but without the prefixes:
docker exec -it postgres16 psql -U spring_batch_test
psql (16.6 (Debian 16.6-1.pgdg120+1))
Type "help" for help.
spring_batch_test=> \dt
List of relations
Schema | Name | Type | Owner
--------+------------------------------+-------+-------------------
public | batch_job_execution | table | spring_batch_test
public | batch_job_execution_context | table | spring_batch_test
public | batch_job_execution_params | table | spring_batch_test
public | batch_job_instance | table | spring_batch_test
public | batch_step_execution | table | spring_batch_test
public | batch_step_execution_context | table | spring_batch_test
public | orders | table | spring_batch_test
(7 rows)
spring_batch_test=>
Does Spring Batch create tables with table prefix?
No, Spring Batch does not create the tables, you need to create them manually in the target database before running your job.
However, if you use Spring Boot, you can tell Spring Boot to create the tables for you by setting the spring.batch.job.jdbc.initialize-schema property (which you seem to be setting).
Based on the error, it seems that the table prefix is not taken into account, so make sure your app is correctly configured with the right prefix.