4

Is it possible in hiberanate to write query like this?

SELECT * FROM `tablename` where created_at> DATE_SUB(curdate(),INTERVAL 7 DAY) 
Nimantha
  • 6,405
  • 6
  • 28
  • 69
anoop
  • 1,604
  • 6
  • 24
  • 50

3 Answers3

9

You can either use a native SQL query

String sql = "SELECT * FROM tablename WHERE created_at > DATE_SUB(curdate(), INTERVAL 7 DAY)"
Query query = session.createSQLQuery(sql);
List result = query.list();

Or you can use Hibernate Criteria Restrictions.sqlRestriction

String sqlWhere = "{alias}.created_at > DATE_SUB(curdate(), INTERVAL 7 DAY)";
Criteria criteria = session.createCriteria(MyEntity.class);
criteria.add(Restrictions.sqlRestriction(sqlWhere));
List result = criteria.list();
Nimantha
  • 6,405
  • 6
  • 28
  • 69
tscho
  • 2,024
  • 15
  • 15
4

You can also extend hibernate's mysql Dialect to support date_add function.

public class CommonMySQL5InnoDBDialect extends MySQL5InnoDBDialect {
    public CommonMySQL5InnoDBDialect() {
        super();
        //mysql date_add function support.
        registerFunction( "date_add_interval", new SQLFunctionTemplate( DateType.INSTANCE, "date_add(?1, INTERVAL ?2 ?3)" ) );
    }
}

Then you can use the registered function in you HQL.

from tablename where created_at > date_add_interval(curdate(), 7  DAY)
cpedia
  • 41
  • 1
  • 1
    This worked for me, with a minor change in the HQL: from tablename where created_at > date_add_interval(curdate(), 7, DAY) – RonyK Jan 11 '18 at 17:02
0
String sql1 = "select  uh.ServiceDate from tablename  uh WHERE uh.ServiceDate >= DATE_SUB(curdate(), INTERVAL 1 YEAR)";

Query query1 = session.createSQLQuery(sql1);

 List result = query1.list();

         use this list in HQL

String queryString = "delete  from tablename  u  where  u.serviceDate in (:result)";
query =session.createQuery(queryString);
query.setParameterList("result", result);