Spring Batch Database Dependency pass parameters to SQL statement

Question

I want separate the sql from the batch.xml file, so I defined the sql statement into a properties file. Inside the batch.xml I bind the property-placeholder bean then point to the properties file.

For simple select statement should not be a problem. But if I want to pass the parameter as where clause condition is it possible to do that?

<context:property-placeholder
    location="classpath:batch-sql.properties/>

<bean id="secondReader"
    class="org.springframework.batch.item.database.JdbcCursorItemReader"
    scope="step">
    <property name="dataSource" ref="dataSource" />
    <property name="sql" value="${sql1}" />
    <property name="rowMapper">
        <bean class="com.test.batchjob.process.TestPersonMapper" />
    </property>
</bean>

This is my sql statment in properties file:

SELECT * FROM Person WHERE id = ?

Can the id pass from jobparameter?

Answer 1

To set the parameters of the query in a JdbcPagingItemReader, you have to use the property parametersValue. This property takes a Map<String,Object> where the key is either the named parameter or the index of the parameter (if you use ?).

<bean id="secondReader"
    class="org.springframework.batch.item.database.JdbcPagingItemReader"
    scope="step">
    <property name="queryProvider">
          <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="selectClause" value="select *" />
            <property name="fromClause" value="from persons" />
            <property name="whereClause" value="where id = ?" />
        </bean>
    </property>
    <property name="parametersValue">
        <map>
            <entry key="1" value="#{jobParameters['id']}" />
        </map>
    </property>
    <property name="rowMapper">
        <bean class="com.test.batchjob.process.TestPersonMapper" />
    </property>
</bean>

See documentation : JdbcPagingItemReader

UPDATE

You have to use a QueryProvider instead of sql and datasource properties. You can replace the text of the query by values of the properties file.