HQL中的日期操作 [英] Date operations in HQL

问题描述

我想使用HQL从表中获取过期的实体。这样做：

  select id，name from entity_table where date_creation + 10_minutes< current_time（）
  

我不能使用HQL来做到这一点。我DONT想做出额外的查询，在java代码中处理日期等等。这可以在HQL级别上完成。

你能帮我吗？

解决方案>

根据你的数据库，这可以简单得多。 HQL支持内置的供应商特定功能和功能，它还支持通过注册新功能（如果尚未被HQL支持）扩展方言的功能。

假设您正在使用SQLServer（或Sybase）。 SQLServer有一个名为'DATEADD'的功能，可以很容易地做你喜欢的事情。格式为：

  DATEADD（datepart，number，date）
  

您可以直接在HQL中使用此函数，方法是首先在自己的Hibernate方言中注册该函数。为此，您只需要扩展您当前正在使用的方言。这是一个非常简单的过程。

首先，创建自己的方言类（用自己的DB供应商替换SQLServer2008Dialect）：

  public class MySQLServerDialect extends SQLServer2008Dialect {
 
 public MySQLServerDialect（）{
 registerFunction（addminutes，new VarArgsSQLFunction（TimestampType.INSTANCE ，dateadd（minute，，，，）））; 
} 
 
} 
  

接下来，修改您的hibernate配置使用这个新类：

 <？xml version ='1.0'encoding ='utf-8'？> 
<！DOCTYPE hibernate-configuration PUBLIC 
 -  // Hibernate / Hibernate配置DTD 3.0 // EN
http://www.hibernate.org/dtd/hibernate-configuration -3.0.dtd> 
< hibernate-configuration> 
< session-factory> 
 ... 
< property name =hibernate.dialect> com.mycompany.MySQLServerDialect< / property> 
 ... 
< / hibernate-configuration> 
  

现在只需使用函数：

 从MyEntity x中选择x，其中addminutes（x.creationDate，10）< current_time（）
  

（这假设你的实体被称为MyEntity，并且creation_date字段被映射到一个名为creationDate）。

I want to get expired entities from table using HQL. Something like that:

select id, name from entity_table where date_creation + 10_minutes < current_time()

I cant get how to do that with HQL. I DONT want to make extra-queries, process date in java code an so on. This must be done on HQL level.

Can you please help me?

解决方案

Depending on your database, this can be trivially simple. HQL supports built-in vendor-specific features and functions, it also supports the ability to extend the dialect by registering new functions if they're not already supported by HQL.

Let's say you're using SQLServer (or Sybase). SQLServer has a function called 'DATEADD' that can do what you like very easily. The format is:

DATEADD (datepart, number, date)

You can use this function directly in HQL by first registering the function in your own Hibernate Dialect. To do this, you just have to extend the Dialect you're currently using. This is a very simple process.

First, create your own dialect class (replace 'SQLServer2008Dialect' with your own DB vendor):

public class MySQLServerDialect extends SQLServer2008Dialect {

  public MySQLServerDialect() {
    registerFunction("addminutes", new VarArgsSQLFunction(TimestampType.INSTANCE, "dateadd(minute,", ",", ")"));
  }

}

Next, modify your hibernate configuration to use this new class:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    ...
    <property name="hibernate.dialect">com.mycompany.MySQLServerDialect</property>
    ...
</hibernate-configuration>

Now simply use the function:

select x from MyEntity x where addminutes(x.creationDate, 10) < current_time()

(This assumes your entity is called MyEntity and the creation_date field is mapped to a property called creationDate).

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