无法使用Spring的JdbcTemplate获得插入DB中的最后一个序列ID
无法使用Spring的JdbcTemplate获得插入DB中的最后一个序列ID
提问于 2017-06-20 18:31:17
我试图在Oracle DB中插入一条记录，并获取序列id (这也是该表的主键)以供进一步使用。我搜索了这些链接，找到了一些代码片段，并一直在尝试它们，但是在Oracle DB中插入记录时，我看到了下面的错误。
误差
Exception in thread "main" org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]
at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:73)
at com.ebayenterprise.publicapi.events.dao.DBConnectionUtils.insertEventLogData2(DBConnectionUtils.java:88)
at com.ebayenterprise.publicapi.events.dao.DBConnectionUtils.main(DBConnectionUtils.java:42)程序方法
private static void insertEventLogData2() {
//setup data
EventType eventType = new EventType(EVENT_TYPE_ID, EVENT_TYPE_CD, null, null);
Timestamp currentTimestamp = new Timestamp(System.currentTimeMillis());
final EventLog eventLog = new EventLog(eventType, ROUTE_ID, ORDER_ID, null, null, currentTimestamp, currentTimestamp);
System.out.println("eventLog = " + eventLog);
//execute query
GeneratedKeyHolder holder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement statement = con.prepareStatement(INSERT_EVENT_LOG_MESSAGE_SQL, Statement.RETURN_GENERATED_KEYS);
statement.setInt(1, eventLog.getRouteId());
statement.setInt(2, eventLog.getEventType().getEventTypeId());
statement.setString(3, eventLog.getOrderId());
statement.setTimestamp(4, eventLog.getIncomingEventTimestamp());
statement.setTimestamp(5, eventLog.getOutgoingEventTimestamp());
return statement;
}
}, holder);
//get the sequence key
long generatedEventId = holder.getKey().longValue();
System.out.println("generatedEventId = " + generatedEventId);
}表桌面：
EVENT_ID (Primary Key - Mapped to sequence EVENT_ID_SEQ)
ROUTE_ID
EVENT_TYPE_ID
ORDER_ID
INCOMING_EVENT_TIMESTAMP
OUTGOING_EVENT_TIMESTAMP回答 1
Stack Overflow用户
发布于 2017-06-20 18:54:16
不要紧。让它通过NamedParameterJdbcTemplate运行。实现也要干净得多。
private static final String INSERT_EVENT_LOG_MESSAGE_SQL2 = "INSERT INTO "
+ "EVENT_LOG (EVENT_ID, ROUTE_ID, EVENT_TYPE_ID, ORDER_ID, INCOMING_EVENT_TIMESTAMP, OUTGOING_EVENT_TIMESTAMP) "
+ "VALUES (EVENT_LOG_ID_SEQ.NEXTVAL, :routeId, :eventTypeId, :orderId, :incomingEventTS, :outgoingEventTS)";
private static void insertEventLogData() {
//setup data
EventType eventType = new EventType(EVENT_TYPE_ID, EVENT_TYPE_CD, null, null);
Timestamp currentTimestamp = new Timestamp(System.currentTimeMillis());
EventLog eventLog = new EventLog(eventType, ROUTE_ID, ORDER_ID, null, null, currentTimestamp, currentTimestamp);
System.out.println("eventLog = " + eventLog);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("routeId", ROUTE_ID);
parameters.addValue("eventTypeId", EVENT_TYPE_ID);
parameters.addValue("orderId", ORDER_ID);
parameters.addValue("incomingEventTS", currentTimestamp);
parameters.addValue("outgoingEventTS", currentTimestamp);
GeneratedKeyHolder holder = new GeneratedKeyHolder();
int nb = namedJdbcTemplate.update(INSERT_EVENT_LOG_MESSAGE_SQL2, parameters, holder, new String[]{"EVENT_ID"});
//get the sequence key
long generatedEventId = holder.getKey().longValue();
System.out.println("generatedEventId = " + generatedEventId);
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44660591
复制相关文章
相似问题
腾讯云开发者
