Hibernate Formula Annotation - MySql函数：INTERVAL，DAY [英] Hibernate Formula Annotation - MySql functions: INTERVAL, DAY

问题描述

我有代码：

@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;

因此，hiberante将此公式解析为：

so, hiberante parse this formula to:

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

，错误是：

您的SQL语法中有错误;检查与MySQL服务器版本对应的手册，以便在'30 entitycity0_.DAY附近使用正确的语法。

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)

我怎么能说hibernate INTERVAL和DAY是函数MysqL？有可能吗？

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

谢谢。

推荐答案

哪种MySQL方言都是你用？
如果MySqlDialect或者MySql5Dialect你可以使用以下：

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

或者定义新的hibernate函数

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)" ) );           
        }
}

查询：

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

这篇关于Hibernate Formula Annotation - MySql函数：INTERVAL，DAY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！