How to fix Named Query Error ( unexpected token: DAY ) in Hibernate?

Question

Following is my named query for finding activities older then 90 Days:

"DELETE FROM PAActivity pa WHERE pa.status IN (:statusSet) AND cast(pa.datoFinish as date) < cast(DATE_SUB(current_date(), INTERVAL (:noofdays) DAY) as date)"

During compile time it gives following Hibernate exception:

ERROR 2018-04-24 11:00:07,579 o.h.h.i.a.ErrorCounter - line 1:243: unexpected token: DAY
ERROR 2018-04-24 11:00:07,579 o.h.h.i.a.ErrorCounter - line 1:243: unexpected token: DAY
line 1:243: unexpected token: DAY
    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:876)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3472)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3250) .
.
.
.
.
WARN  2018-04-24 11:00:07,641 o.h.h.i.a.HqlParser - HHH000203: processEqualityExpression() : No expression to process!
ERROR 2018-04-24 11:00:08,094 o.h.i.SessionFactoryImpl - HHH000177: Error in named query: deletePAActivityByProcessIDs
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: DAY near line 1, column 243 [DELETE FROM fn.gover.model.PAActivity pa WHERE pa.status IN (:statusSet) AND cast(pa.datoFinish as date) < cast(DATE_SUB(current_date(), INTERVAL (:noofdays) DAY) as date)]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)

This might just be helpful here: https://stackoverflow.com/q/10223929/7953365 — Ankur, Apr 24 '18 at 07:30

Danila Zharenkov · Accepted Answer · 2018-04-24T15:20:32.227

I think the problem is that HQL doesn't support INTERVAL

You can solve you problem in 2 ways. First - RestrictionCriteria

Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_YEAR, -90); 
Criteria criteria = session.createCriteria(Yourclass.class);
criteria.add(Restrictions.ge("datoFinish", c.getTime());
List results = criteria.list();

Or just change your query like :

AND cast(pa.datoFinish as date) < cast(:dateMinus90 as date)

And set parameter as result of Calendar

.setParameter("dateMinus90", c.getTime())

The seccond way is to inherit dialect class and register sql function. For example for Oracle.

public class MyOracleDialect extends Oracle10gDialect {
    @Override
    protected void registerFunctions() {
        super.registerFunctions();
        registerFunction("sub_days", new SQLFunctionTemplate(StandardBasicTypes.DATE, " date_sub(?1, INTERVAL ?2 DAY)"));
    } }

And then in hibernate props you should use this dialect. But I think it is not the best way.

How to fix Named Query Error ( unexpected token: DAY ) in Hibernate?

1 Answers1