how to use limit and offset clause in JdbcPagingItemReader in spring batch?

user_8453620 0
Viewed 2157

The table has more than 200 million records, but i need to restrict the select top 5 million records. I have tried with jdbcCursorItemReader which is taking around 2-3 hrs to select and write it to the csv file using single step by chunk processing, So i choose to go with parallel processing that spring is batch offering.
i,e by having taskExecutor and JdbcPagingItemReader making each 5 individual files of million each but the problem is i am not able to specify the limit and offset clause in query parameters. please help me on this. Approach better than this too is appreciated. 

<bean id="itemReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step">
  <property name="dataSource" ref="dataSource" />
  <property name="rowMapper">
    <bean class="MyRowMapper" />
  </property>
  <property name="queryProvider">
    <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
      <property name="dataSource" ref="dataSource" />
      <property name="sortKeys">
        <map>
                    <entry key="esmeaddr" value="ASCENDING"/>
                </map>
      </property>
      <property name="selectClause" value="elect cust_send,dest,msg,stime,dtime,dn_status,mid,rp,operator,circle,cust_mid,first_attempt,second_attempt,third_attempt,fourth_attempt,fifth_attempt,term_operator,term_circle,bindata,reason,tag1,tag2,tag3,tag4,tag5"
      />
      <property name="fromClause" value="FROM bill_log " />
      <property name="whereClause" value="where esmeaddr = '70897600000000' and country='India' and apptype='SMS' Limit 0,1000000" />
    </bean>
  </property>
  <property name="pageSize" value="1000000" />
  <property name="parameterValues">
    <map>
            <entry key="param1" value="#{jobExecutionContext[param1]}" />
            <entry key="param2" value="#{jobExecutionContext[param2]}" />
        </map>
  </property>
</bean>

spring-batch parallel-processing
1 Answers

You can't use a SQL LIMIT clause within that reader since that's what the reader itself will do. Instead, Spring Batch has the functionality built into the JdbcPagingItemReader. To set the max number of items to process, you can configure the reader with JdbcPagingItemReader#setMaxItemCount(5000000) and if there is an offset, you would set the JdbcPagingItemReader#setCurrentItemCount(offset). That being said, the offset will be overriden on a restart with any value it finds in the ExecutionContext. You can read more about this in the javadoc here: https://docs.spring.io/spring-batch/trunk/apidocs/org/springframework/batch/item/database/JdbcPagingItemReader.html

user_6180590
Related