I am using the <formula> element to derive a property (which has no db column) and then I am trying to sort on this derived property using the criteria api and I get an exception. If instead I try to sort on a property that's not derived (ie maps to a column), it works.
my derived property is defined as follows:
Code:
<property name="totalCnt" type="java.lang.Integer">
<formula>
(
select count(*)
from entries en
where en.entries_id = ENTRIES_ID
)
</formula>
</property>
I tried to specify the sort as follows:
Code:
criteria.addOrder(Property.forName("totalCnt").asc());
and this way:
Code:
criteria.addOrder(Order.asc("totalCnt"));
and both ways give me an exception shown below.
Any ideas on how to sort on a derived property?
Thanks in advance.
John
Hibernate version: 3.2.2
Full stack trace of any exception that occurs:
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: 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:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
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 org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:979)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:969)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:962)
at com.ex1.service.MyDAO.findByCriteria(MyDAO.java:175)
at com.ex1.service.MyService.findGetCnt(MyService.java:269)
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:281)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:199)
at $Proxy1.findGetCnt(Unknown Source)
at com.ex1.myTest.testGetCnt(MyTest.java:122)
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 junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.ibm.db2.jcc.b.SQLException: THIS_.ENTRIES_ID
at com.ibm.db2.jcc.b.ce.e(ce.java:1093)
at com.ibm.db2.jcc.b.ce.a(ce.java:869)
at com.ibm.db2.jcc.a.bd.g(bd.java:125)
at com.ibm.db2.jcc.a.bd.a(bd.java:40)
at com.ibm.db2.jcc.a.r.a(r.java:31)
at com.ibm.db2.jcc.a.bp.g(bp.java:141)
at com.ibm.db2.jcc.b.ce.h(ce.java:853)
at com.ibm.db2.jcc.b.cf.G(cf.java:1185)
at com.ibm.db2.jcc.b.cf.a(cf.java:1497)
at com.ibm.db2.jcc.b.cf.executeQuery(cf.java:303)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
... 35 more[code]
Login
FAQ
Search