0

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);
}

}

Jeff Cook
  • 7,956
  • 36
  • 115
  • 186
  • 1
    Hope it helps https://docs.spring.io/spring-boot/docs/2.0.0.M7/reference/htmlsingle/#howto-initialize-a-spring-batch-database – Nghia Do Jul 01 '18 at 15:02

2 Answers2

1

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

akortex
  • 5,067
  • 2
  • 25
  • 57
  • I am getting below error : Caused by: java.lang.IllegalStateException: Input resource must exist (reader is in 'strict' mode): class path resource [C:/Users/pc/students.csv]. Though code works fine. – Jeff Cook Jul 01 '18 at 15:06
  • This seems to indicate that the csv you're trying to read is not where Spring expects it. Try putting it under the `resources` folder and try again. – akortex Jul 01 '18 at 15:08
  • But its not creating test.student table automatically.. How to create it automatically ? – Jeff Cook Jul 01 '18 at 15:17
0

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

moilejter
  • 958
  • 8
  • 9