春季批处理 – 连接到Postgres数据库的问题

huangapple go评论82阅读模式
英文:

Spring Batch - Problems Connecting to Postgres Database

问题

迄今为止,我一直在使用内存中的 H2 数据库配合 Spring Batch。然而,现在我切换到连接外部的 PostgreSQL 数据库。这是我的连接对象(部分内容已进行了混淆):

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

当我启动应用程序时,我收到以下错误:

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

然后我阅读到 Spring Batch 使用数据库来保存其恢复/重试功能的元数据,并且在嵌入式数据库中,这些是 Spring Batch 默认设置的表。好的,这就解释了为什么我以前从未看到过这个错误。

然而,文档中提到我可以设置这个属性:

spring.batch.initialize-schema=never

所以我将这个属性放在了我的 application.properties 文件中。然而,我仍然得到相同的错误。如果有任何想法,我将不胜感激。

英文:

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.

答案1

得分: 0

我自己解决了这个问题。最终,我需要使Spring Batch存储库与我的实际目标关系数据库独立开来。因此,我找到了这个参考链接:

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

我从这个示例中获取了DefaultBatchConfigurer类,并通过为嵌入式/本地数据源添加@Qualifier来对数据源进行了轻微更改:

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

然后,在我的Spring Batch读取器(位于另一个批处理配置类中)中,我通过为postgres数据源添加@Qualifier对数据源进行了轻微更改:

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

然后最后(或者实际上是最开始),我显式地为我的数据源bean命名,以便Java可以区分它们并像上面那样使用:

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

完成了上述所有步骤后,错误消失了,一切都正常工作。

英文:

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(&quot;dataSource&quot;) 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&lt;StuffDto&gt; itemReader(@Qualifier(&quot;postgresDataSource&quot;)DataSource dataSource) {
return new JdbcCursorItemReaderBuilder&lt;StuffDto&gt;()
    .name(&quot;cursorItemReader&quot;)
    .dataSource(dataSource)
    .sql(GET_DATA)
    .rowMapper(new BeanPropertyRowMapper&lt;&gt;(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 = &quot;dataSource&quot;)
    public DataSource dataSource() {
        DriverManagerDataSource datasource = new DriverManagerDataSource();
        datasource.setDriverClassName(&quot;org.h2.Driver&quot;);
        datasource.setUrl(&quot;jdbc:h2:file:/tmp/test&quot;);
        datasource.setUsername(&quot;sa&quot;);
        datasource.setPassword(&quot;&quot;);
        return datasource;
    }

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

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

huangapple
  • 本文由 发表于 2020年8月25日 04:15:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/63568137.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定