在 Oracle 数据库中使用 JdbcTemplate 插入时间戳 (ORA-01858)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28198153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert timestamp with JdbcTemplate in Oracle database ( ORA-01858 )
提问by elcadro
I've read a lot of stuff about this error, and still not found the mistake.
我已经阅读了很多关于此错误的内容，但仍然没有发现错误。
I'm using JdbcTemplate to insert a row in some table with some timestamp column I'm pretty sure the timestamp is the problem, as if delete from the insert it works fine)
我正在使用 JdbcTemplate 在某些带有时间戳列的表中插入一行我很确定时间戳是问题所在，就好像从插入中删除它工作正常一样）
My code:
我的代码：
private static final String INSERT_CITAS = "INSERT INTO CITAS ("
+ "idCita, idServicio, " + "fechaCita, "
+ "idEstado, idUsuarioInicial) " + "VALUES (?, ?, ?, ?, ?)";
Object[] params = {
idCita,
citaQuenda.getIdServicio(),
getDateToDBFormat(citaQuenda.getFechaCita()),
ESTADO_INICIAL,
USUARIO_INICIAL };
String queryCitas = INSERT_CITAS;
super.getJdbcTemplate().update(queryCitas, params);
protected String getDateToDBFormat(Date fechaCreacion){
return "TO_TIMESTAMP('" +
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(fechaCreacion)
+ "', 'yyyy-mm-dd hh24:mi:ss')" ;
}
And having the next error:
并出现下一个错误：
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO citas_55 (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (?, ?, ?, ?, ?)];
ORA-01858: a non-numeric character was found where a numeric was expected
I've tried to execute the sql in some SQL editor having success, so I can't be more confused.
我试过在一些 SQL 编辑器中执行 sql 成功，所以我不能再困惑了。
Being my params: [461, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1, 8888] This actually works.
作为我的参数： [461, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1, 8888] 这确实有效。
INSERT INTO citas (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (457, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss') , 1, 8888);
Any kind of help would be appreciated. Thanks in advance!
任何形式的帮助将不胜感激。提前致谢！
回答by a_horse_with_no_name
Don't convert back and forth between dates/timestamps and Strings.
不要在日期/时间戳和字符串之间来回转换。
Just pass a java.sql.Timestampinstance as a parameter:
只需传递一个java.sql.Timestamp实例作为参数：
Object[] params = {
idCita,
citaQuenda.getIdServicio(),
new java.sql.Timestamp(citaQuenda.getFechaCita()),
ESTADO_INICIAL,
USUARIO_INICIAL };
String queryCitas = INSERT_CITAS;
super.getJdbcTemplate().update(queryCitas, params);
回答by OldProgrammer
I will go out on a limb here, and think I may see the problem. getDateToDBFormat() method is returning a String type, which contains the text, "TO_TIMESTAMP(...)". That is not a date or timestamp parameter. It is a string parameter. You need to do this instead:
我会在这里冒险，并认为我可能会看到问题。getDateToDBFormat() 方法返回一个字符串类型，其中包含文本“TO_TIMESTAMP(...)”。这不是日期或时间戳参数。它是一个字符串参数。你需要这样做：
Remove the TO_TIMESTAMP stuff from getDateToDBFormat() and have it just return the formatted DATE/TIME value (the format you show is not an oracle timestamp, but a DATE type).
change your insert to:
"INSERT INTO CITAS ... VALUES (?, ?, TO_DATE(?,?) , ?, ?)"
从 getDateToDBFormat() 中删除 TO_TIMESTAMP 内容，让它只返回格式化的 DATE/TIME 值（您显示的格式不是 oracle 时间戳，而是 DATE 类型）。
将您的插入更改为：
"INSERT INTO CITAS ... VALUES (?, ?, TO_DATE(?,?) , ?, ?)"
Where the parameters to the TO_DATE call are the return from getDateToDBFormat() and the second parameter is the date format mask. However, can't you just get rid of that mess and bind a Java Date type (or jdbc sql equivalent) directly?
其中 TO_DATE 调用的参数是 getDateToDBFormat() 的返回值，第二个参数是日期格式掩码。但是，您不能摆脱这种混乱并直接绑定 Java Date 类型（或 jdbc sql 等效类型）吗？
That should work.
那应该有效。
