JOIN Query in SqlPagingQueryProviderFactoryBean not working [BATCH-2394]

Niraj Gadgilwar opened BATCH-2394 and commented

I am using 3.0.4.RELEASE version of Spring Batch in my project. I have a batch which is using JdbcPagingItemReader to read records from DB2. In that I have a JOIN on 2 tables and have some where conditions. At the last I am providing column by which I want to sort my records. But because I am using table aliases, its giving me error. When skipping alias name, then its give problem while fetching the next set of result. I searched and found that this issue got resolved in [2.1.9 Release] (https://jira.spring.io/browse/BATCH-1848) but I couldn't find the fix in later releases. Anybody has any idea about this issue and the solution too.

You can find the code snippet of query configuration below.

<bean id="releaseSqlPagingQueryProvider" class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="selectClause" value="SELECT M.* " />
<property name="fromClause" value="FROM MASTER AS M JOIN RELEASE AS R ON M.EMPLOYEE_ID = R.EMPLOYEE_ID " />
<property name="whereClause" >
<value>
<![CDATA[
WHERE R.COLUMN_1 = ?
]]>
</value>
</property>
<property name="sortKey" value="EMPLOYEE_ID" />

And the configuration for JdbcPagingItemReader contains below values.

<property name="pageSize" value="200" />
<property name="fetchSize" value="20" />
When there are less than 200 records, query works perfectly. When there are more than 200 records, I get below attached exception.

Affects: 2.2.7, 3.0.4

Reference URL: http://stackoverflow.com/questions/30260179/spring-batch-join-query-in-sqlpagingqueryproviderfactorybean-not-working?noredirect=1#comment48654382_30260179

Attachments:

• Exception.txt (6.98 kB)

1 votes, 9 watchers

Start with the SqlPagingQueryProviderFactoryBean configuration shown in the issue and inspect the attached Exception.txt. Reproduce the JOIN query with more than 200 records, focusing on table aliases and the EMPLOYEE_ID sort key across subsequent pages. Done means joined queries page correctly without alias-related errors or failures when fetching the next result set.