MySQL DATE_ADD throws Hibernate error

Question

I am using HQL with DATE_ADD (mysql function) as below

String hql = 
        "select count(*) " +
        "from ProgramGroupEvent as eventLog " +
        "where eventLog.id= :id" +
        "and DATE_ADD( eventLog.eventDate, INTERVAL :interval MINUTE) > now()"; 


    long count = 0;

    try {
        count = ((Long)sess.createQuery( hql )
            .setLong( "id", id)
            .setInteger("interval", interval )
            .iterate().next()).longValue();

    } catch (HibernateException e) {
        e.printStackTrace();
    }

But hibernate throws a token error as below

antlr.NoViableAltException: unexpected token: :
at org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4016) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:859) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3390) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]

PS: I am using Hibernate 4.1.7

jprism · Accepted Answer · 2018-05-24 22:13:13Z

3

I did further research and found out the issue. The problem is Hibernate is not able parse the syntax. As per it's expectation, this is not well formed HQL syntax. https://forum.hibernate.org/viewtopic.php?f=1&t=982317&view=previous

I fixed by replacing

DATE_ADD( eventLog.eventDate, INTERVAL :interval MINUTE) > now()";

With

time_to_sec(timediff( now() , eventLog.eventDate )) < :seconds";

Hopefully, this can help for someone

answered May 24, 2018 at 22:13

jprism

3,6626 gold badges44 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter1982 · Accepted Answer · 2018-05-24 18:26:17Z

1

Your query require two parametrs

pharmacyOid
interval

But in your query parameters id and interval are used.

Your query should look:

count = ((Long)sess.createQuery( hql )
        .setLong( "pharmacyOid", id)
        .setInteger("interval", interval )
        .iterate().next()).longValue();

answered May 24, 2018 at 18:26

Peter1982

5182 silver badges11 bronze badges

1 Comment

Peter1982 Over a year ago

stackoverflow.com/questions/46340025/…

Collectives™ on Stack Overflow

MySQL DATE_ADD throws Hibernate error

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related