Spring Batch - Problems Connecting to Postgres Database

Question

Up until now I had been using in-memory H2 DB with Spring Batch. However, now I switched to connecting to external postgres DB. Here was my connection object (with some obfuscation):

@Bean
public DataSource postgresDatasource() {
    DriverManagerDataSource datasource = new DriverManagerDataSource();
    datasource.setDriverClassName("org.postgresql.Driver");
    datasource.setUrl("jdbc:postgresql://x.x.x.x:xxxx/blah");
    datasource.setUsername("Joe");
    datasource.setPassword("password");
    return datasource;
}

When I start my application, I get:

Caused by: 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.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist

I then read that Spring Batch uses the database to save metadata for its recover/retry functionality, and with embedded databases, these are tables Spring Batch sets up by default. Ok, so that would explain why I had never seen this error before.

However, it said I could set this property:

spring.batch.initialize-schema=never

So I put this in my application.properties file. However, I am still getting the error. I would be grateful for any ideas.

Does this answer your question? [How to configure spring batch not to auto create batch tables for storing meta data?](https://stackoverflow.com/questions/54950551/how-to-configure-spring-batch-not-to-auto-create-batch-tables-for-storing-meta-d) — Mahmoud Ben Hassine, Aug 25 '20 at 07:28
@MahmoudBenHassine , I was able to do something very similar. Although your link was essentially the same approach I ended up answering this myself with the more complete solution in the event that someone might find the details helpful for their own issues in the future. — Timothy Clotworthy, Aug 25 '20 at 12:30

score 0 · Accepted Answer · edited Aug 25 '20 at 17:21

I was able to address this myself. Ultimately I needed the Spring Batch repository independent from my actual target relational database. So I found this reference:

https://github.com/spring-projects/spring-batch/blob/342d27bc1ed83312bdcd9c0cb30510f4c469e47d/spring-batch-core/src/main/java/org/springframework/batch/core/configuration/annotation/DefaultBatchConfigurer.java#L84

I was able to take the DefaultBatchConfigurer class from that example and make a minor change to the data source by adding the @Qualifier for embedded/local data source:

@Autowired(required = false)
public void setDataSource(@Qualifier("dataSource") DataSource dataSource) {
    this.dataSource = dataSource;
    this.transactionManager = new DataSourceTransactionManager(dataSource);
}

Then, on my Spring Batch reader (in my other batch config class), I made a minor change to the data source by adding the @Qualifier for postgres data source:

@Bean
public ItemReader<StuffDto> itemReader(@Qualifier("postgresDataSource")DataSource dataSource) {
return new JdbcCursorItemReaderBuilder<StuffDto>()
    .name("cursorItemReader")
    .dataSource(dataSource)
    .sql(GET_DATA)
    .rowMapper(new BeanPropertyRowMapper<>(StuffDto.class))
    .build();
}

Then lastly (or firstly really as I did these first), I explicitly named my data source beans so java could tell them apart to use as above:

@Configuration
public class PersistenceContext {


    @Bean(name = "dataSource")
    public DataSource dataSource() {
        DriverManagerDataSource datasource = new DriverManagerDataSource();
        datasource.setDriverClassName("org.h2.Driver");
        datasource.setUrl("jdbc:h2:file:/tmp/test");
        datasource.setUsername("sa");
        datasource.setPassword("");
        return datasource;
    }

    @Bean(name = "postgresDataSource")
    public DataSource postgresDatasource() {
        DriverManagerDataSource datasource = new DriverManagerDataSource();
        datasource.setDriverClassName("org.postgresql.Driver");
        datasource.setUrl("jdbc:postgresql://x.x.x.x:xxxx/blah");
        datasource.setUsername("joe");
        datasource.setPassword("password");
        return datasource;    }
}

Once I did all the above, the error disappeared and everything worked.

Spring Batch - Problems Connecting to Postgres Database

1 Answers1