How to implement mysql date_sub() function in Hibernate 3.0

Question

Is it possible in hiberanate to write query like this?

SELECT * FROM `tablename` where created_at> DATE_SUB(curdate(),INTERVAL 7 DAY) 

Answer 1

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();