-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
Mikee805
 Post subject: Cannot get native SQL query to excute
PostPosted: Tue Aug 23, 2005 12:21 pm 
Regular
Regular

Joined: Mon Aug 22, 2005 1:11 pm
Posts: 50
Location: Pasadena, CA
I am trying to get this query to run:

Code:
SELECT {a}.userName as {a.username}, count(*),               concat(format(avg( time_to_sec({r}.reviewEnd) - time_to_sec({r}.reviewStart) ) / 60,2), ' min')
FROM reviewPhase {r} INNER JOIN account {a} on {a}.id = {r}.accountId WHERE {r}.queueId = :queueId
AND {r}.reviewStart BETWEEN '2005-8-1 00:00:00' AND '2005-8-1 23:59:59'
GROUP BY {r}.accountId
ORDER BY {a}.userName";

I run it with this method:

Code:
getSession().createSQLQuery( sqlQuery, new String[] {"r","a"}, new Class[] { ReviewRecord.class, Account.class} );

And I get the exception printed below. I have also tried the same query in HQL but it dies on the nested functions. Anyway help would be greatly appreciated. Thanks.

Hibernate version: 2.1.6

Full stack trace of any exception that occurs:
[ERROR][org.springframework.web.servlet.FrameworkServlet] - Could not complete request
org.springframework.orm.hibernate.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException occurred; nested exception is java.sql.SQLException: Column 'id0_' not found.
java.sql.SQLException: Column 'id0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2806)
at com.mysql.jdbc.ResultSet.getLong(ResultSet.java:1691)
at net.sf.hibernate.type.LongType.get(LongType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:427)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:200)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3806)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.ccg.dao.hibernate.HibernateReviewDAO.getReviewerStatistics(HibernateReviewDAO.java:1137)
at com.ccg.review.service.ReviewServiceImpl.getReviewerStatistics(ReviewServiceImpl.java:1339)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:287)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:122)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy118.getReviewerStatistics(Unknown Source)
at com.ccg.report.review.GetReviewerStatisticsController.onSubmit(GetReviewerStatisticsController.java:85)
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:248)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:243)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:355)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.sf.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:84)
at net.sf.acegisecurity.intercept.web.SecurityEnforcementFilter.doFilter(SecurityEnforcementFilter.java:182)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.sf.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:153)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.sf.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:305)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.sf.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:225)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.springframework.orm.hibernate.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:172)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)

Name and version of the database you are using: MySql 4.0
Top
 Profile  
 
anar
 Post subject:
PostPosted: Tue Aug 23, 2005 1:23 pm 
Senior
Senior

Joined: Wed Jul 13, 2005 4:31 pm
Posts: 142
Location: Seattle, WA
I think this is because you are not returning all the columns in account, and record. So when hibernate tries to construct those objects, it doesn't find the return values.

You may be better off with using scalar returns in this case...
Code:
SELECT a.userName as username, count(*) as count, concat(format(avg( time_to_sec(r.reviewEnd) - time_to_sec(r.reviewStart) ) / 60,2), ' min') as concat
FROM reviewPhase r INNER JOIN account a on a.id = r.accountId WHERE r.queueId = :queueId
AND r.reviewStart BETWEEN '2005-8-1 00:00:00' AND '2005-8-1 23:59:59'
GROUP BY r.accountId
ORDER BY a.userName";


and run using...
Code:
getSession().createSQLQuery( sqlQuery )
.addScalar("username", Hibernate.?) //replace ? with the correct type
.addScalar("count", Hibernate.?) //replace ? with the correct type
.addScalar("concat", Hibernate.?) //replace ? with the correct type
.list();


Then the result list will contain Object[] with the three values at 0,1,2 indexes.
Top
 Profile  
 
Mikee805
 Post subject:
PostPosted: Tue Aug 23, 2005 2:23 pm 
Regular
Regular

Joined: Mon Aug 22, 2005 1:11 pm
Posts: 50
Location: Pasadena, CA
I think that would work if, I was using hibernate 3.
Top
 Profile  
 
kgignatyev
 Post subject: Query?
PostPosted: Tue Aug 23, 2005 4:19 pm 
Expert
Expert

Joined: Fri Jul 22, 2005 2:42 pm
Posts: 670
Location: Seattle, WA
Try enabling query and post how does it look like. Mapping would not harm too.

As a wild guess: Does your table have queueId column or queue_id?
Same about accountId, is it account_Id in DB?

_________________
--------------
Konstantin

SourceLabs - dependable OpenSource systems

Top
 Profile  
 
Mikee805
 Post subject:
PostPosted: Tue Aug 23, 2005 4:25 pm 
Regular
Regular

Joined: Mon Aug 22, 2005 1:11 pm
Posts: 50
Location: Pasadena, CA
Quote:
Try enabling query and post how does it look like. Mapping would not harm too.


I am not sure what you are trying to say. Are you telling me to turn on that output?

Quote:
As a wild guess: Does your table have queueId column or queue_id?
Same about accountId, is it account_Id in DB?


They are queueId and accountId.

Hope that helps :)
Top
 Profile  
 
kgignatyev
 Post subject: show sql
PostPosted: Tue Aug 23, 2005 7:40 pm 
Expert
Expert

Joined: Fri Jul 22, 2005 2:42 pm
Posts: 670
Location: Seattle, WA
Mikee805 wrote:
Quote:
Try enabling query and post how does it look like. Mapping would not harm too.


I am not sure what you are trying to say. Are you telling me to turn on that output?

Quote:

In the config: <property name="hibernate.show_sql">true</property>


hbm.xml files are usefull too

_________________
--------------
Konstantin

SourceLabs - dependable OpenSource systems

Top
 Profile  
 
Mikee805
 Post subject:
PostPosted: Tue Aug 23, 2005 8:01 pm 
Regular
Regular

Joined: Mon Aug 22, 2005 1:11 pm
Posts: 50
Location: Pasadena, CA
Thank you for your help. But I got it working now.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.