Late binding fails for property name

[Shriblue]

I'm trying to append the job parameter to refer to a property name in a property file.

The property -

job1LoadSql=select * from tab

config-

<beans:bean id="cleanLoadTableTasklet" class="com.dcinv.eod.batch.framework.QueryExecutingTasklet" scope="step">
    <beans:property name="dataSource" ref="dataSource"/>
    <beans:property name="sql" value="#{jobParameters[fileName]}LoadSql" />
</beans:bean>

where job parameter fileName is passed in as job1.

QueryExecutingTasklet gets the sql and does

this.simpleJdbcTemplate.update( sql, params );

The exception thrown is attached below. Please let me know what I'm doing incorrectly here.

Or is there is a way to late-bind the property file name like

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <beans:property name="location" value="#{jobParameters[fileName]}.properties">
</bean>

?

The exception -

4:45:52,826 [main] ERROR AbstractStep - Encountered an error executing the step
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [job1LoadSql]; nested exception is java.sql.SQLException: Could not find stored procedure 'job1LoadSql'.
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:522)
	at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:237)
	at com.dcinv.eod.batch.framework.QueryExecutingTasklet.execute(QueryExecutingTasklet.java:45)
	at com.dcinv.eod.batch.framework.QueryExecutingTasklet$$FastClassByCGLIB$$319af90b.invoke(<generated>)
	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
	at com.dcinv.eod.batch.framework.QueryExecutingTasklet$$EnhancerByCGLIB$$b7f1b9c7.execute(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy11.execute(Unknown Source)
	at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:268)
	at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:76)
	at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:367)
	at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
	at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
	at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:242)
	at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:198)
	at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:348)
	at org.springframework.batch.core.job.flow.FlowJob.access$100(FlowJob.java:43)
	at org.springframework.batch.core.job.flow.FlowJob$JobFlowExecutor.executeStep(FlowJob.java:135)
	at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
	at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144)
	at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124)
	at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:103)
	at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:250)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:110)
	at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:105)
	at batch.jobs.bloomberg.BloombergJobTest.testBloombergJob(BloombergJobTest.java:38)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.test.context.junit4.SpringTestMethod.invoke(SpringTestMethod.java:160)
	at org.springframework.test.context.junit4.SpringMethodRoadie.runTestMethod(SpringMethodRoadie.java:233)
	at org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:333)
	at org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)
	at org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)
	at org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:160)
	at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
	at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
	at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
	at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
	at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:97)
	at com.intellij.rt.junit4.Junit4TestMethodAdapter.run(Junit4TestMethodAdapter.java:62)
	at junit.textui.TestRunner.doRun(TestRunner.java:116)
	at com.intellij.rt.execution.junit.IdeaTestRunner.doRun(IdeaTestRunner.java:94)
	at junit.textui.TestRunner.doRun(TestRunner.java:109)
	at com.intellij.rt.execution.junit.IdeaTestRunner.startRunnerWithArgs(IdeaTestRunner.java:22)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:118)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Could not find stored procedure 'equity_namr_cleanLoadSql'.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
	at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:512)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
	... 72 more

[Shriblue]

I'm using Spring batch 2.0.4 and Spring 2.5.6

[Jul]

It looks like you are passing property key into sql query, are you sure you have any logic behind sql property?
Just open correct properties file, and get value for specified key first
HTH

[Shriblue]

It looks like you are passing property key into sql query, are you sure you have any logic behind sql property?
Just open correct properties file, and get value for specified key first

The sql query is present in the property file as mentioned in my first post, and does get loaded if I directly refer to the property name instead of trying to late bind the job param like this

<beans:bean id="cleanLoadTableTasklet" class="com.dcinv.eod.batch.framework.QueryExecutingTasklet" scope="step">
<beans:property name="dataSource" ref="dataSource"/>
<beans:property name="sql" value="job1LoadSql" />
    </beans:bean>

Only when I want to pass the value of "job1" from the job parameter, its not picking it up.

[Jul]

Could you post the code of QueryExecutingTasklet class?

[Shriblue]

public class QueryExecutingTasklet implements Tasklet, InitializingBean
{
    private static Logger logger = Logger.getLogger(QueryExecutingTasklet.class);
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private String sql;
    private Object[] params;

    /**
     * can be used to call a SQL statement, i.e. truncate table, update/insert/delete
     *
     * @param contribution
     * @param chunkContext
     * @return
     * @throws Exception
     */
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception
    {
        this.simpleJdbcTemplate.update( sql, params );
        return RepeatStatus.FINISHED;
    }

    public void afterPropertiesSet() throws Exception
    {
        Assert.notNull(sql, "sql must be set");
    }

    public void setDataSource(DataSource dataSource)
    {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public String getSql()
    {
        return sql;
    }

    public void setSql(String sql)
    {
        this.sql = sql;
    }

    public Object[] getParams()
    {
        return params;
    }

    public void setParams(Object[] params)
    {
        this.params = params;
    }
}

[Shriblue]

Sorry, correction to the post above, the snippet below works.

<beans:bean id="cleanLoadTableTasklet" class="com.dcinv.eod.batch.framework.QueryExecutingTasklet" scope="step">
<beans:property name="dataSource" ref="dataSource"/>
<beans:property name="sql" value="${job1LoadSql}" />
    </beans:bean>

[Jul]

Check it:

package test;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.batch.core.StepContribution;
import org.springframework.batch.core.scope.context.ChunkContext;
import org.springframework.batch.core.step.tasklet.Tasklet;
import org.springframework.batch.repeat.RepeatStatus;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.core.io.support.PropertiesLoaderSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.util.Assert;

public class QueryExecutingTasklet extends PropertiesLoaderSupport implements Tasklet, InitializingBean{
	private static Logger logger = Logger.getLogger(QueryExecutingTasklet.class);
	private SimpleJdbcTemplate simpleJdbcTemplate;
	private String sql;
	private String sqlQuery = "queryIsEmptyNow";
	private Object[] params;
	
	/**
	 * can be used to call a SQL statement, i.e. truncate table, update/insert/delete
	 *
	 * @param contribution
	 * @param chunkContext
	 * @return
	 * @throws Exception
	 */
	public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception
	{
	    this.simpleJdbcTemplate.update( sqlQuery, params );
	    return RepeatStatus.FINISHED;
	}
	
	public void afterPropertiesSet() throws Exception
	{
	    Assert.notNull(sql, "sql must be set");
	    
	    sqlQuery = mergeProperties().getProperty(sql);
	}
	
	public void setDataSource(DataSource dataSource)
	{
	    this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}
	
	public String getSql()
	{
	    return sql;
	}
	
	public void setSql(String sql)
	{
	    this.sql = sql;
	}
	
	public Object[] getParams()
	{
	    return params;
	}
	
	public void setParams(Object[] params)
	{
	    this.params = params;
	}

}

<beans:bean id="queryExecutionTasklet" scope="step"
			class="test.QueryExecutingTasklet">
	<beans:property name="dataSource" ref="dataSource"/>
	<beans:property name="sql" value="someSQL"/>
	<beans:property name="location" value="classpath:test/queries.properties"/>
</beans:bean>

file queries.properties:

someSQL=select * from SOME_TABLE

Now you can parametrize sql property by Late Binding. It should works.

Is that something you are looking for?

[Shriblue]

This works, thanks a lot.

The tasklet is used in around 5 or more steps, hence it ends up loading the same properties files for each step. This seems necessary because of the late binding.

Now if there is a way to late bind a property file name once at the job level or when application context is loaded, then that would be great since I needn't load properties every time at the step level.

Something like below?

<beans:property name="location" value="classpath:batch/jobs/#{jobParameter[fileName]}.properties">

[Jul]

Yes. It should works that way.