更新ORACLE数据库DATE类型的字段时报错

Question

@JFinal 你好，想跟你请教个问题：

    Jfinal通过Druid操作Oracle数据库，更新日期类型(DATE类型)的字段失败以下是代码

Test test= Test.dao.findById(1); 

test.set("TDATE","to_date('2015-10-26 17:30:00','yyyy-mm-dd hh24:mi:ss')" ).update();

以下是报错信息：

2015-10-26 17:48:37
Sql: select * from TEST where ID = ?
Sql: update TEST set TDATE = ?  where ID = ?

com.jfinal.plugin.activerecord.ActiveRecordException: java.sql.SQLDataException: ORA-01858: 在要求输入数字处找到非数字字符

at com.jfinal.plugin.activerecord.Model.update(Model.java:491)
at com.netvour.appstore.community.model.VersionInfoTest.testUpdateDate(VersionInfoTest.java:48)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:78)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:212)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:68)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: java.sql.SQLDataException: ORA-01858: 在要求输入数字处找到非数字字符


at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:253)
at com.jfinal.plugin.activerecord.DbPro.update(DbPro.java:260)
at com.jfinal.plugin.activerecord.Db.update(Db.java:194)
at com.jfinal.plugin.activerecord.Model.update(Model.java:484)
... 29 more

Answer 1

终于找到解决方法了，要这样向Model中设值才行用Model的Update方法 Timestamp stamp = Timestamp.valueOf("2015-10-27 12:11:31"); Test test=Test.dao.findById(1); test.set("TDATE",stamp).update();

Answer 2

     test.set("TDATE","to_date('2015-10-26 17:30:00','yyyy-mm-dd hh24:mi:ss')") 是向 test 这个 model 之中放入名称为 "TDATE" 的属性值，而 "to_date('2015-10-26 17:30:00','yyyy-mm-dd hh24:mi:ss')" 这个值只会被当成普通的 String 值，数据表需要的是一个 DATE 类型。

    所以，数据表要的是一个 DATE 类型，而传入的是一个 String 型，必然会出错，如果要使用 to_date(...)这样的函数，需要在 sql 中使用，这样用： Db.update(sql);