Using MySQL functions: INTERVAL, DAY

Question

@Id @Column(name = "id") @GeneratedValue private int id; @Formula(value = "(SELECT count(history.city_id) FROM history where history.ts > (now() - INTERVAL 30 DAY) and history.city_id = id)") private int last30daysUpdates;

So, Hibernate converts this string to:

  ...where history.ts > ( now() - entitycity0_.INTERVAL 30 entitycity0_.DAY ) ...

And the error:

You have an error in your SQL syntax; check my server for the right syntax to use near '30 entitycity0_.DAY)

How to make it clear to Hiber that INTERVAL and DAY are mysql functions? Maybe? Thank.

Answer 1 · 2014-03-21T16:16:33

You could override org.hibernate.dialect.MySQLDialect and add required keyword eg in the constructor

 registerKeyword("day"); registerKeyword("interval");

May also want to add other interval types. Don't forget to register your dialect in the hibernate.dialect property. BTW, DAY, and INTERVAL

Zhenyab Zhenyab 3,439 eight 20 · Answer 2 · 2012-04-19T09:35:37

It seems to me that Hibernate has nothing to do with it. The request itself was written incorrectly. The idea should be like this:

 (SELECT count(history.city_id) FROM history where history.ts > DATE_SUB(curdate(), INTERVAL 30 DAY) and history.city_id = id)

Even if how you write, it’s still a mistake, he assigns a suffix to INTERVAL and to DAY.

Using MySQL functions: INTERVAL, DAY

2 answers 2

More articles: