Date operations in HQL

by

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

More Related Contents:

  1. How do you create a Distinct query in HQL
  2. Performing Date/Time Math In HQL?
  3. How to use the Java 8 LocalDateTime with JPA and Hibernate
  4. how to return Map with HQL
  5. New object with HQL
  6. How to simulate NVL in HQL
  7. How to do bulk (multi row) inserts with JpaRepository?
  8. How to generate Custom Id using hibernate while it must be primary key of table
  9. hibernate: LazyInitializationException: could not initialize proxy
  10. Hibernate criteria: Joining table without a mapped association
  11. How can you call custom database functions with Hibernate?
  12. Why do I need to configure the SQL dialect of a data source?
  13. JPA 2.0 : Exception to use javax.validation.* package in JPA 2.0
  14. How to import initial data to database with Hibernate?
  15. JPQL Constructor Expression – org.hibernate.hql.ast.QuerySyntaxException:Table is not mapped
  16. What is referencedColumnName used for in JPA?
  17. Hibernate hbm2ddl.auto=update doesn’t update column definitions in MySQL
  18. Spring Data JDBC / Spring Data JPA vs Hibernate
  19. Hibernate HQL Query : How to set a Collection as a named parameter of a Query?
  20. Hibernate annotation or XML configuration
  21. JPA Uppercase table names
  22. How to escape reserved words in Hibernate’s HQL
  23. org.hibernate.PersistentObjectException: detached entity passed to persist
  24. Configure Jackson to omit lazy-loading attributes in Spring Boot
  25. IN-clause in HQL or Java Persistence Query Language
  26. inverse=true in JPA annotations
  27. hibernate connection pool
  28. HQL: Is it possible to perform an INNER JOIN on a subquery?
  29. No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode HQL
  30. Hibernate without Primary Key

Leave a Comment