Invalid column name exception - JdbcPagingItemReader query with alias

Question

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

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.apache.commons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.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

chillworld · Answer 1 · 2021-01-22T09:34:42.200

Some years later I stumbled upon the same issue. I would like to give the correct answer as the correct answer is not entirely set here.

@Bean
public PagingQueryProvider queryProvider(final DataSource dataSource) {
    final OraclePagingQueryProvider provider = new OraclePagingQueryProvider();
    provider.setSelectClause("select e.first_name, e.dept_no as dept_no, e.employee_id as employee_id");
    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("dept_no", Order.ASCENDING);
    sortKeys.put("employee_id", Order.ASCENDING);
    provider.setSortKeys(sortKeys);
    try {
        return provider;
    } catch (final Exception e) {
        e.printStackTrace();
        return null;
    }
}

So what changed :

e.dept_no as dept_no, e.employee_id as employee_id is added to the select query.
sortKeys.put may not use the alias. The issue is that the sortKeys will retrieve the data from the result list.
If you set an alias => the rowmapper try to fetch alias.column_name and that is not present as the resultset doesn't contains the aliasses.
Therefore also we also add the AS by default in the select query in order to avoid ambigious column names.

score 1 · Answer 2 · answered Apr 16 '19 at 22:55

1

don't use this (e.) -point- at your SortKeys order by experssion, and it will be fine

answered Apr 16 '19 at 22:55

Chakib Arrama

93
3
12

BrianC · Accepted Answer · 2018-04-11T17:30:55.027

0

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.

edited Apr 11 '18 at 17:30

answered Apr 10 '18 at 20:43

BrianC

1,793
1
18
26

Thanks BrianC. It helps – Vee.Dee Apr 11 '18 at 03:21

score 0 · Answer 4 · answered Dec 15 '21 at 02:50

I have answered it here what worked for me - https://stackoverflow.com/a/70357862/1909708.

I did use column alias in the sort clause and along with that column alias was kept same as the column name (which included the table alias).

Its an existing issue in Spring Batch - https://github.com/spring-projects/spring-batch/issues/1208

Invalid column name exception - JdbcPagingItemReader query with alias

4 Answers4

Linked