Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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.
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.