Java - Oracle - DB Insert - Timestamp to Date 列

Question

我有一个数据类型为 DATE 的表列。我通过 Java PreparedStatement 插入，插入时如何存储时间戳？是否可以在 DATE 列中存储时间戳？

我试过了

ps.setTimestamp(index, new java.sql.Timestamp(date.getTime()));

但是没用。

提前致谢。

Answer 1

Oracle 中SQL 类型和JDBC 类型之间的映射有点复杂，尤其是DATE 和TIMESTAMP SQL 类型。

Oracle 数据库允许你在DATE 列中存储时间戳信息，这与SQL 标准中DATE 的定义完全相反。在引入 TIMESTAMP 列支持的 9.2 之前，Statement.setTimestamp 将适用于 DATE 列。从 9.2 到 11.1，JDBC 驱动程序将 SQL DATE 类型映射到 JDBC Date 并将 SQL TIMESTAMP 类型映射到 JDBC 时间戳。 Oracle JDBC FAQ 中列出了使用 10.2 JDBC 驱动程序存储时间戳的可能解决方案。 , 并转载于此:

There are several ways to address this problem in the 9.2 through 10.2 drivers:

• Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.

• Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it? ).

• Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.

• Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

  java -Doracle.jdbc.V8Compatible="true" MyApp

您还可以使用 JDBC 11.1 驱动程序(它们将适用于 10g 实例)，FAQ 中有这样的说明:

Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default. There is no need to set V8Compatible to get the correct mapping. V8Compatible is strongly deprecated. You should not use it at all. If you do set it to true it won't hurt anything, but you should stop using it.