Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID,

Question

I am developing the Spring Boot Batch example. In this example I am getting the below error. Using spring-boot-starter-parent is 2.0.3.RELEASE.

I suspect Spring Boot expecting the tables to be get created automatically, but its not creating and hence getting the below error.

Any simple quick guidance ?

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:781) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:335) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at com.prateek.SpringBatchCsvDatabaseApplication.main(SpringBatchCsvDatabaseApplication.java:14) [classes/:na]
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is java.sql.SQLSyntaxErrorException: Table 'test.batch_job_instance' doesn't exist
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstances(JdbcJobInstanceDao.java:230) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
    at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstances(SimpleJobExplorer.java:173) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_151]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_151]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_151]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at com.sun.proxy.$Proxy50.getJobInstances(Unknown Source) ~[na:na]
    at org.springframework.batch.core.JobParametersBuilder.getNextJobParameters(JobParametersBuilder.java:254) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:162) ~[spring-boot-autoconfigure-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:179) ~[spring-boot-autoconfigure-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:134) ~[spring-boot-autoconfigure-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:128) ~[spring-boot-autoconfigure-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'test.batch_job_instance' doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-2.7.9.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:666) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    ... 27 common frames omitted

database.properties. I am using mysql 8.0.x version

DATA SOURCE

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

BatchConfig.java

@Configuration
@EnableBatchProcessing
public class BatchConfig {

    @Bean
    Job job(JobBuilderFactory jobBuilderFactory, StepBuilderFactory stepBuilderFactory, Step step1) throws Exception {

        TaskletStep s1 = stepBuilderFactory.get("file-db")
                .<Student, Student>chunk(100)
                .reader(step1.fileItemReader(null))
                .writer(step1.batchItemWriter(null))
                .build();

        return jobBuilderFactory.get("etl")
                .incrementer(new RunIdIncrementer())
                .start(s1)
                .build();
    }
}

Step.java

@Configuration
public class Step {

    @Bean
    public FlatFileItemReader<Student> fileItemReader(@Value("${input}") Resource resource){
        return new FlatFileItemReaderBuilder<Student>()
                .name("file-reader")
                .resource(resource)
                .targetType(Student.class)
                .linesToSkip(1)
                .delimited().delimiter(",").names(new String[]{"firstName", "lastName",  "email", "age"})
                .build();
    }

    @Bean
    public JdbcBatchItemWriter<Student> batchItemWriter(DataSource dataSource){
        return new JdbcBatchItemWriterBuilder<Student>()
                .dataSource(dataSource)
                .sql("INSERT INTO STUDENT (FIRST_NAME, LAST_NAME, EMAIL, AGE) VALUES (:firstName, :lastName, :email, :age)")
                .beanMapped()
                .build();
    }

}

SpringBatchCsvDatabaseApplication.java

@SpringBootApplication public class SpringBatchCsvDatabaseApplication {

public static void main(String[] args) {

    System.setProperty("input", "file://" + new File("C:/Users/pc/students.csv").getAbsolutePath());
    SpringApplication.run(SpringBatchCsvDatabaseApplication.class, args);
}

}

Answer 1

I got the same error using Spring Boot 3.x with Spring Batch 5.x. For me the solution was to remove the @EnableBatchProcessing annotation. Then the property was taking effect:

spring.batch.jdbc.initialize-schema=always
spring.batch.jdbc.initialize-schema=embedded # default
spring.batch.jdbc.initialize-schema=never

Use always to create the metadata tables or never to not create them.

The use of @EnableBatchProcessing is discouraged as of Spring Boot 3.0.0: Migtation Guide.

Answer 2

The error you report is about the Spring Batch metadata tables, not your application tables. I think you need to follow the advice in this question: Spring Batch Framework - Auto create Batch Table

Answer 3

I think that by default Spring will only initialize the schema if you're running an embedded database. You'll need to explicitly tell it to create the schema when using any other database. You can try to set this property and see if that helps you spring.batch.initialize-schema=always.

Have a look here for more: https://docs.spring.io/spring-boot/docs/2.0.3.RELEASE/reference/htmlsingle/#howto-database-initialization