wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Hibernate-Criteria: Group by column alias Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate-Criteria: Group by column alias" Watch "Hibernate-Criteria: Group by column alias" New topic
Author

Hibernate-Criteria: Group by column alias

Emili Calonge
Ranch Hand

Joined: May 17, 2003
Posts: 84
Hi!

I'm tring to perform a query where I need to group by a column alias, but I'm not able to do it.

My code is:


The code works perfectly using just a sqlProjection, without doing the grouping, so it has to do with the grouping.

The resulting select looks correct, and it even works if I execute it on the sql editor, but the log says that it can't find the column "month".

Maybe it is a problem with the Microsoft SQL Driver, even though it's the latest version of the driver, and as I've said, the query works perfectly in the SQLServer sql editor.

Anyone has any suggestion?
Arun Kumarr
Ranch Hand

Joined: May 16, 2005
Posts: 513

try to turn on the hibernate.showsql=true and run your usecase. take the generated query and investigate it.


If you are not laughing at yourself, then you just didn't get the joke.
Kuladip Yadav
Ranch Hand

Joined: Jul 30, 2008
Posts: 162

Will you please put log stack ?


and make sure that 'dataServei' column must be(same column name) in your Table.


Thanks
[ October 08, 2008: Message edited by: Kuldeep Yadav ]
Emili Calonge
Ranch Hand

Joined: May 17, 2003
Posts: 84
Yeah, I have the log, but it just says that the column 'mont' doesn't exist (in Spanish, taht's why I dind't put it here in first place). The syntax of the column names is correct, because as I said, this query works without the Grouping thing.

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at controlador.controladorEstadistiques.consultarServeisZones(controladorEstadistiques.java:241)
at controlador.controladorEstadistiques.ferConsulta(controladorEstadistiques.java:300)
at controlador.controladorEstadistiques.processRequest(controladorEstadistiques.java:84)
at controlador.controladorEstadistiques.doPost(controladorEstadistiques.java:120)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:317)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:288)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:271)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:202)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:206)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:150)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:272)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:637)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:568)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:813)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:263)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:214)
at com.sun.enterprise.web.portunif.PortUnificationPipeline$PUTask.doTask(PortUnificationPipeline.java:380)
at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265)
at com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106)
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]El nombre de columna 'month' no es v´┐Żlido.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 43 more
Emili Calonge
Ranch Hand

Joined: May 17, 2003
Posts: 84
I found the solution to this problem.

I think I already tried to do this thing, but it seems that I didn't do it correctly. All I had to do is use the {alias}. The code now looks like this:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate-Criteria: Group by column alias