在Oracle数据库中使用JdbcTemplate插入时间戳(ORA-01858) [英] Insert timestamp with JdbcTemplate in Oracle database ( ORA-01858 )

问题描述

我已经阅读了很多有关此错误的内容，但仍然没有发现该错误.

I've read a lot of stuff about this error, and still not found the mistake.

我正在使用JdbcTemplate在带有某些时间戳列的某些表中插入一行 我很确定时间戳是问题所在，就像从插入中删除一样可以正常工作

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)

我的代码:

        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')" ;
        }

还有下一个错误:

    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

我试图在一些成功的SQL编辑器中执行sql，所以我不会感到困惑.

I've tried to execute the sql in some SQL editor having success, so I can't be more confused.

成为我的参数:[461，100，TO_TIMESTAMP('2015-01-28 00:00:01'，'yyyy-mm-dd hh24:mi:ss')，1，8888]实际上有效. /p>

Being my params: [461, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1, 8888] This actually works.

    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!

推荐答案

不要在日期/时间戳记和字符串之间来回转换.

Don't convert back and forth between dates/timestamps and Strings.

只需将java.sql.Timestamp实例作为参数传递:

Just pass a java.sql.Timestamp instance as a parameter:

Object[] params = {
         idCita,
         citaQuenda.getIdServicio(),
         new java.sql.Timestamp(citaQuenda.getFechaCita()),
         ESTADO_INICIAL,
         USUARIO_INICIAL };

String queryCitas = INSERT_CITAS;
super.getJdbcTemplate().update(queryCitas, params);

这篇关于在Oracle数据库中使用JdbcTemplate插入时间戳(ORA-01858)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！