I had the same problem as you, so here is what I did to get parametrized queries:
I've derived JdbcCursorItemReader with a custom class that changes dynamically the SQL before the batch is opened.
In my need i've used the JobParameters for that, but you can you anything.
Here is the class, as an example:
/**
* An ItemReader that executes a Sql request with a dynamic select parametrized by batch parameters
* For example, sets the sql to "select * from contract where creationDate>':CREATION_DATE'"
* and then start the job with a job parameter named: SQL_PARAM_CREATION_DATE=2007/03/26
*
* @author Gerard COLLIN
*/
public class ParametrizedQueryItemReader extends JdbcCursorItemReader {
public static final String PARAMETER_SQL_PREFIX="SQL_PARAM_";
public ParametrizedQueryItemReader() {
}
/**
* Allows for sql modification before the query is run
*/
@Override
public void open() {
super.setSql(convertSql (srcSql));
super.open();
}
/**
* Change the sql before it gets executed
*
* @param src
* @return
*/
protected String convertSql (String src)
{
JobParameters parameters=StepSynchronizationManager.getContext() .getStepExecution().getJobExecution().getJobInstan ce().getJobParameters();
for (String key:(Set<String>)parameters.getParameters().keySet())
{
if (key.startsWith(PARAMETER_SQL_PREFIX))
{
String sqlKey=key.substring(PARAMETER_SQL_PREFIX.length() );
Object value=parameters.getParameters().get(key);
String sqlValue=convertToSqlParameter(value);
src = src.replace(":"+sqlKey, sqlValue);
}
}
return src;
}
/**
* Converts an arbitrary object to a sql suitable for select clause
*
* @param value
* @return
*/
protected String convertToSqlParameter(Object value) {
if( value instanceof Date)
{
return SimpleDateFormat.getDateInstance(SimpleDateFormat. SHORT, Locale.US).format((Date)value);
}else if (value instanceof Timestamp)
{
return SimpleDateFormat.getDateTimeInstance(SimpleDateFor mat.SHORT, SimpleDateFormat.SHORT, Locale.US).format((Timestamp)value);
} else if(value!=null)
{
return value.toString();
}
return "null";
}
/**
* Store the sql source without any changes in parameters
*/
protected String srcSql;
@Override
public void setSql(String sql) {
srcSql=sql;
super.setSql(sql);
}
}
And here is an example of the spring declaration:
Notice the :creationDate
<bean id="sqlContractReader" class="com.linedata.masteri.batch.builder.MasteriQueryIte mReader" scope="singleton">
<property name="sql">
<value>select CNT_CONTRAT, CNT_AVENANT, CNT_PRODUIT, CNT_DERNIER_AVT_O_N from CONTRAT where CNT_DATE_CREATION>=TO_DATE(':creationDate','DD/MM/YYYY')</value>
</property>
<property name="dataSource" ref="businessDB" />
<property name="fetchSize" value="1000"></property>
<property name="mapper" ref="contractMapper">
</property>
</bean>
With how the job is launched:
public void testGeneration () throws JobExecutionAlreadyRunningException
{
BuilderJobLauncher jobLauncher=getSpringBean("jobLauncher");
Job job=getSpringBean("extractContractJob");
// We create the job parameters with the creation date for the sql
JobParametersBuilder builder=new JobParametersBuilder();
builder.addString(ParametrizedQueryItemReader.PARA METER_SQL_PREFIX+"creationDate", "24/12/1990");
JobExecution status=jobLauncher.run( job, builder.toJobParameters());
System.out.println(status.getExitStatus().toString ());
}
Hope this helps.
Gerard COLLIN