With the mappings below, i am trying to run this HQL query:
SELECT r.id, SUM(c.regularCredit + c.overtimeCredit) totalCredit
FROM ReviewRecord r, Employee e
LEFT OUTER JOIN e.creditItemCalculations c
WHERE r.employee = e
AND r.reviewSession.id = :sessionId
AND r.currentQueue = :queueId
AND r.checkOutTime IS NULL
AND r.id NOT IN (:idsToIgnore)
GROUP BY r.id
ORDER BY totalCredit DESC
I am not sure if something like this is possible, but when i remove the aggregate functions and the order by and group by, the query works (ie, i believe the join is correct). For some reason the SUM ( x + y ) doesn't fly. I have done an HQL with a similar function with no problem. Please give me a heads up to what i might be doing wrong here.
Hibernate version: 2.1.6
Mapping documents:
<hibernate-mapping>
<class
name="com.ccg.domain.Employee"
table="employee"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
...
<bag
name="qualifications"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
>
<key
column="employeeId"
>
</key>
<one-to-many
class="com.ccg.qualification.EmployeeQualification"
/>
</bag>
...
</hibernate-mapping>
<hibernate-mapping>
<class
name="com.ccg.review.ReviewRecord"
table="reviewRecord"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="identity">
</generator>
</id>
...
<many-to-one
name="employee"
class="com.ccg.domain.Employee"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="employeeId"
not-null="true"
/>
...
</hibernate-mapping>
<hibernate-mapping>
<class
name="com.ccg.credit.CreditItem"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="identity">
</generator>
</id>
...
<property
name="regularCredit"
type="double"
update="true"
insert="true"
access="property"
column="regularCredit"
/>
<property
name="overtimeCredit"
type="double"
update="true"
insert="true"
access="property"
column="overtimeCredit"
/>
<property
name="totalCredit"
type="double"
update="true"
insert="true"
access="property"
column="totalCredit"
/>
<joined-subclass
name="com.ccg.credit.CreditItemCalculation"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="CreditItemCalculation"
/>
...
</joined-subclass>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close(): does this really apply?
Full stack trace of any exception that occurs:
org.springframework.orm.hibernate.HibernateQueryException: , expected in SELECT [SELECT r.id, SUM(c.regularCredit) totalRCredit, SUM (c.overtimeCredit) totalOCredit FROM com.ccg.review.ReviewRecord r, com.ccg.domain.Employee e LEFT OUTER JOIN e.creditItemCalculations c WHERE r.employee = e AND r.reviewSession.id = :sessionId AND r.currentQueue = :queueId AND r.checkOutTime IS NULL AND r.id NOT IN (:idsToIgnore0_, :idsToIgnore1_, :idsToIgnore2_, :idsToIgnore3_, :idsToIgnore4_, :idsToIgnore5_, :idsToIgnore6_, :idsToIgnore7_, :idsToIgnore8_, :idsToIgnore9_, :idsToIgnore10_, :idsToIgnore11_, :idsToIgnore12_, :idsToIgnore13_, :idsToIgnore14_, :idsToIgnore15_, :idsToIgnore16_, :idsToIgnore17_, :idsToIgnore18_, :idsToIgnore19_, :idsToIgnore20_) GROUP BY r.id ORDER BY totalRCredit DESC ]; nested exception is net.sf.hibernate.QueryException: , expected in SELECT [SELECT r.id, SUM(c.regularCredit) totalRCredit, SUM (c.overtimeCredit) totalOCredit FROM com.ccg.review.ReviewRecord r, com.ccg.domain.Employee e LEFT OUTER JOIN e.creditItemCalculations c WHERE r.employee = e AND r.reviewSession.id = :sessionId AND r.currentQueue = :queueId AND r.checkOutTime IS NULL AND r.id NOT IN (:idsToIgnore0_, :idsToIgnore1_, :idsToIgnore2_, :idsToIgnore3_, :idsToIgnore4_, :idsToIgnore5_, :idsToIgnore6_, :idsToIgnore7_, :idsToIgnore8_, :idsToIgnore9_, :idsToIgnore10_, :idsToIgnore11_, :idsToIgnore12_, :idsToIgnore13_, :idsToIgnore14_, :idsToIgnore15_, :idsToIgnore16_, :idsToIgnore17_, :idsToIgnore18_, :idsToIgnore19_, :idsToIgnore20_) GROUP BY r.id ORDER BY totalRCredit DESC ]
org.springframework.orm.hibernate.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:530)
com.ccg.dao.hibernate.HibernateReviewDAO.getReviewRecordIdsAvailable(HibernateReviewDAO.java:251)
com.ccg.dao.hibernate.HibernateReviewDAO.getQueueSizeAvailable(HibernateReviewDAO.java:69)
com.ccg.review.service.ReviewServiceImpl.qetQueueSize(ReviewServiceImpl.java:157)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:155)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:122)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
$Proxy60.qetQueueSize(Unknown Source)
com.ccg.review.web.ListReviewsController.listQueues(ListReviewsController.java:116)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:352)
org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:305)
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:584)
org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:368)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:319)
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
Name and version of the database you are using: MySQL 4.0.x
The generated SQL (show_sql=true): doesn't show up
Debug level Hibernate log excerpt:
Login
FAQ
Search
