JdbcCursorItemReader - Setting PreparedStatementSetter

[K. Arnold]

Hello --

I am learning how to utilize the JdbcCursorItemReader within a Job. I started with the Spring Batch sample and created a working job that will read from a source of data and write to another. I have created a pretty standard ItemReader bean that includes the sql. I am in the process of turning my where statement to parameters. However, since my parameters are going to be set dynamically I am not sure where in my code I can obtain a reference to this reader before the step is called and set the parameters.

I'm exploring this out using unit tests. So I have a job configured and I am calling the job via AbstractBatchLauncherTests that was provided in the Spring Batch samples.

<bean id="groupEligibilitySource" 
		  class="org.springframework.batch.item.database.JdbcCursorItemReader">
		<property name="dataSource" ref="dataSource" />
		<property name="rowMapper">
			<bean class="com.primetherapeutics.benplanmgr.entity.rxclaim.dao.GroupEligibilityRowMapper" />
		</property>
		<property name="sql">
			<value>
					SELECT DISTINCT 
		  			PLN.ADAECD AS planCode,
		  			PLN.ADAKDT AS planEffDate,
		  			PLN.ADA1DT AS planTermDate,
					PLN.ADBOTX AS planName,	
					PLN.ADCSST AS planPreferredFormulary,
					PLN.ADCTST AS planDefaultDrugStatus,	
					GEL.G3AACD AS carCarrierID, 
					GEL.G3ACCD AS accAccountID,
		  			GEL.G3ADCD AS grpGroupID, 
		  			GEL.G3C0N1 AS gelSeqNbr, 
		  			GEL.G3BWS2 AS gelStatus,
		  			GEL.G3CMDA AS gelEffDate, 
		  			GEL.G3CNDA AS gelThruDate, 
		  			GEL.G3L3PR AS gelCopayBrand,
		  			GEL.G3L4PR AS gelCopayGeneric, 
		  			GEL.G3L5PR AS gelCopay3, 
		  			GEL.G3L6PR AS gelCopay4,
		  			GEL.G3GQP4 AS gelCopay5, 
		  			GEL.G3GRP4 AS gelCopay6, 
		  			GEL.G3GSP4 AS gelCopay7,
		  			GEL.G3GTP4 AS gelCopay8, 
		  			GEL.G3PGC2 AS gelClientBenefitCode, 
		  			GEL.G3C2DT AS addDate, 
		  			GEL.G3BMDT AS chgDate, 
		  			GEL.G3ABTM AS chgTime
		  		FROM CLMPRDFIL.RCGELP AS GEL 
		  			JOIN CLMPRDFIL.RCPLNP AS PLN ON PLN.ADAECD = GEL.G3AECD 
		  			AND PLN.ADAKDT = GEL.G3AKDT 
		  		WHERE GEL.G3CNDA &gt;= ?
		  			AND GEL.G3BWS2 = 'A'
		  			AND GEL.G3C2DT &lt;= ?
		  			AND GEL.G3BMDT &lt;= ?
					AND PLN.ADA1DT &gt;= ?
		  		ORDER BY CARCARRIERID, ACCACCOUNTID, GRPGROUPID, PLANCODE, PLANEFFDATE
			</value>
		</property>
	</bean>

[Dave Syer]

Did you see this: http://static.springsource.org/sprin...#late-binding? Is that what you need?

[K. Arnold]

I ended up extending the JdbcCursorItemReader and utilizing the @BeforeStep to set my PreparedStatementSetter

[JayJay]

Instead of creating your own reader, why not just implement PreparedStatementSetter and set it as property for JdbcCursorItemReader?

[jaenswart]

Another option is to use something like this:

<beans:bean id="sqlItemReader"
		class="org.springframework.batch.item.database.JdbcCursorItemReader">
		<beans:property name="dataSource" ref="dataSource" />
		<beans:property name="sql">
			<beans:value>
				<![CDATA[				
				SELECT DISTINCT 
		  			PLN.ADAECD AS planCode,
		  			PLN.ADAKDT AS planEffDate,
		  			PLN.ADA1DT AS planTermDate,
					PLN.ADBOTX AS planName,	
					PLN.ADCSST AS planPreferredFormulary,
					PLN.ADCTST AS planDefaultDrugStatus,	
					GEL.G3AACD AS carCarrierID, 
					GEL.G3ACCD AS accAccountID,
		  			GEL.G3ADCD AS grpGroupID, 
		  			GEL.G3C0N1 AS gelSeqNbr, 
		  			GEL.G3BWS2 AS gelStatus,
		  			GEL.G3CMDA AS gelEffDate, 
		  			GEL.G3CNDA AS gelThruDate, 
		  			GEL.G3L3PR AS gelCopayBrand,
		  			GEL.G3L4PR AS gelCopayGeneric, 
		  			GEL.G3L5PR AS gelCopay3, 
		  			GEL.G3L6PR AS gelCopay4,
		  			GEL.G3GQP4 AS gelCopay5, 
		  			GEL.G3GRP4 AS gelCopay6, 
		  			GEL.G3GSP4 AS gelCopay7,
		  			GEL.G3GTP4 AS gelCopay8, 
		  			GEL.G3PGC2 AS gelClientBenefitCode, 
		  			GEL.G3C2DT AS addDate, 
		  			GEL.G3BMDT AS chgDate, 
		  			GEL.G3ABTM AS chgTime
		  		FROM CLMPRDFIL.RCGELP AS GEL 
		  			JOIN CLMPRDFIL.RCPLNP AS PLN ON  
                                               PLN.ADAECD = GEL.G3AECD 
		  			AND PLN.ADAKDT = GEL.G3AKDT 
		  		WHERE GEL.G3CNDA &gt;= ?
		  			AND GEL.G3BWS2 = 'A'
		  			AND GEL.G3C2DT &lt;= ?
		  			AND GEL.G3BMDT &lt;= ?
					AND PLN.ADA1DT &gt;= ?
		  		ORDER BY CARCARRIERID, ACCACCOUNTID,              
                                              GRPGROUPID, PLANCODE, PLANEFFDATE
			
					]]>
			</beans:value>

		</beans:property>
		<beans:property name="rowMapper" >
			<beans:bean
				class="com.yourcompany.CustomRowMapper" />
		</beans:property>
		<beans:property name="preparedStatementSetter" ref="statementSetter">
		</beans:property>
	</beans:bean>
	
	<beans:bean id="statementSetter"
		class="org.springframework.batch.core.resource.StepExecutionPreparedStatementSetter">
		<beans:property name="parameterKeys">
			<beans:list>
				<beans:value>parameterKey1</beans:value>
                                <beans:value>parameterKey2</beans:value>
                                <beans:value>parameterKey3</beans:value>
                                <beans:value>parameterKey4</beans:value>
			</beans:list>
		</beans:property>
	</beans:bean>

[K. Arnold]

I wanted a way to dynamically assign my preparedsetter. So I needed the setter in code and not in the xml file.