CODEWITHSUNDEEP
jdbcspring-batchspring-jdbcmssql-jdbc
Jordi
Jordi

Reputation: 23187

Spring batch: JdbcPagingItemReader doesn't obtain page 1 onwards

Here my reader:

@Bean
public ItemReader<Unitat> itemReader(PagingQueryProvider queryProvider) {
    return new JdbcPagingItemReaderBuilder<Unitat>()
        .name("creditReader")
        .dataSource(this.dataSource)
        .queryProvider(queryProvider)
        .rowMapper(this.unitatMapper)
        .pageSize(2)
        .build();
}

@Bean
    public SqlPagingQueryProviderFactoryBean queryProvider() {
        SqlPagingQueryProviderFactoryBean provider = new SqlPagingQueryProviderFactoryBean();

        provider.setDataSource(this.dataSource);
        provider.setSelectClause("SELECT im_notext1.nt1_id");
        provider.setFromClause("FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id");
        provider.setSortKey("nt1_id");

        return provider;
    }

Formatted query is:

SELECT
  TOP 2 im_notext1.nt1_id
FROM
  im_notext1
  LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id
ORDER BY
  nt1_id ASC

I know, it seems a dummy query. Only for simplifying context.

Shortly, PagingQueryProvider is engaged to generate "paged queries" like:

Page 0:

SELECT
  TOP 2 im_notext1.nt1_id
FROM
  im_notext1
  LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id
ORDER BY
  nt1_id ASC

Page 1:

SELECT
  TOP 2 im_notext1.nt1_id
FROM
  im_notext1
  LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id
WHERE
  ((nt1_id > ?))
ORDER BY
  nt1_id ASC

I've activated logs:

logging.level.org.springframework.batch.item.database=debug

I'm getting this error message:

com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'nt1_id'.

Really, nt1_id column is on both im_notext1 and im_notext2 tables (joined on from clause).

Logs:

Reading page 0
SQL used for reading first page: [SELECT TOP 2 im_notext1.nt1_id FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id ORDER BY nt1_id ASC]
Reading page 1
SQL used for reading remaining pages: [SELECT TOP 2 im_notext1.nt1_id FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id WHERE ((nt1_id > ?)) ORDER BY nt1_id ASC]
Using parameterList:[244029]

Any ideas?

Upvotes: 1

Views: 1141

Answers (1)

Minnow
Minnow

Reputation: 515

I was facing same issue using JdbcPagingItemReader with sql having column name alias

resolve it by nesting query in a table expression in the from (i.e. select * from (), so that I have unambiguous column names.

Before

SELECT
    C.CATEGORY_ID as CATEGORY_ID 
  , C.PRICE
FROM
    MY_SCHEMA.MY_TABLE_1        A
    JOIN MY_SCHEMA.MY_TABLE_2   B ON B.STORAGE_ID = A.STORAGE_ID
    JOIN MY_SCHEMA.MY_TABLE_3   C ON C.CATEGORY_ID = B.CATEGORY_ID
ORDER BY C.CATEGORY_ID asc

After

SELECT CATEGORY_ID from (
SELECT
    C.CATEGORY_ID as CATEGORY_ID 
  , C.PRICE
FROM
    MY_SCHEMA.MY_TABLE_1        A
    JOIN MY_SCHEMA.MY_TABLE_2   B ON B.STORAGE_ID = A.STORAGE_ID
    JOIN MY_SCHEMA.MY_TABLE_3   C ON C.CATEGORY_ID = B.CATEGORY_ID
    )
ORDER BY CATEGORY_ID asc

Upvotes: 1

Related Questions