JdbcPagingItemReader not working with join query

Question

I have two tables table 1 and table 2. Both have same name id column.

As it's a left outer join query, I have to use aliases.

In sortKeys, If I mention id , error is

Caused by: org.postgresql.util.PSQLException: ERROR: column reference "id" is ambiguous

In sortKeys, If I mention t1.id , error is

Caused by: org.postgresql.util.PSQLException: The column name t1.id was not found in this ResultSet.

How to handle such queries?

I am using latest spring boot version 2.2.5.RELEASE

I have seen other posts related to this issue on stackoverflow. However, those are in older versions of spring batch and hence I should not face this issue.

Answer 1

One way you could solve this issue, is by adding the t1.id as projection and naming it something else, like :

select t1.id as id_t1 ....

and in your sortKeys add "id_t1".

Hope this helps

Answer 2

The answer by @youness.bout and another kind programmer worked for me. I've documented it here https://github.com/spring-projects/spring-batch/issues/1208#issuecomment-994234546. It appears to be a known issue in Spring Batch project.

Rewriting the answer here:

1. Using the same column name alias as that in column name in the select clause - t1.table1_column1 as 't1.table1_column1'
2. Enclosing the column name alias in single quote (') in the select clause - t1.table1_column1 as 't1.table1_column1'
3. Used the column name alias as the sort key - setSortKey("t1.table1_column1")

Here's what I did in kotlin

    @Bean
    @StepScope
    fun readQueryProvider() = SqlPagingQueryProviderFactoryBean().apply {
        setDataSource(dataSource)
        setSelectClause("select t1.table1_column1 as 't1.table1_column1', t1.table1_column2,"
                + " t1.table1_column3, t2.table2_column2, t3.table3_column3")
        setFromClause("table1 t1"
                + " inner join table2 t2 on t1.table1_column1 = t2.table2_column1"
                + " inner join table3 t3 on t2.table2_column2 = t3.table3_column2"
                + " inner join table4 t4 on t1.table1_column1 = t4.table4_column1")
        setWhereClause("where t3.table3_column2 = :placeholder1" 
                + " and t2.table2_column3 = :placeholder2 and t4.table4_column2 = :placeholder3")
        setSortKey("t1.table1_column1")
    }