Hi,

Do you know if it is possible to perform a "group by month" with hibernate ?
The following query doesn't work but it shows what I want:

select object.creationStamp from Objects object group by object.creationStamp.month

Thank you for your help.

Matthieu

You must use database-specific function to work with date.
For Oracle following may work:



This assumes object.creationStamp is mapped to Oracle datetime column

Thank you very much for your answer dimas !

Your tip seems correct to me but I am not able to make it working.
It currently fails at hibernate layer.

Here is what I did and the trace error is at the bottom.
Should I use something else than session.find(...) for DB specific queries (Oracle 9i in my case) ?

session.find("select to_char(object.creationStamp, 'MM') from Objects object group by to_char(object.creationStamp, 'MM')");

Regs,
Matthieu


net.sf.hibernate.QueryException: undefined alias: to_char [select to_char(object.creationStamp, 'MM') from Objects object group by to_char(object.creationStamp, 'MM')]
at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:154)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:152)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:141)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:287)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1488)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1459)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1449)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1441)

Hmm. After I post the reply to you I have found following post in the forum: http://forum.hibernate.org/viewtopic.ph ... ight=month

This uses SQL extensions to offer portable way of performing date operations. To be honest, I have never tried this.

New your query should look like


See also http://java.sun.com/j2se/1.3/docs/guide ... ame11.html for details.

You can not use database specific functions in the select part, only in the where part. I don't know if they are allowed in the group by however. I've never seen the stuff steve pointed out in the post noted by dimas, does this really work?

Well, Hibernate supports this but JDBC driver may not.
I have just tried this with Oracle (no idea which driver version I'm using, suppose it is default which comes with Oracle 8.16).

Thank you for your contribution.
Unfortunately it is still not working for me.
I have tried proposal from Steve but I still have an alias problem.

net.sf.hibernate.QueryException: undefined alias: {fn [SELECT {fn MONTH(o.creationStamp)}, count(*) from Objects as o GROUP BY {fn MONTH(o.creationStamp)}]

which of the session method should I use and what are the potential additionnal parameters (with createSQLQuery) ?

session.createQuery(...)
session.find(...)
session.createSQLQuery(...)

Thank you

Matthieu

I'm afraid the simplest way here is to use native SQL queries - check documentation on this.

You can not use db-specific SQL Functions in the select clause, as I said. You could however try to select only o.creationStamp and extract the month manually in Java.

Thank you all.
As adviced I will perform the group by month outside of hibernate (either on java side or on DB side).
Thank you again for your reactiveness.

Cheers,
Matthieu

Does group by month using a database-specific SQL function really not work? I think the problem with your query really was just the select part - here database specific functions are not allowed.