休眠和日期比较 [英] Hibernate and date comparisons

问题描述

假设我有一个到期日和提醒时间间隔。我如何找到那些到期日期少于当前日期+提醒与Hibernate 3.6条件查询？换句话说，我想找到我的活动我已经显示提醒。

总而言之，我的实体如下：

  java.util.Date Event.DueDate 
 Long Event.Type.Reminder.Before //（以天或毫米为单位）
  

示例

 今天是2012-06-11。 
包括：
 DueDate是2012-06-15，之前是30天。 
排除：
 DueDate为2012-06-15，Before为1天。 
  

解决方案

最终这只是ANSI SQL调用日期/时间算术，具体来说，您正在查找 INTERVAL 数据类型处理。不幸的是，数据库在支持INTERVAL数据类型上差异很大。我真的想支持这在HQL（和可能的标准，虽然这依赖于协议在JPA规范委员会）。像我说的，困难是对间隔的各种（如果有的话）支持。

现在最好的方法是通过Hibernate 4.1提供方言（））注册的自定义函数（ org.hibernate.dialect.function.SQLFunction 搜索google查看这是如何完成的）或自定义函数注册表（ org.hibernate.cfg.Configuration＃addSqlFunction ）。

这里是一个示例，它使用Oracle NUMTODSINTERVAL 函数：

  MySqlFunction实现SQLFunction 
 {
 public Type getReturnType（type firstArgumentType，
映射映射）throws QueryException 
 {
 return TimestampType.INSTANCE; 
} 
 
 public String render（type firstArgumentType，
 List arguments，
 SessionFactoryImplementor factory）throws QueryException 
 {
 //参数已经解释为sql变体... 
 final String dueDateArg = arguments.get（0）; 
 final String beforeArg = arguments.get（1）; 
 
 //再次使用Oracle特定的NUMTODSINTERVAL 
 //函数并使用days作为单位... 
 return dueDateArg ++ numtodsinterval（+ beforeArg + ， '天'）; 
} 
 
 public boolean hasArguments（）{return true; } 
 public boolean hasParenthesesIfNoArguments（）{return false; } 
} 
  

您将在HQL中使用此方法：

选择...
来自事件e
其中e.dueDate和
之间的current_date（）interval_date_calc（e.dueDate ，e.before）


其中interval_date_calc是注册SQLFunction的名称。 p>

Let's say I have a due date and a reminder timespan. How do I find the ones where due date is less than current date + reminder with Hibernate 3.6 criteria queries? In other words, I want to find my Events I've displayed the reminder. The reminder is a Long marking when the reminder should be sent either days or milliseconds, whichever is easier.

To summarize, my entities are following:

java.util.Date Event.DueDate
Long Event.Type.Reminder.Before // (in days or millis)

Examples

Today is 2012-06-11.
Included:
  DueDate is 2012-06-15 and Before is 30 days.
Excluded:
  DueDate is 2012-06-15 and Before is 1 day.

解决方案

Ultimately this is just what ANSI SQL calls date/time arithmetic and specifically you are looking for INTERVAL datatype handling. Unfortunately, databases vary widely on support for INTERVAL datatype. I really want to support this in HQL (and possibly criterias, although that relies on agreement in the JPA spec committee). The difficulty, like I said, is the varied (if any) support for intervals.

The best bet at the moment (through Hibernate 4.1) is to provide a custom function (org.hibernate.dialect.function.SQLFunction) registered with either the Dialect (search google to see how this is done) or the "custom function registry" (org.hibernate.cfg.Configuration#addSqlFunction). You'd probably want this to render to your database-specific representation of date-arith with an interval.

Here is an example using the Oracle NUMTODSINTERVAL function:

public class MySqlFunction implements SQLFunction
{
    public Type getReturnType(Type firstArgumentType,
                              Mapping mapping) throws QueryException
    {
        return TimestampType.INSTANCE;
    }

    public String render(Type firstArgumentType,
                         List arguments, 
                         SessionFactoryImplementor factory) throws QueryException
    {
        // Arguments are already interpreted into sql variants...
        final String dueDateArg = arguments.get( 0 );
        final String beforeArg = arguments.get( 1 );

        // Again, using the Oracle-specific NUMTODSINTERVAL
        // function and using days as the unit...
        return dueDateArg + " + numtodsinterval(" + beforeArg + ", 'day')";
    }

    public boolean hasArguments() { return true; }
    public boolean hasParenthesesIfNoArguments() { return false; }
}

You would use this in HQL like:

select ...
from   Event e
where  current_date() between e.dueDate and
       interval_date_calc( e.dueDate, e.before )

where 'interval_date_calc' is the name under which you registered your SQLFunction.

这篇关于休眠和日期比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！