Hibernate Oracle INTERVAL EXPRESSION和Oracle 11g方言 [英] Hibernate Oracle INTERVAL EXPRESSION and Oracle 11g Dialect

问题描述

我需要使用INTERVAL EXPRESSION来计算字段。如下所示：

  @Formula（SYSDATE  -  INTERVAL'1'HOUR * SHOW_LIMIT_HOURS）
 private showLimitDate; 
  

然而，hibernate将这个表达式看作一列，产生下面的SQL片段。

 和product0_.sold_at> （
 SYSDATE  -  settings0_.INTERVAL'1'settings0_.HOUR * setting0_.SHOW_LIMIT_HOURS 
）
  

有人可以帮助我吗？

解决方案

经过2天的痛苦，分析了hibernate源代码的AST处理，我终于放弃了！ = P ..事实上，目前还没有可用的Oracle 11g方言。

因此，我改变了策略并通过以下更改解决：

1。在Oracle数据库中创建跟随功能

$ b

  CREATE OR REPLACE FUNCTION INTERVAL_HOURS_AGO（HOURS_PARAM IN NUMBER）
 RETURN DATE DETERMINISTIC 
 IS TIME_AGO DATE; 
 BEGIN 
 SELECT（SYSDATE  -  INTERVAL'1'HOUR * HOURS_PARAM）INTO TIME_AGO FROM DUAL; 
 RETURN（TIME_AGO）; 
 END; 
  

功能上的DETERMINISTIC HINT对于避免使用Where-Clauses时的性能问题非常重要。有关链接的更多信息：
http://www.inside-oracle-apex.com/caution-when-using-plsql-functions-in-sql-statement/

的 2。创建自定义Oracle Dialect类并注册新函数。

  public class Oracle11gDialectExtended extends Oracle10gDialect {
 
 public Oracle11gDialectExtended（）{
 
 super（）; 
 
 registerFunction（interval_hours_ago，
新的StandardSQLFunction（INTERVAL_HOURS_AGO，StandardBasicTypes.DATE））; 
 
 
 
 
 
 
所以，只需在@公式： 
 
 
  @Formula（INTERVAL_HOURS_AGO（SHOW_LIMIT_HOURS））
私人日期showLimitDate; 
  
或在HQL / NamedQuery上： 
 
 
 从产品p 
中选择p where p.createdAt> interval_hours_ago（60）
  
 
I need to use a INTERVAL EXPRESSION for a calculated field. like below:
@Formula(" SYSDATE - INTERVAL '1' HOUR * SHOW_LIMIT_HOURS ")
private Date showLimitDate;
However, hibernate produces the following SQL fragment considering this expression like a column.
and product0_.sold_at > (
                SYSDATE - settings0_.INTERVAL '1' settings0_.HOUR * setting0_.SHOW_LIMIT_HOURS
            )
Anybody could help me ? 
解决方案
After 2 days suffering, analysing AST processing of hibernate source code i finally gave up !! =P .. In fact there isnt a Oracle 11g Dialect available yet.


So , I changed the strategy and solve it with following changes :

1. Create the follow function on Oracle database
CREATE OR REPLACE FUNCTION INTERVAL_HOURS_AGO(HOURS_PARAM IN NUMBER) 
   RETURN DATE DETERMINISTIC
   IS TIME_AGO DATE;
   BEGIN 
      SELECT (SYSDATE - INTERVAL '1' HOUR * HOURS_PARAM) INTO TIME_AGO FROM DUAL;
      RETURN(TIME_AGO); 
    END;
DETERMINISTIC HINT on function is very important for avoid performance issue when use it on Where-Clauses. More infos about it on link:
http://www.inside-oracle-apex.com/caution-when-using-plsql-functions-in-sql-statement/

2. Create a Custom Oracle Dialect Class And Register the new Function.
public class Oracle11gDialectExtended extends Oracle10gDialect {

    public Oracle11gDialectExtended() {

        super();

       registerFunction("interval_hours_ago", 
           new StandardSQLFunction("INTERVAL_HOURS_AGO", StandardBasicTypes.DATE));

    }
}
So, just call it on @Formula :
@Formula(" INTERVAL_HOURS_AGO( SHOW_LIMIT_HOURS ) ")
private Date showLimitDate;
Or on HQL / NamedQuery :
select p from Product p 
  where p.createdAt > interval_hours_ago(60)


                        
这篇关于Hibernate Oracle INTERVAL EXPRESSION和Oracle 11g方言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！