0

I have developed a Spring Batch portioned example by taking a reference from : http://www.mkyong.com/spring-batch/spring-batch-partitioning-example/.

In this example I have configured the bean like below :

<bean id="pagingItemReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step">
        <property name="dataSource" ref="dataSource" />
        <property name="pageSize" value="10" />
        <property name="queryProvider">
            <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
                <property name="dataSource" ref="dataSource" />
                <property name="selectClause" value="SELECT customerNumber, checkNumber,paymentDate,amount" />
                <property name="fromClause" value="FROM payments" />
                <property name="whereClause" value="WHERE customerNumber &apos;&gt;&apos;= :fromId AND customerNumber &apos;&lt;&apos;= :toId" />
                <property name="sortKey" value="customerNumber" />
            </bean>
        </property>
        <!-- Inject via the ExecutionContext in rangePartitioner -->
        <property name="parameterValues">
            <map>
                <entry key="fromId" value="stepExecutionContext[fromId]" />
                <entry key="toId" value="stepExecutionContext[toId]" />
            </map>
        </property>
        <property name="rowMapper">
            <bean class="com.prateek.mapper.PaymentsRowMapper" />
        </property>
    </bean>

when I launch the main method I get the below error. Could you please guide me how to fixed this ?

2018-07-16 01:02:35 DEBUG o.s.b.repeat.support.RepeatTemplate - Handling fatal exception explicitly (rethrowing first of 1): org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT customerNumber, checkNumber,paymentDate,amount FROM payments WHERE customerNumber '>'= ? AND customerNumber '<'= ? ORDER BY customerNumber ASC LIMIT 10]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''>'= 'stepExecutionContext[fromId]' AND customerNumber '<'= 'stepExecutionContex' at line 1
2018-07-16 01:02:35 DEBUG o.s.jdbc.core.JdbcTemplate - SQL update affected 1 rows
2018-07-16 01:02:35 DEBUG o.s.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
2018-07-16 01:02:35 ERROR o.s.batch.core.step.AbstractStep - Encountered an error executing step slave in job paymentsPartitionJob
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT customerNumber, checkNumber,paymentDate,amount FROM payments WHERE customerNumber '>'= ? AND customerNumber '<'= ? ORDER BY customerNumber ASC LIMIT 10]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''>'= 'stepExecutionContext[fromId]' AND customerNumber '<'= 'stepExecutionContex' at line 1
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1400)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:618)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:655)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:734)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:206)
    at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:201)
    at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108)
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:89)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:338)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy7.read(Unknown Source)
    at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91)
    at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:157)
    at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:116)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145)
    at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:110)
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)
    at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:139)
    at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:136)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''>'= 'stepExecutionContext[fromId]' AND customerNumber '<'= 'stepExecutionContex' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:664)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    ... 42 common frames omitted

pom.xml

<properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring-batch-vesion>4.0.1.RELEASE</spring-batch-vesion>
        <mysql.version>8.0.11</mysql.version>
        <logback.version>1.2.3</logback.version>
        <jcl.slf4j.version>1.7.25</jcl.slf4j.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-core</artifactId>
            <version>${spring-batch-vesion}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-infrastructure</artifactId>
            <version>${spring-batch-vesion}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <!-- Logging -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>${jcl.slf4j.version}</version>
        </dependency>

        <!-- 3. logback -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

Added Screen shot

enter image description here

Jeff Cook
  • 7,956
  • 36
  • 115
  • 186
  • Looks like this is the same issue as in https://stackoverflow.com/questions/51355574/jdbctemplate-sqlwarning-ignored-sql-state-22007-error-code-1292-message . See my answer there. – Mahmoud Ben Hassine Jul 16 '18 at 08:03
  • @MahmoudBenHassine - If you closely look at the issue, then its different than what you're saying. Added screen shot above for more clarity. – Jeff Cook Jul 16 '18 at 15:13
  • `<`is a special XML character. Use `<` instead. – JB Nizet Jul 16 '18 at 16:02
  • That will at least make your XML file valid. Start by doing that. I have no idea about the rest. But if the XML is invalid in the first place, nothing should work. – JB Nizet Jul 16 '18 at 16:07
  • @@JB Nizet - If you take a look at my code snippet, I already used that. Do you want to refactor that string ? – Jeff Cook Jul 16 '18 at 16:10

2 Answers2

0

You can use a nested value element to specify the whereClause query:

<property name="whereClause">
    <value>
        WHERE customerNumber >= :fromId AND customerNumber <= :toId
    </value>
</property>
Mahmoud Ben Hassine
  • 28,519
  • 3
  • 32
  • 50
0

The below snippet works fine. @Mahmoud Ben Hassine - Thanks for such a great guidance. Looking forward to follow you.

<property name="whereClause">
        <value>
            <![CDATA[   WHERE customerNumber >= :fromId AND customerNumber <= :toId ]]>
        </value>
</property>
Jeff Cook
  • 7,956
  • 36
  • 115
  • 186