无效的列名称异常-带有别名的JdbcPagingItemReader查询

问题描述

当JdbcPagingItemReader查询具有联接和别名时，春季批处理步骤将失败.当我删除联接并仅从employee表中执行一个简单查询时，它工作正常.以下是失败的代码段.有人遇到过这样的问题吗?任何帮助，将不胜感激.

Spring batch step fails when JdbcPagingItemReader query has a join and alias. It works fine when I remove the join and just do a simple query from employee table. Below is the code snippet that fails. Did anyone encounter such an issue ? Any help would be appreciated.

spring-batch-core-4.0.1.RELEASEspring-boot-2.0.0.RELEASE

spring-batch-core-4.0.1.RELEASEspring-boot-2.0.0.RELEASE

@Autowired
    @Bean(destroyMethod = "")
    @StepScope
    public JdbcPagingItemReader<String> dbItemReader(final DataSource dataSource, final PreparedStatementSetter paramSetter) {
         return new JdbcPagingItemReaderBuilder<String>().name("dbReader").dataSource(dataSource)            .queryProvider(queryProvider(prodDataSource)).rowMapper((rs, rowNum) -> {
         return rs.getString("first_name");
         }).pageSize(1000).fetchSize(1000).build();
    }

@Bean
public PagingQueryProvider queryProvider(final DataSource dataSource) {
    final OraclePagingQueryProvider provider = new OraclePagingQueryProvider();
    provider.setSelectClause("select first_name");
    provider.setFromClause("from employee e join department d on e.dept_no= d.dept_no");
    provider.setWhereClause("where d.dept_name in ('HR','Marketing')");
    final Map<String, Order> sortKeys = new HashMap<String, Order>();
    sortKeys.put("e.dept_no", Order.ASCENDING);
    sortKeys.put("e.employee_id", Order.ASCENDING);
    provider.setSortKeys(sortKeys);
    try {
        return provider;
    } catch (final Exception e) {
        e.printStackTrace();
        return null;
    }
}

推荐答案

我敢打赌，由于JOIN是Page项读取器，因此无法使用JOIN.它需要一个一致的列表和主键来进行处理.您可以尝试将JOIN创建为具有其自己唯一主键的视图，然后对该视图运行简单查询.

I would bet that using a JOIN is not going to work since it is the Page item reader. It needs a consistent list and primary key to work against. You might try creating your JOIN as a view with its own unique primary key and running your simple query against that.

这篇关于无效的列名称异常-带有别名的JdbcPagingItemReader查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！