使用 Hibernate 避免使用 Oracle 进行从日期到时间戳的隐式转换

Avoid implicit conversion from date to timestamp for selects with Oracle using Hibernate

提问by otto.poellath

I'm using Hibernate 3.2.7.GA criteria queries to select rows from an Oracle Enterprise Edition 10.2.0.4.0 database, filtering by a timestamp field. The field in question is of type java.util.Datein Java, and DATEin Oracle.

我正在使用 Hibernate 3.2.7.GA 条件查询从 Oracle Enterprise Edition 10.2.0.4.0 数据库中选择行，并按时间戳字段进行过滤。有问题的字段在java.util.DateJava 和DATEOracle 中属于类型。

It turns outthat the field gets mapped to java.sql.Timestamp, and Oracle converts all rows to TIMESTAMPbefore comparing to the passed in value, bypassing the indexand thereby ruining performance.

事实证明，该字段被映射到java.sql.Timestamp，并且 OracleTIMESTAMP在与传入的值进行比较之前将所有行转换为，绕过索引从而破坏了性能。

One solution would be to use Hibernate's sqlRestriction()along with Oracle's TO_DATEfunction. That would fix performance, but requires rewriting the application code (lots of queries).

一种解决方案是将 HibernatesqlRestriction()与 Oracle 的TO_DATE功能一起使用。这将修复性能，但需要重写应用程序代码（大量查询）。

So is there a more elegant solution? Since Hibernate already does type mapping, could it be configured to do the right thing?

那么有没有更优雅的解决方案呢？由于 Hibernate 已经进行了类型映射，是否可以对其进行配置以执行正确的操作？

Update:The problem occurs in a variety of configurations, but here's one specific example:

更新：该问题出现在多种配置中，但这是一个具体示例：

• Oracle Enterprise Edition 10.2.0.4.0
• Oracle JDBC Driver 11.1.0.7.0
• Hibernate 3.2.7.GA
• Hibernate's Oracle10gDialect
• Java 1.6.0_16

• Oracle 企业版 10.2.0.4.0
• Oracle JDBC 驱动程序 11.1.0.7.0
• 休眠 3.2.7.GA
• Hibernate 的 Oracle10gDialect
• Java 1.6.0_16

回答by dpbradley

This might sound drastic, but when faced with this problem we ended up converting all DATE columns to TIMESTAMP types in the database. There's no drawback to this that I can see, and if Hibernate is your primary application platform then you'll save yourself future aggravation.

这听起来可能很激烈，但是当遇到这个问题时，我们最终将数据库中的所有 DATE 列转换为 TIMESTAMP 类型。我可以看到这没有任何缺点，如果 Hibernate 是您的主要应用程序平台，那么您将避免未来的恶化。

Notes:

笔记：

• The column types may be changed with a simple "ALTER tableName MODIFY columnName TIMESTAMP(precisionVal)".

• I was surprised to find that indexes on these columns did NOT have to be
  rebuilt.

• 可以使用简单的“ALTER tableName MODIFY columnName TIMESTAMP(precisionVal)”更改列类型。

• 我惊讶地发现这些列上的索引不必
  重建。

Again, this only makes sense if you're committed to Hibernate.

同样，这只有在您致力于 Hibernate 时才有意义。

回答by Kamal

According to Oracle JDBC FAQ:

根据Oracle JDBC 常见问题解答：

"11.1 drivers by default convert SQL DATE to Timestamp when reading from the database"

“从数据库读取时，11.1 驱动程序默认将 SQL DATE 转换为时间戳”

So this is an expected behaviour. To me this means that actual values coming from DATEcolumns are converted to java.sql.Timestamp, not that bind variables with java.util.Dateare converted to java.sql.Timestamp.

所以这是一个预期的行为。对我来说，这意味着来自DATE列的实际值被转换为java.sql.Timestamp，而不是绑定变量java.util.Date被转换为java.sql.Timestamp。

An EXPLAIN PLANoutput would help identifying the issue. Also, an Oracle trace could tell you exactly what type is assigned to the bind variable in the query.

一个EXPLAIN PLAN输出将有助于识别问题。此外，Oracle 跟踪可以准确地告诉您分配给查询中的绑定变量的类型。

If that's really happening it could be a Oracle bug.

如果这真的发生了，那可能是 Oracle 的错误。

You can work around it this way:

您可以通过以下方式解决此问题：

• Create an FBI (Function Based Index) on the DATEcolumn, casting it to a TIMESTAMP. For example:

  CREATE INDEX tab_idx ON tab (CAST(date_col AS TIMESTAMP)) COMPUTE STATISTICS;
  

• Create a View that contains the same CASTexpression. You can keep the same column name if you want:

  CREATE VIEW v AS
  SELECT CAST(date_col AS TIMESTAMP) AS date_col, col_1, ... FROM tab;
  

• Use the View instead of the Table (it's often a good idea anyway, e.g. if you were already using a View, you wouldn't need to change the code at all). When a java.sql.Timestampvariable will be used with date_colin the WHEREcondition, (if enough selective) the Index will be used.

• If you find out why there was a java.sql.Timestamp(or Oracle fixes the potential bug), you can always go back just changing the View (and dropping the FBI), and it would be completely transparent to the code

• 在列上创建一个 FBI（基于函数的索引）DATE，将其转换为TIMESTAMP. 例如：

  CREATE INDEX tab_idx ON tab (CAST(date_col AS TIMESTAMP)) COMPUTE STATISTICS;
  

• 创建一个包含相同CAST表达式的视图。如果需要，您可以保留相同的列名：

  CREATE VIEW v AS
  SELECT CAST(date_col AS TIMESTAMP) AS date_col, col_1, ... FROM tab;
  

• 使用视图而不是表（无论如何，这通常是一个好主意，例如，如果您已经在使用视图，则根本不需要更改代码）。当在条件中java.sql.Timestamp使用变量时，（如果有足够的选择性）将使用索引。date_colWHERE

• 如果你发现为什么有一个java.sql.Timestamp（或 Oracle 修复了潜在的错误），你可以随时返回，只需更改视图（并删除 FBI），这对代码来说是完全透明的