Java – jdbcbatchitemwriter – syntax error near keyword “on”
•
Java
I am running the following query to insert records from my spring application into my SQL Server database using the jdbcbatchitemwriter
"INSERT INTO USER (username,address) VALUES (:username,:address) ON DUPLICATE KEY UPDATE username = username"
But I get the following exceptions
org.springframework.jdbc.BadsqlGrammarException: PreparedStatementCallback; bad sql grammar [{my_query_above}]; nested exception is java.sql.sqlException: Incorrect Syntax near the keyword 'ON'.
The query runs normally without on condition Do you know why it failed?
UPDATE
This is the write configuration
@Bean
public ItemWriter<User> userWriter(DataSource dataSource) throws sqlException {
JdbcBatchItemWriter<User> userWriter = new JdbcBatchItemWriter<User>();
userWriter.setItemsqlParameterSourceProvider(new BeanPropertyItemsqlParameterSourceProvider<StagingProcess>());
userWriter.setsql(QueryUtil.QUERY_INSERT_PROCESS);
userWriter.setDataSource(sqlServerDataSource());
return userWriter;
}
@Primary
@Bean(name="sqlserverDS")
public static DataSource sqlServerDataSource() throws sqlException {
final DriverManagerDataSource sqlServerDataSource = new DriverManagerDataSource(); sqlServerDataSource.setDriverClassName("net.sourceforge.jtds.jdbc.Driver");
sqlServerDataSource.setUrl("jdbc:jtds:sqlserver://myHost:8080/myDB");
sqlServerDataSource.setUsername("user");
sqlServerDataSource.setPassword("password");
return sqlServerDataSource;
}
More stack traces
org.springframework.jdbc.BadsqlGrammarException: PreparedStatementCallback; bad sql grammar [{my_query_above}]; nested exception is java.sql.sqlException: Incorrect Syntax near the keyword 'ON'.
at org.springframework.jdbc.support.sqlErrorCodesqlExceptionTranslator.doTranslate(sqlErrorCodesqlExceptionTranslator.java:231) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbacksqlExceptionTranslator.translate(AbstractFallbacksqlExceptionTranslator.java:73) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:335) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:181) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:274) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_101]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_101]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_101]
at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_101]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) [spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) [spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at com.sun.proxy.$Proxy75.run(UnkNown Source) [na:na]
Solution
SQL server does not support "on duplicate key update" View SQL Server recommended SQL does SQL Server offer anything like MySQL's on duplicate key update
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
二维码