我正在尝试使用org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate批量更新表。namedParameterJdbcTemplate.batchUpdate(query…(方法；我的查询包含在子句中，因此我得到如下SQL错误：
nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10824][4.14.113] Invalid data conversion: Parameter instance [1271, 1272] is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815.
以下是我想要批量更新的数据。它是contractId(String(的映射作为键，值作为taskid列表(Integer(数据：
{10008=[1271, 1272], 10007=[1269, 1270], 10009=[1273, 1274], 10011=[1277, 1278], 10010=[1275, 1276], 10012=[1279], 10006=[1267, 1268]}
where key is the CONTRACT_ID (String) and value is list of TASK_ID ( Integer)
e.g. CONTRACT_ID are 10008, 10007, 10009 etc.
and respective TASK_ID are [1271, 1272], [1269, 1270], [1273, 1274] etc.
我尝试在循环中调用namedParameterJdbcTemplate.update，它运行良好，我可以使用单一更新更新相同的查询
单一更新
@Override
public int assignUserTask(String customerId, String userId, Map<String, List<Integer>> resultMap)
throws ApplicationException {
int totalCount = 0;
String ts = DateUtils.getCurrentDatetimeStamp();
String todaysDate = DateUtils.getTodaysDate();
String query = CommonUtils.buildQuery("UPDATE EM_WF_USER_QUEUE SET", "CURRENT_USER_ID = :currentUserId,",
"INITIAL_USER_ID = :initialUserId,", "USER_REASSIGN_TYPE_CD = 'GW',",
"USER_REASSIGN_TIME = :userReassignTime,", "LAST_CENTRAL_QUEUE_DATE = :lastCentralQueueData,",
"REASSIGN_CENTRAL_DATE = :reassignCentralDate,", "ORG_GW_CRT_TIME = :orgGwCreateTime,",
"LAST_UPDT_USER = :lastUpdtUser ,", "LAST_UPDT_TIME = :lastUpdtTime",
"WHERE CONTRACT_ID = :contractId AND TASK_ID IN (:taskIds)",
"AND CUSTOMER_ID = :customerId AND CURRENT_USER_ID = 'UNASSIGNED'",
"AND OVERIDE_IND = 'N' AND QUEUE_STATUS != 'CLOSED'");
try {
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("currentUserId", userId);
params.addValue("initialUserId", userId);
params.addValue("userReassignTime", ts);
params.addValue("lastCentralQueueData", todaysDate);
params.addValue("reassignCentralDate", todaysDate);
params.addValue("orgGwCreateTime", ts);
params.addValue("lastUpdtUser", userId);
params.addValue("lastUpdtTime", ts);
params.addValue("customerId", customerId);
for (Entry<String, List<Integer>> entry : resultMap.entrySet()) {
params.addValue("contractId", entry.getKey());
params.addValue("taskIds", entry.getValue());
totalCount += namedParameterJdbcTemplate.update(query, params);
}
return totalCount;
} catch (DataAccessException exp) {
throw new CustomException(exp, "Exception occured!");
}
}
批处理更新
@Overridepublic int assignUserTask(字符串customerId，字符串userId，Map>resultMap(throws CustomException｛
String ts = DateUtils.getCurrentDatetimeStamp();
String todaysDate = DateUtils.getTodaysDate();
String query = CommonUtils.buildQuery("UPDATE EM_WF_USER_TASK SET", "CURRENT_USER_ID = :currentUserId,",
"INITIAL_USER_ID = :initialUserId,", "USER_REASSIGN_TIME = :userReassignTime,",
"LAST_CENTRAL_QUEUE_DATE = :lastCentralQueueData,", "REASSIGN_CENTRAL_DATE = :reassignCentralDate,",
"ORG_GW_CRT_TIME = :orgGwCreateTime,", "LAST_UPDT_USER = :lastUpdtUser ,",
"LAST_UPDT_TIME = :lastUpdtTime", "WHERE CONTRACT_ID = :contractId AND TASK_ID IN (:taskIds)",
"AND CUSTOMER_ID = :customerId AND CURRENT_USER_ID = 'UNASSIGNED'",
"AND ACTIVE_IND = 'Y' AND TASK_STATUS != 'CLOSED'");
try {
List<MapSqlParameterSource> batchArgs = new ArrayList<>();
for (Entry<String, List<Integer>> entry : resultMap.entrySet()) {
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("currentUserId", userId);
params.addValue("initialUserId", userId);
params.addValue("userReassignTime", ts);
params.addValue("lastCentralQueueData", todaysDate);
params.addValue("reassignCentralDate", todaysDate);
params.addValue("orgGwCreateTime", ts);
params.addValue("lastUpdtUser", userId);
params.addValue("lastUpdtTime", ts);
params.addValue("contractId", entry.getKey());
params.addValue("taskIds", entry.getValue());
params.addValue("customerId", customerId);
batchArgs.add(params);
}
int[] retVal = namedParameterJdbcTemplate.batchUpdate(query,
batchArgs.toArray(new MapSqlParameterSource[resultMap.size()]));
return retVal.length;
} catch (DataAccessException exp) {
throw new CustomException(exp, "Exception occured!");
}
}
当我使用namedParameterJdbcTemplate.batchUpdate(query..)时，我希望查询在批处理中运行但它给出了类型转换错误。
由以下原因引起：org.springframework.jdbc.BadSqlGrammar异常：PreparedStatementCallback；错误的SQL语法[更新EM_WF_USER_TASK集CURRENT_USER_ID=？，INITIAL_USER_ID=？，用户分配类型='GW'，USER_REASSIGN_TIME=？，LAST_CENTRAL_QUEUE_DATE=？，重新分配_中心日期=？，ORG_GW_CRT_TIME=？，LAST_UPDT_USER=，LAST_UPDT_TIME=？其中CONTRACT_ID=？和TASK_ID在(？，？(和CUSTOMER_ID=？CURRENT_USER_ID="未分配"和ACTIVE_IND='Y'和TASK_STATUS！='关闭']；嵌套异常为com.ibm.db2.jcc.am.SqlSyntaxErrorException：[jcc][1091][10824][4.14.113]无效的数据转换：参数实例[12711272]对于请求的转换无效。错误代码=-4461，SQLSTATE=42815网址：org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93(网址：org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLServerExceptionTranslater.java:72(网址：org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLServerExceptionTranslater.java:81(位于org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java：1402(网址：org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620(网址：org.springframework.jdbc.core.JdbcTemplate.execure(JdbcTemplate.java:634(网址：org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:924(位于org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParametryBatchUpdateUtils.java:43(位于org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:357(网址：com.medicare.mss.daoImpl.ContractDAO.assignUserTask(ContractDAO.java:274(…还有164引起原因：com.ibm.db2.jcc.am.SqlSyntaxErrorException：[jcc][1091][1081][4.14.113]无效的数据转换：参数实例[12711272]对于请求的转换无效。错误代码=-4461，SQLSTATE=42815网址：com.ibm.db2.jcc.am.ed.a(ed.java：677(网址：com.ibm.db2.jcc.am.ed.a(ed.java：60(网址：com.ibm.db2.jcc.am.ed.a(ed.java：103(在com.ibm.db2.jcc.am.po.c(po.java：2630(在com.ibm.db2.jcc.am.po.setObject(po.java:2409(网址：com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariPoxyPreparedStatement.java(位于org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:411(位于org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232(位于org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163(位于org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:70(位于org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access$000(NamedParameterBatch UpdateUtils.java:33(位于org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatch UpdateUtils.java:50(位于org.springframework.jdbc.core.JdbcTemplateLambda$batchUpdate$2(JdbcTemplate.java:932(网址：org.springframework.jdbc.core.JdbcTemplate.execure(JdbcTemplate.java:605(…还有169com.medicare.mss.exception.ApplicationException：分配用户任务时出错！网址：com.medicare.mss.daoImpl.ContractDAO.assignUserTask(ContractDAO.java:279(网址：com.medicare.mss.daoImpl.ContractDAO$$FastClassBySpringCGLIB$$30e66146.invoke((。。。网址：org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61(在java.lang.Thread.run(Thread.java:745(
我发现问题出在batchUpdate查询中的IN子句上。请查看下面的链接。希望这能有所帮助：https://github.com/spring-projects/spring-framework/issues/21935