CODEWITHSUNDEEP
springspring-batch
PAA
PAA

Reputation: 12035

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

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"
}

Upvotes: 10

Views: 28661

Answers (6)

adarsh mourya
adarsh mourya

Reputation: 1

If you use OracleDB first you should delete all the Batch related tables in oracleDB which is automatically generated..

Upvotes: 0

Shivam Anand
Shivam Anand

Reputation: 41

I was getting this error when I migrated to Spring boot 3 and jdk 17.

Apart from adding spring.batch.jdbc.initialize-schema=always in application file.

You should remove @EnableBatchProcessing from your spring batch config. As it is no longer required in spring batch 5.

https://github.com/spring-projects/spring-batch/issues/4336

Upvotes: 4

Ajay Ranwa
Ajay Ranwa

Reputation: 59

spring.batch.jdbc.initialize-schema=always

Upvotes: 4

Tiago PC
Tiago PC

Reputation: 129

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.

Upvotes: 1

Saksham Sangal
Saksham Sangal

Reputation: 79

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

spring.batch.initialize-schema=always

Upvotes: 7

PAA
PAA

Reputation: 12035

To fixed this issue, I executed the MYSQL spring batch metadata tables manually by executing the script from here: MySQL Spring Batch Metadata tables script

Since we've two dataSources, Spring Batch is unable to create the metadata tables automatically and hence this error is coming. I executed the script manually as a workaround, but is there any way to fixed this issue through code?

Upvotes: 4

Related Questions