CODEWITHSUNDEEP
mysqlsqlhibernatedatejpa
Shiva
Shiva

Reputation: 113

INTERVAL near line 1 : Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: INTERVAL near

Here is my query:

 private static final String USERS_NOT_ACTION_IN_LAST_MONTH = select u from myTable u where u.lastDate + INTERVAL 30 DAY <= CURDATE();

 @Override
 public List<MyEntity> getItemOlderThanMonth() {
    Query q = _entityManager.createQuery(USERS_NOT_ACTION_IN_LAST_MONTH, MyEntity.class);
    return q.getResultList();
 }

I want to get records older than 30 days.

Upvotes: 2

Views: 5005

Answers (3)

Dylan
Dylan

Reputation: 2219

@ARPAN CHAKARVARTY's answer helped me get to what I needed. Here is my Groovy code.

 final int DELETE_OLDER_THAN = 90

 Map restrictions = [date: new Date() - DELETE_OLDER_THAN]
        String query = """select a
                           from history a
                           where date <= :date
                        """
        List<History> histories = History.executeQuery(
                query, restrictions, [max: 100, offset: null])

Upvotes: 0

ARPAN CHAKARVARTY
ARPAN CHAKARVARTY

Reputation: 387

You can calculate current date by query parameter(current_date- 30)

private static final String USERS_NOT_ACTION_IN_LAST_MONTH = select u from myTable u where u.lastDate <= :calculated_date;

where calculated_date=current_date -30 days.

Upvotes: 3

Mara
Mara

Reputation: 3077

By using _entityManager.createQuery hibernate sql (hql) script is created and hibernate doesn't have INTERVAL key word.

But createNativeQuery is native sql and behaves as normal sql script. So for mysql db INTERVAL key word should work. So smth like that

_entityManager.createNativeQuery("select * from myTable u where u.lastDate + INTERVAL 30 DAY <= CURDATE()");

Upvotes: 0

Related Questions