无效的列名称异常

本文介绍了无效的列名称异常-带有别名的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.RELEASE spring-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; } }

由以下原因引起:java.sql.SQLException:无效的列名位于oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3965)〜[ojdbc6-11.2.0.3.jar:12.1.0.1.0]在oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:299)〜[ojdbc6-11.2.0.3.jar:12.1.0.1.0]在oracle.jdbc.driver.GeneratedResultSet.getObject(GeneratedResultSet.java:1394)〜[ojdbc6-11.2.0.3.jar:12.1.0.1.0]在org.apachemons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328)〜[commons-dbcp-1.4.jar:1.4]在org.apachemons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328)〜[commons-dbcp-1.4.jar:1.4]在org.springframework.batch.item.database.JdbcPagingItemReader $ PagingRowMapper.mapRow(JdbcPagingItemReader.java:333)〜[spring-batch-infrastructure-4.0.0.RELEASE.jar:4.0.0.RELEASE]在org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)〜[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]在org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)〜[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]在org.springframework.jdbc.core.JdbcTemplate $ 1.doInPreparedStatement(JdbcTemplate.java:667)〜[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]在org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)〜[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE] ... 72个共同的框架省略

Caused by: java.sql.SQLException: Invalid column name at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3965) ~[ojdbc6-11.2.0.3.jar:12.1.0.1.0] at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:299) ~[ojdbc6-11.2.0.3.jar:12.1.0.1.0] at oracle.jdbc.driver.GeneratedResultSet.getObject(GeneratedResultSet.java:1394) ~[ojdbc6-11.2.0.3.jar:12.1.0.1.0] at org.apachemons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328) ~[commons-dbcp-1.4.jar:1.4] at org.apachemons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328) ~[commons-dbcp-1.4.jar:1.4] at org.springframework.batch.item.database.JdbcPagingItemReader$PagingRowMapper.mapRow(JdbcPagingItemReader.java:333) ~[spring-batch-infrastructure-4.0.0.RELEASE.jar:4.0.0.RELEASE] at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE] at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:667) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE] ... 72 common frames omitted

推荐答案

我敢打赌，由于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.