JdbcCursorItemReader reads nothing the second time

[divren]

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

status:COMPLETED
commitcount:40
readcount:39
writecount:39

StepExecution after second run

status:COMPLETED
commitcount:1
readcount:0
writecount:0

Here is the job configuration.

	<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.

	<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>

[divren]

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:

	<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

public class UpdatePriceReader extends JdbcCursorItemReader {

    @BeforeStep
    public void setStepExecution(StepExecution stepExecution) {
	UpdatePriceParameterSetter paramsSetter = new UpdatePriceParameterSetter();
	paramsSetter.setParams(stepExecution.getJobParameters());
	super.setPreparedStatementSetter(paramsSetter);
    }
	
}

PreparedStatementSetter

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"));
	}

}

[CameronL]

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?

[divren]

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.

[Jul]

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

[CameronL]

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