Date operations in HQL

Question

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?

Answer 1

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).