将小数字段的空值插入 MS SQL Server 时，会引发 BatchUpdateException

Question

我们使用标准的Spring Batch JdbcBatchItemWriter将数据插入MS SQL Server。要插入的数据（来自项目处理器的输出）包含在一个Map<String,String>中，该保存列名和值。

当映射包含定义为十进制的列的空值（表示为空字符串 "）时（更具体地说，（十进制 （19,5），null），将引发以下异常：

java.sql.BatchUpdateException: Error converting data type nvarchar to numeric

不是十进制的数字字段工作正常。

驱动程序是：net.sourceforge.jtds.jdbc.Driver

以下 dll 用于连接到 SQL Server：ntlmauth.dll

Spring 批处理项目编写器配置类似于以下内容：

<bean id="itemWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
   <property name="dataSource" ref="..." />
   <property name="sql" value="${...}" />
   <property name="itemPreparedStatementSetter">
      <bean class="org.springframework.batch.item.database.support.ColumnMapItemPreparedStatementSetter" />
   </property>
   <property name="itemSqlParameterSourceProvider">
            <bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
   </property>
</bean>

以下是完整的堆栈跟踪：

]; Error converting data type nvarchar to numeric.; nested exception is java.sql.BatchUpdateException: Error converting data type nvarchar to numeric.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:303)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:298)
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:175)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:274)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:141)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
    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:151)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:130)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.BatchUpdateException: Error converting data type nvarchar to numeric.
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:944)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)

任何帮助将不胜感激。

Answer 1

我没有

使用过 Spring 框架，但我可以说，在从字符串转换为数字类型时，空字符串不等于 NULL。 只需尝试以下两个查询：

SELECT CONVERT(INT, '');
SELECT CONVERT(DECIMAL(19, 5), '');

第一个（即 CONVERT(INT) ） 返回 0，而不是 NULL。 第二个失败，并出现您看到的相同错误。 您需要以某种方式更改映射以传入可以等于 NULL 的值。如果可以提交表达式而不是文本，则可能可以执行以下操作：

CASE WHEN '{my value}' = '' THEN NULL ELSE '{my value}' END