wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Sub query(oracle) conversion to HQL/criteria Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Sub query(oracle) conversion to HQL/criteria" Watch "Sub query(oracle) conversion to HQL/criteria" New topic
Author

Sub query(oracle) conversion to HQL/criteria

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I need to convert the following subquery(oracle) to either HQL/criteria.

SELECT * FROM( SELECT * FROM OnlineExam ORDER BY dbms_random.value ) WHERE rownum <= 2

This is what i tried.



Code 2


code 2 gives me following exception :


org.hibernate.hql.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [SELECT * FROM org.nit.OnlineExam ORDER BY dbms_random.value]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at org.nit.TestDao.fetchQuestion(TestDao.java:31)
at org.nit.TestServlet.doPost(TestServlet.java:30)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)




which gives the error
org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'dbms_random.value' [from org.nit.OnlineExam test order by dbms_random.value]


kunal
Anurag Verma
Ranch Hand

Joined: Mar 30, 2012
Posts: 124

Create Native Query instead of Query to use your original sql.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I did that. But, how to solve the problem using criteria or hql?
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Can this be solved using criteria?
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Solved, in Restrctions. class there is a in method which takes 2 args, string property and the collection
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sub query(oracle) conversion to HQL/criteria