Table “BATCH_JOB_INSTANCE” not found; SQL statement – Spring Boot Batch

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 org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "BATCH_JOB_INSTANCE" not found; SQL statement:
 SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ? [42102-200]

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "BATCH_JOB_INSTANCE" not found; SQL statement:
 SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ? [42102-200]

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

Table “BATCH_JOB_INSTANCE” not found; SQL statement exception occurs when the spring boot batch database tables are not present in the database. Spring boot batch requires batch tables to hold batch execution information. When the spring boot batch program is started, the spring boot batch tables are created automatically. If the batch tables cannot be created or are not available in the database, the exception Table “BATCH_JOB_INSTANCE” not found; SQL statement will be thrown.

The following tables are required for the spring boot batch to execute.

• 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

The spring boot batch will create those tables automatically when the application starts. The spring boot batch is disabled to create the tables by default. The spring boot application should be enabled to create the tables to create and use the batch tables in the database.

Exception

The exception stack trace is shown as below. The exception will be shown if spring boot batch unable to find the batch tables in the database.

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2021-07-21 16:49:05.359 ERROR 74661 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

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 org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "BATCH_JOB_INSTANCE" not found; SQL statement:
SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ? [42102-200]
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239) ~[spring-jdbc-5.3.8.jar:5.3.8]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.8.jar:5.3.8]
. . . . . . . . . .
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
	at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "BATCH_JOB_INSTANCE" not found; SQL statement:
SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ? [42102-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]

Solution 1

The “spring.batch.initialize-schema” property should be added in the application.properties to create the batch tables in the database. The property should be set to “ALWAYS” to create the batch tables in the database. This property allows the spring boot batch to perform a database table check. Spring boot batch will use the tables if they exist. If the tables do not exist, it will create them for you.

application.properties

spring.batch.initialize-schema=ALWAYS

Solution 2

If your application does not require the spring boot batch tables, the following configurations should be added to prevent the tables from being created. The datasource configuration will block the creation and usage of tables in the spring boot application.

BatchConfiguration.java

package com.yawintutor;

import javax.sql.DataSource;

import org.springframework.batch.core.configuration.annotation.DefaultBatchConfigurer;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Configuration;

@Configuration
@EnableAutoConfiguration(exclude = {HibernateJpaAutoConfiguration.class})
@EnableBatchProcessing
public class BatchConfiguration extends DefaultBatchConfigurer {
    
    @Override
    public void setDataSource(DataSource dataSource) {
        super.setDataSource(null);
    }
}

Solution 3

In some of spring boot version, the following code works to prevent the spring boot batch table creation. The following code should be added in the application.yaml file to prevent the batch table to create and use in the application.

application.yaml

batch:
        initialize-schema: never
        initializer:
            enabled: false