Date Operations in HQL

Question

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

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

I cannot get how to do this using HQL. I DONT want to make additional requests, process the date in java code, and so on. This should be done at the HQL level.

Can you help me?

Answer 1

Depending on your database, this might be trivially simple. HQL supports built-in functions and manufacturer-specific functions, and also supports the possibility of expanding the dialect by registering new functions if they are not yet supported by HQL.

Say you are using SQLServer (or Sybase). SQLServer has a "DATEADD" function that can do what you like very easily. Format:

 DATEADD (datepart, number, date) 

You can use this function directly in HQL by first registering the function in your own Hibernate dialog. To do this, you just need to expand the dialect that you are currently using. This is a very simple process.

First create your own dialect class (replace "SQLServer2008Dialect" with your own database provider):

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

Then change the sleep 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 just use the function:

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

(It is assumed that your entity is called MyEntity, and the create_date field maps to a property called createDate).

Answer 2

HQL does not support arithmetic with date and time, so this is not possible without the HQL extension. The easiest way is probably to register the functions of the SQL dialog providers for this calculation. Another possibility is to implement and register an interceptor (the override method is onPrepareStatement ), if the syntax with plus and minus signs is preferred.

If the solution does not have to be pure in HQL, and the time from the JVM host is a fairly simple solution, it is to calculate the date, which is 10 minutes in the past, and give it an argument. If database time is preferred, this can be achieved by querying it in a separate query and then decreasing it by 10 minutes.