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



4 Comments

  • Muthuganesh M , August 11, 2022 @ 3:50 PM

    Hi Yawin,

    Thanks for giving this. But above solution is not working for me as well. I don’t want to create table itself since my db is restricted. Kindly help me if possible

    Thanks
    Muthu

    • Muthuganesh M , August 11, 2022 @ 4:43 PM

      It is working now. Without exclude statement in EnableAutoConfiguration. Thanks

  • Federico Martín , March 16, 2022 @ 2:28 AM

    None of the above solutions worked for me. The NOT have tested well. Before upload any useless solution test before.

    • Yawin Tutor , March 16, 2022 @ 10:32 AM

      Hi Federico Martin,

      Thanks for referring our website and for your suggestion.

      We make every effort to provide our viewers with a useful and accurate answer.
      Please check the entire code in this link Spring Boot Batch Example – Step by Step. Alternatively, you may share your code with us and we can assist you.

Leave a Reply

Your email address will not be published. Required fields are marked *