Announcement Announcement Module
Collapse
No announcement yet.
JdbcCursorItemReader - Setting PreparedStatementSetter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcCursorItemReader - Setting PreparedStatementSetter

    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.



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

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

    Comment


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

      Comment


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

        Comment


        • #5
          Another option is to use something like this:

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

          Comment


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

            Comment

            Working...
            X