i have this simple sql query which i want to execute thru hibernate

so far i have attempted to write it native query of hibernate but this thing gives error kindly help...

List syscompList = session.createSQLQuery("select max(s.stat_stamp),a.syscompid, from stats.stats s,stats.syscomps a where s.syscompid=a.syscompid and a.syscompid is not null group by a.syscompid").addEntity("s",StatsEntity.class).addEntity("a",SyscompsEntity.class).list()

where i am making mistake????

Why do you have a , (Comma) after "a.syscompid" and before "from" in the above SQL statement? This will give a syntax error. Other than that it looks fine to me.

Also pls post the error you were getting.

-BJ.

I am still getting the error ..

Hibernate: select max(s.stat_stamp),a.syscompid from stats.stats s,stats.syscomps a where s.syscompid=a.syscompid and a.syscompid is not null group by a.syscompid
Exception in thread "Main Thread" org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
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.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.nomura.dbe.atrisk.stats.StatsDaoHelper.GetMaxTimestamp(StatsDaoHelper.java:199)
at com.nomura.dbe.atrisk.stats.StatsDao.GetMaxTimestamps(StatsDao.java:366)
at com.nomura.dbe.atrisk.stats.StatsDao$$FastClassByCGLIB$$1b4b95e.invoke(ILjava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(<generated>:???)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:644)
at com.nomura.dbe.atrisk.stats.StatsDao$$EnhancerByCGLIB$$457768e.GetMaxTimestamps()Ljava.util.List;(<generated>:???)
at com.nomura.dbe.atrisk.GetForecastValue.main(GetForecastValue.java:51)
Caused by: com.ibm.db2.jcc.a.SqlException: Invalid argument: unknown column name statid
at com.ibm.db2.jcc.a.n.a(n.java:1270)
at com.ibm.db2.jcc.a.zc.a(zc.java:1369)
at com.ibm.db2.jcc.a.zc.getLong(zc.java:1166)
at org.apache.commons.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:239)
at org.hibernate.type.LongType.get(LongType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 13 more

The stack trace has the following:



The query you mentioned does not show statid column where as the JDBC exception is showing an error on column called "statid". We should take a look at the SQL statement that is getting generated/executed by hibernate at runtime. If you enable DEBUG log for Hibernate, you can see the exact SQL statement being fired.


-BJ

I would check your mappings for these entities. I would bet that you have an id defined with an incorrect column name of statid (maybe it should be stat_id - just a guess) and when Hibernate goes to get the id, it chokes with that exception.