Is it possible in hiberanate to write query like this?
SELECT * FROM `tablename` where created_at> DATE_SUB(curdate(),INTERVAL 7 DAY)
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();
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)
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);