HQL with convert timezone

I have the following query in SQL

Select 
count(*) as cnt, 
DATE_FORMAT(CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00',:zone),'%Y-%m-%d') as dat
 from
 t_twitter_tracking wrdTrk 
where 
wrdTrk.word like (:word) and wrdTrk.createdOnGMTDate  between  :stDate and :endDate  group by dat;

I am in the process of migrating all this to Hibernate, I have two questions related to this,

1. How can I write the same query in HQL?
2. Can hibernate cache (ehcache) cache native SQL and how does it work. It would be great if someon can point me开发者_JAVA百科 to the right direction to understand caching of Native SQL queries

Regards, Rohit

That's a really complex question because there are several things to consider. The "AS" portion of your query is ignored in HQL because NHibernate strips it out and creates it's own. Unfortunately this means that the query gets ugly. You'll notice that the the long function starting with "date_format(convert_tz" needs to be repeated in the GROUP BY portion of the query.

string hql = @"SELECT 
                count(*), 
                date_format(
                   convert_tz(wrdTrk.createdOnGMTDate,
                              '+00:00', :zone),'%Y-%m-%d')
               FROM
                 t_twitter_tracking wrdTrk 
               WHERE
                 wrdTrk.word LIKE (:word) 
                   AND wrdTrk.createdOnGMTDate 
                       BETWEEN :stDate and :endDate
               GROUP BY
                 date_format(
                   convert_tz(wrdTrk.createdOnGMTDate,
                              '+00:00',:zone),'%Y-%m-%d')";

var list = session.CreateQuery(hql)
                    .SetParameter("zone", zone)
                    .SetParameter("word", word)
                    .SetParameter("stDate", stDate)
                    .SetParameter("endDate", endDate)
                    .List<object[]>();

foreach (var item in list)
{
    int count = (int)item[0];
    DateTime date = (DateTime)item[1];
    Console.WriteLine("Count: {0}, Date: {1}", count, date.ToString());
}

Unfortunately your work might not end there. You might get this error if the "date_format" or "convert_tz" functions are not registered in the MySQLDialect class.

No data type for node: MethodNode ( ( date_format etc, etc, etc

If they are not then you need to register them yourself with this code.

public class MyDialect : MySQL5Dialect
{
    public MyDialect()
    {
        RegisterFunction("date_format", 
           new StandardSQLFunction(NHibernateUtil.Date, "date_format(?1, ?2)"));
        RegisterFunction("convert_tz", 
           new StandardSQLFunction(NHibernateUtil.Date, "convert_tz(?1, ?2, ?3)"));
    }
}

Then you need to register your custom dialect in the "hibernate.cfg.xml" file like below ("Ns1" is just a placeholder for your namespace).

<property name="dialect">Ns1.MyDialect, MyProgram</property>