PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY =?

Question

I want the Spring Batch metadata to be created on the MySQL server and used all the existing tables from Oracle to fetch data from it and put it into the MongoDB.

I created the following configurations, but somehow missing the trick to create the Spring Batch metadata tables though configuration.

spring.data.mongodb.host=localhost
spring.data.mongodb.port=27017
spring.data.mongodb.database=MY_DB

#By default, Spring runs all the job as soon as it has started its context.
spring.batch.job.enabled=false

spring.batch.initialize-schema=always
spring.batch.tablePrefix=test.BATCH_
#spring.batch.initializer.enabled=false
spring.batch.schema=org/springframework/batch/core/schema-mysql.sql

spring.datasource.url=jdbc:oracle:thin:@localhost:1527:OR_DEV
spring.datasource.username=EDR_USR
spring.datasource.password=txz$2Zhr
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

jdbc.batch.jdbcUrl=jdbc:mysql://localhost:3306/test?useSSL=false
jdbc.batch.username=root
jdbc.batch.password=root
jdbc.batch.driver-class-name=com.mysql.cj.jdbc.Driver

DBConfig.java

@Configuration
@ComponentScan
public class DBConfig {

    @Autowired
    private Environment env;

    @Bean(name="oracleDS")
    public DataSource batchDataSource(){          
           return DataSourceBuilder.create()
                    .url(env.getProperty("spring.datasource.url"))
                    .driverClassName(env.getProperty("spring.datasource.driver-class-name"))
                    .username(env.getProperty("spring.datasource.username"))
                    .password(env.getProperty("spring.datasource.password"))
                    .build();          
    } 

    @Bean(name="mysqlDS")
    @Primary
    public DataSource mysqlBatchDataSource(){          
           return DataSourceBuilder.create()
                    .url(env.getProperty("jdbc.batch.jdbcUrl"))
                    .driverClassName(env.getProperty("jdbc.batch.driver-class-name"))
                    .username(env.getProperty("jdbc.batch.username"))
                    .password(env.getProperty("jdbc.batch.password"))
                    .build();          
    }
}

Job

@GetMapping("/save-student")
    public String saveStudent() {
        JobParameters params = new JobParametersBuilder()
                .addString("JobID", String.valueOf(System.currentTimeMillis()))
                .addString("Job_ID", String.valueOf(System.currentTimeMillis()))
                .addDate("date", new Date())
                .toJobParameters();
        try {
            JobExecution jobExecution = jobLauncher.run(countryJob, params);
            log.debug("Job Status : " + jobExecution.getStatus());
        } catch (JobExecutionAlreadyRunningException | JobRestartException | JobInstanceAlreadyCompleteException
                | JobParametersInvalidException e) {
            log.error("Job Failed : "+e.getMessage());
        }
        return "";
    }

Error:

{
    "timestamp": "2019-03-27T14:57:52.745+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "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: Table 'test.batch_job_instance' doesn't exist",
    "path": "/save-student"
}

Answer 1

Write below code in application.properites file. It will automatically generate tables required for the spring batch.

spring.batch.initialize-schema=always

Answer 2

After spend some time, I could achieve the results i hoped by setting the batch datasource as @Primary. Like this:

Datasource configuration

@Bean
@Primary
public DataSource batchDataSource() {
    return batchDataSourceProperties().initializeDataSourceBuilder().build();
}

@Bean
@ConfigurationProperties("spring.datasource.batch")
public DataSourceProperties batchDataSourceProperties() {
    return new DataSourceProperties();
}

OR, In the Batch configuration i set the data source

@Component
public class BatchDSConfiguration extends DefaultBatchConfigurer {
@Override
@Autowired(required = false)
public void setDataSource(@Qualifier("batchDataSource") DataSource dataSource) {
    super.setDataSource(dataSource);        
}
...

}

Application properties

spring.datasource.batch.driverClassName=org.h2.Driver
spring.datasource.batch.url=jdbc:h2:mem:batchdb
spring.datasource.batch.username=sb
spring.datasource.batch.password=
spring.batch.jdbc.initialize-schema=ALWAYS

I Tried to create a custom JobRepository in the BatchConfigurer, but could not create the batch schema with multiple data sources. I Will try more and if i can do it i'll come back here to tell.

Hope it helps.