hibernate mysql annotation,Hibernate Formula Annotation - MySql函数：INTERVAL，DAY

I have the code:

@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, hiberante parse this formula to:

...where

history.ts > (

now() - entitycity0_.INTERVAL 30 entitycity0_.DAY

) ...

and the error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 entitycity0_.DAY)

How can i say hibernate that INTERVAL and DAY are the functions of MysqL? Is it possible?

Thanks.

解决方案

Which MySQL dialect are you use?

If MySqlDialect oder MySql5Dialect you can use follows:

SELECT count(history.city_id) FROM history where timediff(now(), history.ts) < '720' and history.city_id = id

Or define new hibernate function

public class ExtendedMySQL5Dialect extends MySQL5Dialect

{

public ExtendedMySQL5Dialect()

{

super();

registerFunction( "date_sub_interval", new SQLFunctionTemplate( Hibernate.DATE, "date_sub(?1, INTERVAL ?2 ?3)" ) );

registerFunction( "date_add_interval", new SQLFunctionTemplate( Hibernate.DATE, "date_add(?1, INTERVAL ?2 ?3)" ) );

}

}

Query:

History.ts < date_sub_interval(now(), 30, DAY)