Spring Batch Framework - Auto create Batch Table
Solution 1
Spring Batch uses the database to save metadata for its recover/retry functionality.
If you can't create tables in the database then you have to disable this behaviour
If you can create the batch metadata tables but not in runtime then you might create them manually
Solution 2
With Spring Boot 2.0 you probably need this: https://docs.spring.io/spring-boot/docs/2.0.0.M7/reference/htmlsingle/#howto-initialize-a-spring-batch-database
spring.batch.initialize-schema=always
By default it will only create the tables if you are using an embedded database.
Or
spring.batch.initialize-schema=never
To permanently disable it.
Solution 3
Spring Batch required following tables to run job
- BATCH_JOB_EXECUTION
- BATCH_JOB_EXECUTION_CONTEXT
- BATCH_JOB_EXECUTION_PARAMS
- BATCH_JOB_EXECUTION_SEQ
- BATCH_JOB_INSTANCE
- BATCH_JOB_SEQ
- BATCH_STEP_EXECUTION
- BATCH_STEP_EXECUTION_CONTEXT
- BATCH_STEP_EXECUTION_SEQ
If you are using h2 db then it will create all required table by default
- spring.h2.console.enabled=true
- spring.datasource.url=jdbc:h2:mem:testdb
- spring.datasource.driverClassName=org.h2.Driver
- spring.datasource.username=sa
- spring.datasource.password=
- spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
When you start using Mysql or any other database you need to add follwing properties into application.properties
spring.batch.initialize-schema=always
Solution 4
To enable auto create spring batch data-schema simply add this line to your spring application.properties file :
spring.batch.initialize-schema=always
To understand more about Spring batch meta-data schema :
https://docs.spring.io/spring-batch/trunk/reference/html/metaDataSchema.html
Solution 5
Since Spring Boot 2.5.0 use
# to always initialize the datasource:
spring.batch.jdbc.initialize-schema=always
# to only initialize an embedded datasource:
spring.batch.jdbc.initialize-schema=embedded
# to never initialize the datasource:
spring.batch.jdbc.initialize-schema=never
(spring.batch.initialize-schema
is deprecated since 2.5.0 for removal in 2.7.0)
Einn Hann
Updated on September 15, 2021Comments
-
Einn Hann over 2 years
I just created a batch job using Spring Batch framework, but I don't have Database privileges to run CREATE SQL. When I try to run the batch job I hit the error while the framework tried to create TABLE_BATCH_INSTANCE. I try to disable the
<jdbc:initialize-database data-source="dataSource" enabled="false"> ... </jdbc:initialize-database>
But after I tried I still hit the error
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
Anyway can disable the SQL, I just want to test my reader writer and processor work properly.
-
Einn Hann over 8 yearsFor the CREATE SQL it doesn't show the error so I assumed it is disabled. Just that when I re-run the batch job it prompt me the SQL error again but this time round is related to SELECT SQL.
-
Shailendra over 8 yearsTry running "SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = xxx and JOB_KEY =xxx" from database client with the credentials you are using for Spring Batch datasource configuration and see if you have any access issues.
-
Einn Hann over 8 yearsuse MapJobRepositoryFactoryBean and ResourcelessTransactionManager disabled the SQL thanks
-
Sushi over 6 yearsSergio, just a simple question, can I remove/avoid to create those tables ? I am just using spring batch to get data from a table from DB
-
Sergio over 6 yearsSushi, that's what the first of the options I listed do ("If you can't create tables in the database then...")
-
vijayakumarpsg587 almost 5 yearsI was trying to use postgreSQL to maintain the batch schema .This worked for me. THank you.!!!
-
Yaroslav over 3 yearsThank you a lot! I hit the next Error: JdbcSQLSyntaxErrorException: Table "BATCH_STEP_EXECUTION_CONTEXT" not found. I fixed my issue with this, and I'm using h2 db.
-
Dan over 2 yearsJust upgraded to Spring Boot 2.5.3 and had a problem with my H2 database + Flyway in test environment ... the manual creation of the BATCH tables was happening twice ... I had to set initialize-schema to
never
... except the key seems to have now changed:spring.batch.jdbc.initialize-schema=never
-
BitfulByte over 2 yearsGood to know, I've tried to pinpoint when this changed, but can't find any reference to this new key anywhere in the migration guides or current documentation. I'm curious where/how you've learned it's changed? @Dan
-
Dan over 2 yearsHey @pim-hazebroek ... you know I think I let JetBrains' IntelliJ help auto-complete that for me. I may have looked at the source code too? I'll get you a definitive answer when I swing back to my project.