Get input query from batch parameters

Question

I need to retrive dynamically a parameter from the batch arguments and set it to my query. Here is my implementation :

<bean id="Reader" scope="step"
    class="org.springframework.batch.item.database.JpaPagingItemReader">
    <property name="entityManagerFactory"
        ref="entityManagerFactory" />

    <property name="queryString"
        value="SELECT distinct d FROM ReglementClient r 
                   JOIN FETCH d.etablissement e 
                   WHERE e.code = #{jobParameters[code]}
                   And r.statut in('Validé')
                " />

</bean>

I run my batch usine the following argument : -Dcode=882.

I had the following error :

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: And near line 1, column 697 [SELECT distinct d FROM com.natixis.smartcontestation.emetteur.entite.ReglementClient r JOIN FETCH d.etablissement e And r.statut in('Validé') And e.code =  ]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)

actually it seems to work, but the HQL is wrong, your SQL for the queryString differs from the one in the stacktrace, specifically "JOIN FETCH d.etablissement e And r.statut in('Validé') And e.code = " seems to be the problem, afaik you can not use AND directly after JOIN FETCH, it should be WHERE or WITH — Michael Pralow
– Michael Pralow, Commented Jun 30, 2016 at 12:59
My query is : SELECT distinct d FROM ReglementClient r JOIN FETCH d.etablissement e WHERE r.statut in('Validé') And e.code = #{jobParameters[code]}. I think the problem is due to the fact that it could not retrieve the #{jobParameters[code]}. I run the batch with the argument 'code' using -Dcode=882 — Nabil Salah
– Nabil Salah, Commented Jun 30, 2016 at 13:07
That is invalid JPQL (it is not SQL!). Please look in a decent JPQL reference ... about "IN" keyword and WHERE clauses. There is no "#" symbol in JPQL! — Neil Stockton
– Neil Stockton, Commented Jun 30, 2016 at 13:08
So how can I retrieve dynamically e.code, as I explained thios value is available in my batch inputs — Nabil Salah
– Nabil Salah, Commented Jun 30, 2016 at 13:12
Nobody can tell you how since you don't post your ENTITY class so they don't know what e.code is, or r.statut. Maybe this "#{jobParameters[code]}" would be a simple ":myParam" ... but then maybe not since it isn't defined — Neil Stockton
– Neil Stockton, Commented Jul 1, 2016 at 7:41

Andreas · Accepted Answer · 2016-07-01 07:37:39Z

2

You need to specify normal parameter syntax in the jpql, use the parameterValues property on the reader to set the jobParameter

<bean id="Reader" scope="step"
      class="org.springframework.batch.item.database.JpaPagingItemReader">
    <property name="entityManagerFactory"
              ref="entityManagerFactory" />

    <property name="queryString"
              value="SELECT distinct d FROM ReglementClient r 
               JOIN FETCH d.etablissement e 
               WHERE e.code = :code
               And r.statut in('Validé')
            " />

    <property name="parameterValues">
        <map>
            <entry key="code" value="#{jobParameters[code]}"/>
        </map>
    </property>

</bean>

edited Jul 1, 2016 at 7:37

answered Jul 1, 2016 at 7:28

Andreas

1281 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Michael Pralow Over a year ago

see stackoverflow.com/a/34900569/62201, it should work with the jobParameter inside the SQL

Andreas Over a year ago

Hmm ok, might be an issue with xml decoding then, not using CDATA

Nabil Salah Over a year ago

Thanks for your answer. Unfortently I had this error : Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanExpressionException: Expression parsing failed; nested exception is org.springframework.expression.spel.SpelEvaluationException: EL1008E:(pos 0): Field or property 'jobParameters' cannot be found on object of type 'org.springframework.beans.factory.config.BeanExpressionContext'

Andreas Over a year ago

do you access the writer before/out of step scope?

Andreas Over a year ago

or perhaps step scope isn't initialized correctly docs.spring.io/spring-batch/reference/htmlsingle/#step-scope

|

Michael Pralow · Accepted Answer · 2016-06-30 13:10:50Z

0

if you use the standard spring batch mechanism to run jobs from the commandline, you need to follow the right pattern for the jobparameters

These arguments must be passed in with the path first and the name second. All arguments after these are considered to be JobParameters and must be in the format of 'name=value':

bash$ java CommandLineJobRunner endOfDayJob.xml endOfDay schedule.date(date)=2007/05/05

answered Jun 30, 2016 at 13:10

Michael Pralow

6,6302 gold badges32 silver badges48 bronze badges

Collectives™ on Stack Overflow

Get input query from batch parameters

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related