PreparedStatement and JdbcCursorInputSource

[sandrine]

Hi,

I would like to make a select with a clause WHERE parameterizable. The date in the clause WHERE will be knew at Runtime.
The JdbcCursorInputSource (m3 snapshot) uses a Statement instead of PreparedStatement.

Have you any solution with the actual JdbcCursorInputSource?
Or do you plane to change JdbcCursorInputSource to use PreparedStatement?

Thanks in advance.

[lucasward]

This is already being tracked as an issue:

http://jira.springframework.org/browse/BATCH-377

I don't believe it will get fixed for release 1 though.

[gcollin]

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().getJobInstance().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(SimpleDateFormat.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.MasteriQueryItemReader" 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.PARAMETER_SQL_PREFIX+"creationDate", "24/12/1990");
		JobExecution status=jobLauncher.run( job, builder.toJobParameters());
		
        System.out.println(status.getExitStatus().toString());

	}

Hope this helps.

Gerard COLLIN

[sandrine]

I have found an other issue : use java -Dvariable=value to call my BatchMain and in my job.xml I have added the following code:

<bean class="org.springframework.beans.factory.config.Pr opertyPlaceholderConfigurer">
<property name="ignoreUnresolvablePlaceholders" value="false" />
</bean>
...
<bean id="DBInputTemplate" class="org.springframework.batch.io.cursor.JdbcCur sorInputSource">
<property name="dataSource" ref="dataSource" />
<property name="fetchSize" value="5000"/>
<property name="sql">
<value>
SELECT XXX
FROM YYY
WHERE COL1 = '${variable}'
</value>
</property>
<property name="mapper">
<bean class="package.Mapper" />
</property>
</bean>