Announcement Announcement Module
Collapse
No announcement yet.
JdbcCursorItemReader reads nothing the second time Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcCursorItemReader reads nothing the second time

    Hi,

    I search on the web using google and also on this forum but I could not find anyone that had a problem like mine. This tells me that I am doing something fundamentally wrong.

    I have a batch job with 2 steps. The second uses a JdbcCursorItemReader to iterate over the items collected in the first step.

    This works fine the first time with the second step reading and writing all the items. If I run the job again with different parameters it finishes the job with a COMPLETED status but the JdbcCursorItemReader step did not read or write anything. I can see this by looking at the BATCH_STEP_EXECUTION table.

    If I make a code change and redeploy it works again one time. I think it must have something to do with the updatePriceReader that is not closing or reopening with new parameters.

    I am sure that the parameters change because I can see this in the BATCH_JOB_PARAMS table for the new JobInstance.

    StepExecution after first run
    Code:
    status:COMPLETED
    commitcount:40
    readcount:39
    writecount:39
    StepExecution after second run
    Code:
    status:COMPLETED
    commitcount:1
    readcount:0
    writecount:0
    Here is the job configuration.
    Code:
    	<job id="pricesBatch" restartable="true">
    		<step id="getPrices" next="updatePrices">
    			<tasklet>
    				<chunk reader="priceReader" writer="priceWriter" commit-interval="1"/>
    			</tasklet>
    		</step>
    		<step id="updatePrices">
    			<tasklet>
    				<chunk reader="updatePriceReader" writer="updatePriceWriter" commit-interval="1"/>
    				<listeners>
    					<listener ref="updatePriceListener"/>
    				</listeners>
    			</tasklet>
    		</step>
    		<listeners>
    				<listener ref="pricesJobListener"/>
    		</listeners>
    	</job>
    Here is the reader configuration.
    Code:
    	<beans:bean id="updatePriceReader" class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
    		<beans:property name="dataSource" ref="dataSource"/>
    		<beans:property name="sql" value="select ID, INSTRUCTIONID, PROCESSED, REFID FROM PRICEINSTRUCTION WHERE PROCESSED = 0 AND REFID= '#{jobParameters[refId]}'"/>
    		<beans:property name="rowMapper">
    			<beans:bean class="batch.jobs.prices.PriceInstructionMapper"/>
    		</beans:property>
    	</beans:bean>
    Last edited by divren; Oct 13th, 2009, 01:04 AM.

  • #2
    Fixed by changing approach

    Hi,

    I managed to prove my suspicion that the JdbcCursorItemReader uses the same query every time.

    I created a subclass of JdbcCursorItemReader and overrode the setSql method. Printed the sql and then called super.setSql(); The parameter used stayed the same as the first time so it appeared as if that nothing happened.

    I decided that I am going to change the implementation to get around this. So I added a @BeforeStep method to my new JdbcCursorItemReader subclass. In here I initialised and set the PreparedStatementSetter for the JdbcCursorItemReader. I also changed the query to be a parameterized query.

    This works fine. I am still not sure why my first approach did not work. Why isn't the query string built up every time by re-evaluating the paramter?

    Can you suggest a better way to achieve this?

    Config now looks like this:
    Code:
    	<beans:bean id="updatePriceReader" class="batch.jobs.prices.UpdatePriceReader" scope="step">
    		<beans:property name="dataSource" ref="dataSource"/>
    		<beans:property name="sql" value="select ID, INSTRUCTIONID, PROCESSED, REFID FROM PRICEINSTRUCTION WHERE PROCESSED = 0 AND REFID= ?"/>
    		<beans:property name="rowMapper">
    			<beans:bean class="batch.jobs.prices.PriceInstructionMapper"/>
    		</beans:property>
    	</beans:bean>
    JdbcCursorItemReader
    Code:
    public class UpdatePriceReader extends JdbcCursorItemReader {
    
        @BeforeStep
        public void setStepExecution(StepExecution stepExecution) {
    	UpdatePriceParameterSetter paramsSetter = new UpdatePriceParameterSetter();
    	paramsSetter.setParams(stepExecution.getJobParameters());
    	super.setPreparedStatementSetter(paramsSetter);
        }
    	
    }
    PreparedStatementSetter
    Code:
    public class UpdatePriceParameterSetter implements PreparedStatementSetter {
    
    	private JobParameters params;
    	
    	public JobParameters getParams() {
    		return params;
    	}
    
    	public void setParams(JobParameters params) {
    		this.params = params;
    	}
    
    	public void setValues(PreparedStatement stmnt) throws SQLException {
    		stmnt.setString(1, params.getString("refId"));
    	}
    
    }

    Comment


    • #3
      I'm facing a very similar problem. I'm pretty sure I've been able to determine that the cause is the late binding. In your first example, you were using it as well:

      '#{jobParameters[refId]}

      I think the 2nd time around, it's not binding to the updated parameter, and so it's running the same sql twice.

      Is this a bug in 2.0.3?

      Comment


      • #4
        I also think that this might be a bug. The documentation suggests that late binding can be used in this way, but it does not work.

        Comment


        • #5
          Yes, there is a bug up to 2.0.3.RELEASE, After upgrade to 2.1.0.M1 it will be work - check it

          Problem is BATCH-1420, placeholder as a part of string

          HTH

          Comment


          • #6
            Dave provided a workaround in BATCH-1420 for those who are still using 2.0.3.

            "just pull the inner bean definition (the preparedStatement Setter in your example) with the <list> of placeholders out into a top-level step-scoped bean with an id."

            I verified and seems to work for me

            Comment

            Working...
            X