Spring Batch JdbcPagingItemReader seems not EXECUTING ALL THE ITEMS

Question

I'm working on an app that extract records from an Oracle database and then are exported as one single tabulated file.

However, when I attempt to read from the DB using JdbcPagingItemReader and write to a file I only get the number of records specified in pageSize. So if the pageSize is 10, then I get a file with 10 lines and the rest of the records seem to be ignored. So far, I haven't been able to find whats is really going on and any help would be most welcome.

Here is the JdbcPagingItemReader config:

<bean id="databaseItemReader"
class="org.springframework.batch.item.database.JdbcPagingItemReader" >
    <property name="dataSource" ref="dataSourceTest" />
<property name="queryProvider">
  <bean
    class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
    <property name="dataSource" ref="dataSourceTest" />
    <property name="selectClause" value="SELECT *" />
    <property name="fromClause" value="FROM *****" />
    <property name="whereClause" value="where snapshot_id=:name" />
    <property name="sortKey" value="snapshot_id" />
  </bean>
</property>
<property name="parameterValues">
   <map>
    <entry key="name" value="18596" />
   </map>
</property>
    <property name="pageSize" value="100000" />
<property name="rowMapper">
    <bean class="com.mkyong.ViewRowMapper" />
</property>

<bean id="itemWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">
<!-- write to this csv file -->
<property name="resource" value="file:cvs/report.csv" />
<property name="shouldDeleteIfExists" value="true" />

<property name="lineAggregator">
      <bean
       class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
    <property name="delimiter" value=";" />
    <property name="fieldExtractor">
          <bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
        <property name="names" value="ID" />
       </bean>
    </property>
       </bean>  
</property>

  <job id="testJob" xmlns="http://www.springframework.org/schema/batch">
<step id="step1">
  <tasklet>
    <chunk reader="databaseItemReader" writer="itemWriter" commit-interval="1" />
  </tasklet>
</step>

thanks

Possible duplicate of http://stackoverflow.com/questions/33345157/spring-batch-jdbcpagingitemreader-seems-not-paginating?rq=1 — Sheetal Mohan Sharma, Feb 12 '16 at 09:27
no it's not the same they changed the use of the reader they have used finally jdbcCursorItemReader in my case i want absolutely to use the JdbcPagingItemReader for performance reasons — user3580733, Feb 12 '16 at 09:49
And if possibel keep both pagesize and commit-interval same.If nothing works, then i would say to use JdbcCursorItemReader which reads one by one.. — surya, Feb 12 '16 at 22:23

score 2 · Answer 1 · edited Feb 12 '16 at 21:31

2

it was the scope="step" that was missing it should be:

<bean id="databaseItemReader"
    class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step">

edited Feb 12 '16 at 21:31

Marvin

13,325
3
51
57

answered Feb 12 '16 at 10:04

user3580733

71
1
2
5

See my comment below for java config – Amos Kosgei Mar 15 '18 at 11:44

surya · Answer 2 · 2016-02-12T23:37:58.057

Your setting seem incorrect for whereClause and sort key can not be same because pagesize works hand to hand with your sorting column name.

Check how is your data(in corresponding table) looks like.

In spring batch , as per your configuration, spring will create and execute as given below.. first query executed with pagesize = 10 , is like following

SELECT top 10  FROM tableName where snapshot_id=18596 snapshot_id > 10

Second /remaining query executed depends on your sort key.

SELECT *  FROM tableName  where snapshot_id=18596 snapshot_id > 10
SELECT *  FROM tableName  where snapshot_id=18596 snapshot_id > 20

and so on.. try running this query in database , doesn't it look weird . :-)

If you don't need where clause, remove it.

And if possible keep page size and commit-interval same, because that's how you decide to process and persist. But of course that depends on your design. So you decide.

score 0 · Answer 3 · answered Mar 15 '18 at 11:43

Adding @StepScope made my item reader take off with paging capability.

@Bean
@StepScope
ItemReader<Account> ItemReader(@Value("#{jobParameters[id]}") String id) {
    JdbcPagingItemReader<Account> databaseReader = new JdbcPagingItemReader<>();
    databaseReader.setDataSource(dataSource);
    databaseReader.setPageSize(100);
    databaseReader.setFetchSize(100);
    PagingQueryProvider queryProvider = createQueryProvider(id);
    databaseReader.setQueryProvider(queryProvider);
    databaseReader.setRowMapper(new BeanPropertyRowMapper<>(Account.class));
    return databaseReader;
}

Spring Batch JdbcPagingItemReader seems not EXECUTING ALL THE ITEMS

3 Answers3

Linked